[lug] cron and mysql

[David L. Anselmi]

Luke P wrote:
> Hey,
> 
> I am thinking about running a cron job that runs a mysql script, is that
> possible and if so, can you point me in the direction of some docs on doing
> that?

Yes, it is possible.  Take a look at:

http://dev.mysql.com/doc/refman/5.0/en/batch-mode.html

If you've got your script and the mysql command line to run it, then 
look at crontab(5) for the instructions on scheduling it.

> In addition, I am not sure if cron is the best method for running
> scheduled sql scripts, if you have a better idea, let me know.

cron (and at, and anacron) are the typical ways to schedule things in 
UNIX.  It may be that mysql has some scheduling ability (SQL Server does 
(e.g., for backup jobs), so I'd guess that Oracle does too but I've 
never seen it used, and postgresql seems to prefer cron).

> p.s. if you are curious, the script I'm thinking of running is one where I
> select from one table and insert into another, again, if you think there's a
> better way than a script, let me know.

If you want this done daily, that seems fine.  If what you really want 
is an insert in one table should cause an insert in another, you might 
prefer using a trigger (and I have no idea whether mysql supports those).

Dave