MySQL backups with Percona’s XtraBackup

MySQL backup is sometimes very hard to do effectively. MySQL provides various options for backup, but many of them are simply unsuitable for large systems, particularly if they need to remain active during backups. Percona’s XtraBackup is an open-source clone of InnoBase’s InnoDB Hot Backup utility. So what makes XtraBackup a better solution, and how does it work?

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!

Many people use LVM snapshots for backups as they offer many of the same advantages, however there are two downsides. Firstly they can adversely affect performance – while a snapshot is active, disk performance can take a big hit (though that may not be a problem on low-traffic sites). Secondly, although the captured snapshot may be in a known state as far as the file system is concerned, it may not contain information that was in memory at the time (or is held in as-yet-unapplied logs), and thus may require a long crash recovery operation at restore time (just when you’re having a little panic that your database died!) instead of at backup time (probably the middle of the night, while you’re sleeping).
Also, you can’t use LVM unless you started with it – adding LVM to a running server is extremely fiddly and difficult, in some cases impossible. LVM also imposes a whole new layer of acronyms and concepts to the process of disk access.

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 InnoDB 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:

/usr/bin/innobackupex-1.5.1 --slave-info --stream=tar /var/backups/xtrabackup | gzip -c -9 > /var/backups/mysql/backup.tar.gz

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:

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackup
           prints "innobackup completed OK!".

innobackupex: Using mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2
innobackupex: Using mysql server version 5.0.51a-3ubuntu5.4-log

innobackupex: Created backup directory /var/backups/mysql
090911 00:59:28  innobackupex: Starting mysql with options: --unbuffered
090911 00:59:28  innobackupex: Connected to database with mysql child process (pid=15052)
090911 00:59:32  innobackupex: Connection to database server closed

090911 00:59:32  innobackupex: Starting ibbackup with command: xtrabackup --backup --suspend-at-end --log-stream --target-dir=./ --throttle=150
innobackupex: Waiting for ibbackup (pid=15086) to suspend
innobackupex: Suspend file '/var/lib/mysql/xtrabackup_suspended'

xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 67108864
xtrabackup: use O_DIRECT
xtrabackup: Stream mode.
>> log scanned up to (185 2443877757)

090911 00:59:34  innobackupex: Continuing after ibbackup has suspended

innobackupex: Starting to backup InnoDB tables and indexes
innobackupex: from original InnoDB data directory '/var/lib/mysql'
innobackupex: Backing up as tar stream 'ibdata1'
>> log scanned up to (185 2443879075)
...
>> log scanned up to (185 2443879075)
tar: ibdata1: file changed as we read it
innobackupex: If you use GNU tar, this warning can be ignored.
innobackupex: Backing up files '/var/lib/mysql/mydb1/*.ibd' (39 files)
>> log scanned up to (185 2447627905)
...
>> log scanned up to (185 2455853862)
090911 04:44:19  innobackupex: Starting mysql with options: --unbuffered
090911 04:44:19  innobackupex: Connected to database with mysql child process (pid=9067)
>> log scanned up to (185 2455853862)
090911 04:44:23  innobackupex: Starting to lock all tables...
>> log scanned up to (185 2455854380)
...
>> log scanned up to (185 2455858345)
090911 04:44:53  innobackupex: All tables locked and flushed to disk

090911 04:44:53  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, and .opt files in
innobackupex: subdirectories of '/var/lib/mysql'
innobackupex: Backing up file '/var/lib/mysql/mydb2_survey/prefs.frm'
innobackupex: Backing up file '/var/lib/mysql/mydb2_survey/prefs.MYD'
>> log scanned up to (185 2455858345)
>> log scanned up to (185 2455858345)
innobackupex: Backing up file '/var/lib/mysql/mydb2_survey/prefs.MYI'
>> log scanned up to (185 2455858345)
...
>> log scanned up to (185 2455858355)
innobackupex: Backing up file '/var/lib/mysql/mydb2_survey/db.opt'
innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (52 files)
innobackupex: Backing up files '/var/lib/mysql/phpmyadmin/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (25 files)
innobackupex: Backing up files '/var/lib/mysql/mydb1/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (40 files)
innobackupex: Backing up file '/var/lib/mysql/mydb3_prefs/prefs.frm'
innobackupex: Backing up file '/var/lib/mysql/mydb3_prefs/prefs.MYD'
>> log scanned up to (185 2455858355)
...
>> log scanned up to (185 2455858355)
innobackupex: Backing up file '/var/lib/mysql/mydb3_prefs/prefs.MYI'
>> log scanned up to (185 2455858355)
...
>> log scanned up to (185 2455858355)
innobackupex: Backing up file '/var/lib/mysql/mydb3_prefs/db.opt'
innobackupex: Backing up file '/var/lib/mysql/work/jobs.frm'
innobackupex: Backing up file '/var/lib/mysql/work/jobs.MYD'
innobackupex: Backing up file '/var/lib/mysql/work/jobs.MYI'
innobackupex: Backing up file '/var/lib/mysql/work/db.opt'
090911 04:48:21  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '185:2455858355'
>> log scanned up to (185 2455858365)
xtrabackup: Transaction log of lsn (185 2443876945) to (185 2455858365) was copied.
090911 04:48:25  innobackupex: All tables unlocked
090911 04:48:25  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/var/backups/mysql'
innobackupex: MySQL binlog position: filename 'mysql-bin.000684', position 7411270
innobackupex: MySQL slave binlog position: master host '192.168.1.2', filename 'mysql-bin.000107', position 98
090911 04:48:26  innobackupex: innobackup completed OK!
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.

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:

innobackupex-1.5.1 --stream=tar /tmp/ --slave-info | ssh -C user@remotebox "tar xfi - -C /var/backups/mysql"

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):

rm -rf /var/lib/mysql/*
mv /var/backups/mysql/* /var/lib/mysql
innobackupex-1.5.1 --apply-log --use-memory=28G  /var/lib/mysql

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.”