How to Backup MySQL Databases Automatically on Ubuntu

Not backing up your customer’s data regularly is detrimental to the trustworthiness of your service. This tutorial shows how to back up MySQL data at regular intervals.

MySQL is a popular database management solution that uses a Structured Querying Language (SQL) to access and manipulate data. It can easily be used to manage the data of web or desktop applications.

Regular backups are important with any kind of data, and this is especially relevant when talking about the data of your customers who trust to have their data available anytime. I run MySQL on DigitalOcean droplets and in case you want to give it a try with a $100 credit, simply follow this link:

DigitalOcean - The developer cloud

This tutorial introduces an excellent tool that helps you backup MySQL databases at regular intervals (i.e. daily, weekly, and monthly), is completely automatic and easy to setup.

Using mysqldump and Restore

The conventional (and more tedious) way is to dump your database’s data into an SQL file. The basic syntax of the command is:

$ mysqldump -u username -p database_name > backup.sql

To restore from an SQL file, use this command:

$ mysql -u username -p database_name < backup.sql

You could spin this further and create a cron job which automatically assigns a new file name containing the timestamp to ensure that previous backups are not replaced by a new one. Luckily, there is a tool automysqlbackup which does everything for you.

How to Backup MySQL using automysqlbackup

automysqlbackup is a utility program that is available in the Ubuntu repositories. This utility can be scheduled to automatically perform backups at regular intervals. To install, type the following into the terminal:

$ sudo apt-get install automysqlbackup

A following prompt will ask you which mail configuration you prefer. Select “internet site” if you’re going to set up email notification. If not, just select “no configuration”. I personally went for “no configuration” to leave things unchanged. Start automysqlbackup:

$ sudo automysqlbackup

A folder structure and initial backups will be performed immediately. The default location for backups is /var/lib/automysqlbackup. List contents of this directory to see the folder structure:

$ ls /var/lib/automysqlbackup

You should see three directories for the daily, weekly and monthly backups being performed automatically by automysqlbackup and a subdirectory for each database containing the respective gzipped SQL dump.

The configuration file for automysqlbackup is located at /etc/default/automysqlbackup. Open it in your favorite editor:

sudo nano /etc/default/automysqlbackup

This file assigns many variables by the MySQL file located at /etc/mysql/debian.cnf. It reads the user, password, and databases to be backed up. All default configurations can be left intact. You are now able to sleep well at night, knowing that your customer’s data will be backed up at regular intervals.