Backing Up MySQL Databases on Ubuntu Server

Having your own server comes with a lot of maintenance considerations. It can be your worst nightmare to experience a disastrous server crash and damaging (or loosing!!!) your database. Prevention is better than cure. An easy prevention of such an unfortunate problem is to backup your databases regularly, on a rotational basis.

I have some ubuntu servers running on Amazon AWS. I am well aware of the high quality of the services provided by amazon. But I am just pretty obsessed with fault prevention and recovery. Backing up databases daily on rotational basis seems to be a rather good idea.

There are many ways to do that. You can employ a shell script or cronjob or some third party tool/utility. I preferred to use cronjob and mysqldump utility which ships with MySQL itself.

Cron is a software utility in Unix like (that means ALL Linuxes) operating Systems. Its a job scheduler, speaking crudely. Much like windows Task Scheduler. Now there is a lot to learn about cron. For the purpose of this article I will provide the steps I took to backup my databases.

First of all in the terminal lets open up “/etc/crontab” file with an editor of your choice (my choice is nano) with root privileges (this essentially means using “sudo” and all).

Now at the bottom of the file add following line:

0 13 * * * root mysqldump -u root -p database_name | gzip > /home/backup/database_`date +\%m-\%d-\%Y`.sql.gz

Here 0 13 * * * specifies the time when you want to take the backup. There are 5 terms , MINUTE HOUR DAY_OF_MONTH MONTH DAY_OF_WEEK. So our backup is created at 1 PM everyday, because we have not provided the values for other parameters.

root mysqldump -u root -p database_name is the command executed with root privileges. It invokes mysqldump utility and creates a SQL dump of the database whose name is provided.

Then using |gzip we pipe this sql file to gzip utility which compresses this file to gzip format.

Finally we are providing a location’s path to save this file by /home/backup/database_`date +\%m-\%d-\%Y`.sql.gz

After adding this line its required to restart the cron daemon. Following command will do this.

sudo /etc/init.d/cron restart

Although this process is applied and tested on my ubuntu server. Any linux distro will be having almost the same setup.