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.
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.