Is this the best way to schedule a task?

[HestonJames]

Hello Guys,

I'm relatively inexperienced when it comes to MySQL, this morning I've been working on a maintenance script for the database which optimizes tables within, deletes a bunch of old data and does a bunch of other small tasks.

I've saved this into a .SQL script, which I'm now planning to schedule using cron to run every day in the early hours, when the database is experiencing the least traffic.

I'm currently planning to action the script like so:

* 3 * * * mysql -h localhost -u root -D <mydbname> --password=<mydbpassword> < /myapp/tools/maintenance.sql > /tmp/results.sql

Now, one thing that concerns me a little is the fact that I'm having to pass in a root username/password, which in an ideal world I guess I wouldn't do?

Am I going along the right track to schedule this kind of tasks? or is there a better way of doing this?

I'm running MySQL 5.0, I have not GUI tools available as this is an embedded platform so only have command line available.

Thanks all,

Heston

 

[hvass]

Yup this is the right approach.
You might want to consider setting up a special user who only has specific rights on specific tables from a specific host rather than use root.
You could set up a simple script with the mysql command in that cron calls and then chmod the file to restrict access.

 

[lgarner]

You can also store the credentials in ~root/.my.cnf (or in ~specialuser/.my.cnf, even better). Then reference that file instead of passing the username/password.

 

[DonQuichote]

Only one thing to add: In CRON jobs, do not expect every variable to exist. So the path and $HOME may not be set. To be sure, I would therefore /usr/bin/mysql instead of just mysql for calling the command-line client.

I do similar things: I have a subversion update script that updates all of my code, runs the (repeatable) MySQL script, runs the unit tests and finally sends all the results to our monitoring system. To run the MySQL script, I defined a user automatic@localhost with no password. Naturally, I only do this on my development machines, as the Live servers must not be updated automatically.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)