Backup all your MySQL databases with a cron job

June, 27 2008

Here is a easy way to back up your databases using a cron job

1. Go to cPanel > Cron Jobs
2. Select between Standard or Advanced, that’s up to you !
3. In the command field, type this :
   

   date=`date -I` ; mysqldump -uyourusername -pyourpassword --all-databases > /home/login/backups/xbackup_$date.sql ; gzip /home/login/backups/xbackup_$date.sql

4. Now let’s see what this all means : the date line formats the date so that we can append it to our backup filename. We then ask mysql to dump all databases into /home/login/backups/xbackup_$date.sql, where login is your cPanel name, using “yourusername” as user (-u) and “yourpassword” as the password (-p). Notice the date variable inserted in the filename. Finally our SQL file is gzipped using the gzip directive.
5. You now have to set your backup frequency. And relax !
6. Remeber that you will have to break up each database from the back up. Also, depending on the host remove the Create Database line from the top of each database