Backing up MySQL with Percona XtraBackup

The logical backup created by mysqldump was just 430MB. but for testing I wanted to repeatedly tear down and restore the same database. The restore was too slow even after disabling foreign key checks and unique checks.

I decided to try Percona XtraBackup. Here’s how I completed a full backup and restore. For incremental backups, you must do things a little differently, but I won’t handle incremental backups in this post.

Installing XtraBackup on Fedora

I used Fedora Linux. To pick the right tarball, check the version of the libgcrypt shared library module on your system. On Fedora, I can use rpm to query the files in the libgcrypt package:

rpm -ql libgcrypt

This tells me I have usr/lib64/libgcrypt.so.20, so I downloaded percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt20.tar.gz.

You also need the DB::MySQL Perl module installed. I installed the version in the Fedora repository:

sudo dnf install perl-DBD-MySQL

Backing up the database

You’re ready to create a full backup of the database. The MySQL server must be up and running as xtrabckup needs to connect to it.

xtrabackup --backup --target-dir=localdbbackup 
 --datadir=<mysql data directory> -P 3306 --host 127.0.0.1 --user <user> -p

The MySQL data directory is where MySQL stores the files containing the database data. I followed the instructions for installing MySQL from a tarball on Linux, and the data directory ended up to be /usr/local/mysql/data.

xtrabackup connects to the database as a user. Be sure the user has sufficient privileges to do the backup. As it was just a test database on my machine, I used root.

Now, the procedure begins to differ depending on whether you only want to do a full backup or also incremental backups. If you don’t plan to make incremental backups, run

~/bin/percona-xtrabackup-2.4.12-Linux-x86_64/bin/xtrabackup
 --prepare --target-dir=<local> 

Now the backup is ready to restore.

Restoring

Here you, copy the data to its original location. There is no xtrabackup specific command, I guess you could just use cp but rsync is faster. The xtrabackup docs suggest:

rsync -rvt --exclude 'xtrabackup_checkpoints' 
 --exclude 'xtrabackup_logfile' ./ /usr/local/mysql/data  

This command assumes you cd-ed inside the directory that contains the backup. In the rsync options, -r stands for a recursive copy and -t for preserving modification times. -v is verbose output.

Backing up again

xtrabackup refuses to overwrite an existing backup, so pass it a new directory every time you create a new back up.

Saving time

Backups and restores are faster than with logical backups created by mysqldump. On the local machine, the restore took a minute instead of thirty. But xtrabackup requires access to the file storage, so you can’t use it to back up a database stored on a platform-as-aservice MySQL instance, like Amazon RDS.