backup mysql database with cron job

If you run multiple WordPress websites on your web server like I do, you will need to make regular backups. A blog database changes daily and if your server blows up without no recent backups…..well, I would hate to be in that persons shoes.

Here I will describe how you can create a cronjob that automatically backs up your database at certain intervals.

I use ISPConfig to manage my websites and it contains a way to setup cronjobs but sometimes you can run into trouble using ISPConfig to do that. The easiest way to do it is to open up your terminal and type in : crontab -e

Next it will ask you what editor you would like to use. I use nano. Then you will see something like this below:

* * * * * /usr/local/ispconfig/server/server.sh 2>&1 > /dev/null | while read line; do echo `/bin/date` “$line” >> /var/log/ispconfig/cron.log; done
30 00 * * * /usr/local/ispconfig/server/cron_daily.sh 2>&1 > /dev/null | while read line; do echo `/bin/date` “$line” >> /var/log/ispconfig/cron.log; done

So far we have 2 cronjobs. I will now add one that backs up a specific database on my server.

Lets say I want to back this database up everyday at 3:00AM. The 5 stars (*) we see in the 2 cronjobs above represent minutes, hours, day of the month, month, and days of the week. The part after the stars is the command we want to issue. Cronjobs use 24-hour format so 3:00AM is….well 3:00. What I come up with is below:

00 3 * * * mysqldump -u root -pPASSWORD DATABASE | gzip > /var/www/clients/client0/web19/web/database_backup/db_`date +\%m-\%d-\%Y`.sql.gz

The BOLD regions above need to be swapped out to your own credentials. So the command above is logging into mysql and dumping the database into a gzip file. It then puts it in the database_backup folder located here (/var/www/clients/client0/web19/web/database_backup) and appends the current date to the end of the filename. So our actual file may be named: db_12_23-2014.sql.gz.

After you have that all squared away in nano, we save the file. It then will say: crontab: installing new crontab

To delete files older than 1 week add this into your cronjob:
find /var/www/clients/client0/web19/web/database_backup -type f -mtime +7 -exec rm {} +