MySQL Backup Script in Bash / Cron Job

The following is a handy cron job that backs up a MySQL database, compress it with gzip and will delete the ones older than 30 days. Make sure the user running the job has write permissions (in my case the user levente@testsrv) on the backup output directory. The mysql backup job will be executed daily at 2:15am and the cleanup job at 2:20am.

levente@testsrv:~$ crontab -e


# ....
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command

 15 2 * * * mysqldump -u mysqluser -pmysqlpassword db_name | gzip > /var/backups/mysql/db_name_`date '+\%Y\%m\%d-\%H\%M\%S'`.sql.gz
 20 2 * * * find /var/backups/mysql/db_name* -mtime +30 -exec rm {} \;

If you want to run the MySQL backup script directly at the shell, you have to remove the backslash characters before the % because these are escaped in cron.

levente@testsrv:~$ mysqldump -u mysqluser -pmysqlpassword db_name | gzip > /var/backups/mysql/db_name_`date '+%Y%m%d-%H%M%S'`.sql.gz