howto backup mysql using cron

edit

Edit the crontab file running the following command:

sudo crontab -e

and add the following line (or similar) to it:

17 17 * * * /usr/bin/mysqldump -h host.with.db -uroot -prootpassword DataBaseName > /path/to/directory/dbdump.sql

save and exit from the crontab file. This will run once per day at 5:17 p.m.

If the DB lives in the same host you are running the cron job, the "-h host.with.db" option is not needed. Be careful and do not add "spaces" after -u and -p options or it will not work. Replace DataBaseName with you real DB name. Finally redirect (>) sql output file to your favorite location/folder.

Note: Although what I just showed should work I strongly discourage the use of root user to dump a DB. I also would not recommend to run a cron job (or any job from the command line) which has the clear text password in it. It is a security risk and should be avoided! If someone else have access to your box with a simple ps command might see the full syntax of your cron command and grab the root password for your box!

I agree with ztank1013 about adding the password to the cron job. One way around it would be to just take the command and put it in a bash script with restrictive permissions. So under /root/ create a file called mysqlBackup.sh and put the below in to the file.

#!/bin/bash
/usr/bin/mysqldump -h host.with.db -uroot -prootpassword DataBaseName > /path/to/directory/dbdump.sql

Again fill the command in with the appropriate values as stated above. Next as root do:

chmod 700 /root/mysqlBackup.sh
chown root:root /root/mysqlBackup.sh

Finally make your crontab entry as instructed above but use the script instead so it reads as follows:
17 17 * * * /root/mysqlBackup.sh