There are a number of ways to automatically backup MySQL databases, the simplest one is just to use the mysqldump program and schedule a cron job.

The mysqldump program typically produces an SQL file which can used to restore any where between one to all databases on a server.

Running the mysqldump program is done using the following command:

mysqldump [OPTIONS] (Database name or list of databases)

NOTE: You can use --all-databases in the place of a single or list of database
names to backup all the databases in one file!

Whilst there are a large number of options to give the program the most important ones are probably the “username” and “password” ones.

Trying out MySQLDump in a terminal

Before setting up a cron job to automatically backup the databases it is best to test the command directly in a terminal and see if it produces the expected result.

If the server only holds a handful of databases then doing a backup of all the databases at once is a nice easy way to do it. however if the database server holds lots of databases then you will end up with a very large dump file and it will make it a lot harder to just recover a single database. For the purpose of keeping this blog simple I am only going to use commands for backing up all the databases at once. (i.e. using the option –all-databases)

Firstly try the command as follows, replacing “USERNAME” and “PASSWORD” with the username and password of a user that has full access to all the databases (probably root).

mysqldump --opt --user=USERNAME --password=PASSWORD --all-databases > /home/seb/mybackups/test.sql

If you then navigate to the location you asked it to save test.sql to, there should be a file called test.sql. Just check the file size of it and  open it up. The first line of the file should be:

--MySQL dump <<Version number>>

If so, then it looks like it dumped the database contents correctly. If not go back and retry running the command again, check the username and password your using and make sure the directory it is trying to write to exists and you have write access to it.

If Linux is unable to find the command mysqldump then you may need to enter /usr/bin/mysqldump instead.

Using GZip to compress the file

If you want to reduce the amount of space the backup file uses you can force mysqldump to pipe the output through gzip to have it compressed automatically before writing to disk. Just add “| gzip “ before you specify you want the output to go to a file and the location. i.e:

mysqldump --opt --user=USERNAME --password=PASSWORD --all-databases | gzip > /home/seb/mybackups/test.sql.gz

Try running that command and make sure it works, also note the .gz appended to the output file name.

Linking the Filename to the Date

To get the filename of the backup to be linked to the date so that each day it changes (i.e. does not just override the previous days backup) we can use the date command. This will print out the current date (and time) in a format we specify. So if the file name part of the dump command is written as follows:

backup_`date +\%d\%m\%y`.sql.gz

This would create a file with the filename backup_180610.sql.gz (On the day this blog post was written)

The \ character is simply there as an escape character so the %d, %m and %y are correctly picked up.

Scheduling the backup (Creating a Cron Job)

You will most likely want this cron job to run as root so know one else can view the password stored in the crontab file.

To edit the current users crontab file, enter:

crontab -e

The first five columns (separated by spaces) specify the time and date at which the command will be run, and the sixth column is the command.

The columns:

1. minute (0-59),
2. hour (0-23),
3. day of the month (1-31),
4. month of the year (1-12),
5. day of the week (0-6, 0=Sunday)
6. The command (exactly the same as entered directly in to the terminal)

If you want a command to run every hour, or every day you can use the * character in the correct column to specify this. The * is effectively a wild card and so represents any possible value.

The following line would backup all the databases every night at midnight:

0 0 * * * mysqldump --opt --user=USERNAME --password=PASSWORD --all-databases | gzip > /home/seb/mybackups/backup_`date +\%d\%m\%y`.sql.gz

save and exit the crontab file and that’s it.

Checking your backup files work

Once you cron job is all set up and has ran once, it is a good idea to check the backup file works correctly by trying to restore from it, after all there is no point making backups if they don’t work. Every now again it is wise to check your backups are still working as expected, and your fully able to restore all the backed-up databases using them.