Cron jobs for working with MySQL


#1

I am trying to schedule a MySQL event, for example to ditch a bunch of records on a specific date. So I am trying to experiment a bit, for starters by making a query that adds a record, just to see if I can schedule the cron job properly.

I have made a test.sql file with the following contents:

INSERT INTO tblTest
VALUES (3);

The table exists and contains only one column, called “Test”. I stored the file in /home/kars (kars is my username).

I then added a cronjob using the Dreamhost Panel with the following line in it:

/usr/bin/mysql -hHOSTNAME -uUSERNAME -pPASSWORD DB_NAME < /home/kars/test.sql

(needless to say the CAPS are replaced by actual credentials)

But it doesn’t work. No row with “3” gets added. I think maybe the /usr/bin/mysql part might be wrong. If I login on my server there is no /usr/ directory, for starters. And various examples on the web sometimes mention /usr/local/bin/mysql instead of the one I use. But I don’t see any error messages and the quickest way I re-test is setting the Cronjob on an hourly basis…a very slow process with no visual feedback.
Who can help me?


#2

I fixed it myself. I edited the cron job to read:

/usr/bin/mysql -h HOSTNAME -u USER -pPASSWORD DBNAME < /home/USERNAME/test.sql

and it worked!
Replace everything in CAPS by stuff that is relevant for you. Also make sure you include the right spaces and dashes, but no space in front of the password!
I must say I had expected more help here, but alas :frowning: