MySQL incremental backup with mysqldump and rdiff-backup


Thu 30 January 2020

In this post I will show you a simple method to perform a MySQL incremental backup on Linux. This method is quite effective for small and medium databases. In a follow up post I will present another method that can be used when the size of your database is large (think many GB of data).

Do you need a MySQL incremental backup?

Of course you need a backup! Every good sysadmin knows that to prevent data loss in case of application bugs or human errors, the three golden rules are: backup, backup, backup! Ok, the rule is just one, but you got the point. 😉

If your database server uses a RAID array of disks, you may be tempted to skip the backup requirement. A good RAID array should protect you from data losses, right? Nope!

Take the following advice, write it on a post-it note and stick it on your monitor:

RAID is good to prevent hardware failures. Backup is good to repair damages made by application bugs or human errors.

If your bugged application or your inadvertent employee performs a query that mistakenly deletes one million row from your database, the RAID controller will happily delete data from every disk attached to the array. Keep the data in sync is exactly the job of a RAID controller, and of course it will keep in sync also the errors.

A good MySQL incremental backup strategy will let you restore the database to a point in time when the data loss has not been occurred yet. Then you can fix your application bug or dismiss your employee and keep up with your business.

A simple approach to backup small and medium MySQL databases

This approach to incremental backup will use mysqldump and rdiff-backup. I’ll skip the steps needed to install these software packages, because you can install both from your Linux distribution package management system.

The idea is simple: perform a MySQL backup using mysqldump, then passing the dump to rdiff-backup to take the history of changes for a desired amount of days.

Creating and updating an incremental backup

Putting it in code is quite straightforward:

mysqldump --single-transaction --quick --skip-extended-insert \
    --routines -umyuser -pmysecret dbname > /path/to/dumps/dir/dbname.dump;
rdiff-backup /path/to/dumps/dir/ /path/to/backup/dir/

The first command will use mysqldump to dump your database on a text file. It will also include DB routines, that by default would be excluded from the dump. Of course there are some parameters that you have to customize to your needs:

  • myuser and mysecret of course should be substituted with the MySQL username and password of a user that can have access to the structure and the data of the database;
  • dbname should be substituted with the actual name of your database;
  • /path/to/dumps/dir/ is the directory where the dump will be saved. You should substitute that with a real path on your system.

The second command will use rdiff-backup to create a first backup of your dump. The directory /path/to/backup/dir/ will contain an exact copy of your dump file, together with a rdiff-backup-data/ subdirectory. Here rdiff-backup will store the data it needs to save the history of changes made to your dump. The good news is that it will save only the actual changes happened on data over time, so your incremental backup will have the smallest possibile size on disk.

You can put the two lines above on a bash script and put it in a daily cron job. For instance, on Debian/Ubuntu Linux, you can create the script in the /etc/cron.daily directory and make it executable.

Every time it will create a new dump of your database with mysqldump, and then it will run rdiff-backup to update the history of your dump.

Purging changes that are too old

Now that you have a nice incremental backup in place you have to answer a simple question: how many days you want to keep a deleted record of your database before it will be lost forever?

It is indeed advisable to purge changes that are too old to be useful for a restore of your database. You can tell rdiff-backup to delete changes that are older than a specified amount of time, like this:

rdiff-backup --force --remove-older-than 7D /path/to/backup/dir/

This command will remove changes that are older than 7 days. You can refer to the TIME FORMATS section of the rdiff-backup man page to know what other time formats are allowed here.

How to restore a database from the backup

No backup can be considered reliable until you test a restore from it. Suppose that a data loss happened two days ago on the database called dbname. You can restore the database dump by issuing this command:

rdiff-backup -r 2D /path/to/backup/dir/dbname.dump /tmp/dbname_restored.dump

Your restored dump from 2 days ago will be saved in /tmp/dbname_restored.dump. The time formats allowed in this command are the same used in the –remove-older-than option described earlier.

Another way that I find very useful is restoring directly from a rdiff-backup increment file. Look into the directory rdiff-backup-data/increments/ and you’ll find many files for your database dump. Each file will have a timestamp appended in the file name, for example dbname.dump.2020-01-25T06:25:41+01:00.diff.gz.

The timestamp is exactly the date when the backup was taken, so to restore a backup taken on 25th January 2020 you can call the command in this way:

rdiff-backup /path/to/backup/dir/rdiff-backup-data/increments/dbname.dump.2020-01-25T06:25:41+01:00.diff.gz /tmp/dbname_restored.dump

Again your restored dump from 25th January 2020 will be saved in /tmp/dbname_restored.dump.

Putting it all together

I took a nice script written by Driantsov Alexander (unfortunately I cannot find a link to the author) and I adapted it a little bit, here you can see the complete script:

#!/bin/sh

# Priority for the MySQL dump and rdiff-backup Min: 19 Max: -20
BACKUP_PRIO="19"
# New dumps will be stored here
BACKUP_TMP_DIR="/path/to/dumps/dir/"
# Incremental backup of dumps will be stored there
BACKUP_DIFF_DIR="/path/to/backup/dir/"
# MySQL user to use for DB connection in mysqldump
MYSQL_USER="root"
# put the root password here at your own risk ;)
MYSQL_PASSWD="root-password"

if [ ! -d $BACKUP_TMP_DIR ]; then
    mkdir -p $BACKUP_TMP_DIR
fi

# Dump
databases=`mysql -u$MYSQL_USER -p$MYSQL_PASSWD -Bse "show databases" | grep -v mysql | grep -v information_schema`
for i in $databases;
do
    nice -n $BACKUP_PRIO mysqldump --single-transaction \
      --quick --skip-extended-insert --routines -u$MYSQL_USER -p$MYSQL_PASSWD $i \
      > $BACKUP_TMP_DIR/$i.dump;
done

# Diff
nice -n $BACKUP_PRIO rdiff-backup $BACKUP_TMP_DIR $BACKUP_DIFF_DIR
nice -n $BACKUP_PRIO rdiff-backup --force --remove-older-than 7D $BACKUP_DIFF_DIR

This script will backup incrementally all databases on your server using the approach described earlier. You’ll have to customize the variables BACKUP_TMP_DIR, BACKUP_DIFF_DIR, MYSQL_USER and MYSQL_PASSWD.

When a backup is not enough

In another post I explained how to repair a corrupted MySQL database, when a backup is not available or is too old.

Conclusion

In this tutorial I explained how to perform a simple MySQL incremental backup, that will help you in avoiding data losses. I used this method on many projects of mine and I can say that this is quite effective.

This method is good at taking backups of small and medium databases. In a follow up post I will present another method that can be used when the size of your database is large.

Happy backup (and restore)! 😉


Share: