How to Backup MySQL Database automatically (for Linux users)

If you are a serious webmaster and have your own server, you should take special care of your data. If you are a BSP (Blog Service Provider) or have a large forum, you need backup the database every day. But it is a boring work indeed. Why not to let the computer do the dirty work? Cron can help you.

"cron" is a time-based scheduling utility in Unix/Linux operating system. You  can find it in most of Unix/Linux distributions and usually it stats up with the operating system automatically. It reads a configure file (/etc/crontab) when it starts up and keeps running while the computer is running.

It will execute some programs at some given moment. For example, I can use cron to reboot my server at 3:00 am in each day. So, of course, I can use it to backup MySQL database automatically.

Cron and mysqldump will backup your database automatically

The most important thing is to edit its configure file. Usually it is /etc/crontab. For example, if I want to backup MySQL in 2:15 am per day, I need to add the following line:

15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`data '+%m-%d-%Y'`.sql.gz

The first part is "15 2 * * *". The five fields mean "minute, hour, day of month, month, day of week" respectively. The character '*' stands for "any". Since I want to backup in each day, I only set the minute and the hour.

The next, "root", means "run following command as root account".

So the last part is the command. This command is so long that some people feel it is a little difficult to understand it. Let me explain it in detail.

"mysqldump -u root -pPASSWORD --all-databases" means backing up all the MySQL databases (to learn more, read How to backup MySQL with mysqldump).

But what's the backup file? I put a vertical bar and "gzip". It means passing the backup to gzip to compress it. And then I use a redirect symbol to specify the compressed backup file: /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz.

There is a trick. I put `date '+%m-%d-%Y'` in the file name. What does it mean? "date" is a command that shows the current time. date '+%m-%d-%Y" shows current date using 'MM-DD-YYYY' format. I use the back quote (or backslash) ` to quote the whole command. What I mean is to use its result to replace the command string.

So this command generates a compressed backup file every day and its name is "database_MM-DD-YYYY". Here "MM-DD-YYYY" indicates the backup date.

Finally, you should use '/etc/init.d/cron restart' command to restart the cron. Then your computer or your server will backup MySQL databse at 2:15am every day.