Cron Jobs MySQL


#1

Hi,

I have a MySQL database and I need to be able to back it up and restore it and would like to do this using Cron jobs.

My Back up Cron job is backing up to the selected directory but as a 0kb file

Here is my daily backup job:

mysqldump --opt -u userid -p password -h SQLHOSTNAME DATABASENAME > /home/username/Backup/DB.sql.date +%A

This is my restore job:

mysql -u userid -p password -h SQLHOSTNAME DATABASENAME < /home/username/Backup/DB.sql

I plan to manually enter the filename should I have to restore and run it as a cron job.

I’m assuming I would have to stop to DB first before a restore. - haven’t reached this stage yet as the backup keeps failing.

Any help with this would make me a very very happy noob


#2

Use the full path in your command:
/usr/bin/mysqldump
/usr/bin/mysql

So your dump command should look like:
/usr/bin/mysqldump -hSQLHOSTNAME -uUSERID -pPASSWORD DATABASENAME > /home/username/Backup/DB.sql.date +%A

Leave the spaces out of the flags for -h -u -p. And I believe that --opt is on by default, so there’s no need to include it.

By the way, what happens if you run the command manually?

-Scott