Daily Full MySQL backup using cron

Today I bring to you a cron job that will save a well named gzip of your database dump every day at 4:00 (4am).
Example output: “myDB_2012-01-29_04-00-01.sql.gz

In this example, I’ll use a fictional host, path, login and password. When you copy this cron job, make sure to replace the following values.
Host: mysql.foo.bar
Login: Foo
Password: Bar
Path to save to: /where/we/backup/db/
0 4 * * *: at 0 minutes, 4 hours, every day, every month, every day of week

Add the following line to your crontab:
0 4 * * * mysqldump -hmysql.foo.bar -uFoo -pBar –all-databases | gzip > /where/we/backup/db/myDB_`date +\%Y-\%m-\%d_\%H-\%M-\%S`.sql.gz

To open the cron table for edit, use the command “crontab -e

Important: If you type it yourself make sure to use ` and not ‘, or the date won’t be put into the filename, instead you’ll end up with the command as a string.
Note: This is a “Full” backup, I use this approach on my database because it allows me to go without potential data loss on a corrupted Incremental/Differential backup.

Posted in Tutorials Tagged with: , , , , ,

Leave a Reply