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
rpm -ql libgcrypt
This tells me I have
usr/lib64/libgcrypt.so.20, so I downloaded
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
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
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.
Here you, copy the data to its original location. There is no
xtrabackup specific command, I guess you could just use
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.
-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.
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.