MySQL backups with Percona's XtraBackup

Update: on December 10th 2009, Percona released Xtrabackup 1.0.

Most importantly, XtraBackup can do a backup that maintains transactional integrity without having to stop or lock the database. XtraBackup is mainly concerned with InnoDB tables because other tables don't offer transactional integrity anyway, and they are already served to an extent by mysqldump and the misleadingly named mysqlhotcopy which isn't actually very 'hot' at all!

Another advantage of Xtrabackup is that it backs up the actual database files, not an SQL dump, meaning that it takes a similar amount of space, but restores are much faster (it can just use the files, and doesn't have to rebuild them from scratch). Generally you'll use XtraBackup on a whole database server, not just a subset of its contents (the latest version now supports single-table backups, which I've not tried yet). Make sure you grab the latest version as though it works for me, it's still very much in development.

In practice you don't use XtraBackup directly, but via a wrapper script called InnoBackupEx, which takes care of various matters that are fiddly to handle manually. XtraBackup only handles InnoDB tables, but InnoBackupEx can also handle MyISAM tables, so it's ok to have a mix, though MyISAM offers neither transactional integrity or crash recovery (or at least nowhere near as far as InnoDB does), and requires locks during backups.

The essentials of InnoBackupEx' operation are these steps:

1. Copy the active database files (which may be in an unknown state)
2. Apply the current InnoDB transactional logs to them to bring into a consistent state at a known checkpoint
3. Save them as a snapshot, or into a tar stream, possibly on a remote machine

Step 2 requires intimate knowledge of database and log file internals, so rather than having code that replicates MySQL's functionality, it actually fires up another instance of mysqld to do it. This can be a little confusing if you happen to look at your processes at the time! What it's doing here is effectively a crash recovery process, and it can take a long time; how long is at least partly dependent on your log files size - big log files give better performance at the price of longer crash recovery, which here means slower backups. In my case, with a 140Gb database, it takes a bit under 4 hours.

Because the content of the backups may be dependent on the settings in use on the db server at the time, the backup process needs to use your my.cnf file, usually found at /etc/my.cnf or /etc/mysql/my.cnf(the latter is common on many distros, and if it's there, symlink it to /etc/my.cnf so that xtrabackup can find it), and in fact it embeds a copy of that file in the backup stream so that any new server can be made to match the original server's config exactly - this makes XtraBackup particularly useful for setting up replication slaves. It's generally a good idea to have innodb_file_per_table enabled as it keeps backup sizes down, reduces risk, lets you recover wasted space more easily, and keeps your options open for per-table disk layout optimisations.

If the backup is destined for a replication slave, you can add the --slave-info switch which will embed the log file name and position corresponding to the time the backup was taken. When you start up the slave, it can then start pulling events that have happened since then from the master.

After all this, the command that I use to backup a whole mysql server to a gzip compressed tar stream is this:

Notice that the command dos not specify where the database files are - this is obtained from your my.cnf file. When this runs you'll get output looking like this:

You can see from this log that the scanning of the log files is what takes all the time - be thankful that this is happening while you back up as it means you won't have to wait for it on restore! The process is that it runs the ibbackup program, which gets suspended while the logs are scanned, and when that's finished it resumes and copies the files to make the backup. If you're watching this output, you may find that it sometimes looks like it's stalled during log scanning. Don't worry, it hasn't, it just doesn't prioritise the output of the log info, and it will turn up eventually.

An alternative command to backup directly to another server without file compression (assuming that the other server is on the same LAN), which is the kind of thing you'd want to do to when setting up a slave:

The backup operation can impose quite high disk loads itself, but you can throttle it using the --throttle=<iops> switch, which is in IOs per second, and depends on your disk system - if you're on simple RAID-1, around 100 should be a fair balance. If you're running replication, it makes a lot of sense to use one of your slaves to run the backups on as it then imposes no load on your primary master.

From reading the docs it's not immediately obvious what you end up with after a backup; it's a complete MySQL data directory, i.e. what you'd typically find in /var/lib/mysql on most Linux distros. This makes restores really simple - it's a matter of moving any existing data directory out of the way and restoring the backup in its place, and again this is very handy for replication slaves. Although (if you use stream output) the result is a tar file, it doesn't act quite like "normal" tar archives, and if you do a tar tf to list the contents, it will seem to be empty. This is because it's not really a file collection, but a stream of raw data, and as such may contain lots of null bytes (char 0), so you need to specify the `i` switch to tar as well, and there is no point in trying to untar it yourself.

Restores

So your daily backups are running nicely and one of your servers dies or falls irreparably out of sync with its master (it happened to me!) and you need to restore a backup. Firstly, don't panic - you have another master that handles transparent failover using mmm, right?

You use the same innobackupex script as you used for the backup to do the restore, just with different options. It helps to give the restore process lots of memory with the --use-memory=nn, and mysqld isn't running during the restore process so you don't need to leave space for it. Firstly move your existing data directory out of the way (or delete it if you're low on space and sure your backup is OK - you've tested this procedure, right?!), then move the backup into place before running the restore operation. So here's a restore command (on a server with 32Gb RAM):

The machine is offline anyway, so you don't need to worry about it using up all available i/o capacity. Because the backup is already in a good and consistent state, the restore actually happens pretty fast - in my case only a few seconds - also it's not really a restore in the usual sense, it's really a "Confirm the correctness of the backup" step and it's still possible that the backup could fail at this point (for example if you backed up corrupted data), so it's a good idea to keep multiple backup generations.

To recap: XtraBackup gives you:

• Reliable (but possibly slow), consistent backups without disrupting a running service
• A simple method for setting up MyQSL replication slaves very quickly
• Fast, simple restores

May your backups go smoothly and never be needed!

Thanks to all at Percona for their excellent services, reviews of this article, the MySQL book , and of course, XtraBackup.

Update Oct 9th 2009

Don't set the xtrabackup backup directory to the same location as your compressed tar files as xtrabackup clears this directory before each run. I've amended the command line to reflect this. I also just spotted that there's an article on this exact same subject in the first issue (July '09) of osdbzine. Spotted a tweet from @timhaines: "xtrabackup just impressed the pants off me. Full db backup and it didn't affect site performance! BTW, I was exaggerating about the pants."