Taking a hot backup of a MySQL database with XtraBackup

A hot backup means you don’t have to take the database offline or stop sending traffic to it. This is especially useful for creating new replication slaves. A hot backup takes minutes vs the hours a dump/import would take. Ideally, all the tables in the database need to be InnoDB; however, Percona has adapted a script (innobackupex) to also backup MyISAM tables (if there are MyISAM tables then you probably should stop sending traffic to it as those require table locks). What makes XtraBackup so nice (besides being free) is that it logs C_UD statements to applied during restoration and it can do incremental backups as well.

First up, taking a backup. Make sure the “datadir” parameter is defined in your my.cnf

innobackupex-1.5.1 --slave-info --defaults-file=/var/data/mysql/my.cnf \
	--databases=mydb /var/data/backups

As I am taking this from a replication slave, I want it to log the current position in the master’s binary log: “–slave-info”. My my.cnf is not in the default location “/etc/my.cnf” so I need to specify its location. Finally, I want it to save the backup in my current working directory. Fairly straightforward I think.

Now, it doesn’t backup your actual my.cnf, so you’ll need to backup that separately (hopefully you already have it somewhere else).

To restore this backup actually requires 2 steps.
First, the backup needs to prepped. Which means to apply any C_UD statements that were logged during the backup.

innobackupex-1.5.1 --defaults-file=/var/data/mysql/my.cnf --apply-log \
	--use-memory=42G /var/data/backups/2010-07-15_18-12-07/

The “–use-memory” parameter is like the “innodb_buffer_pool_size” parameter. Give it approximately 80% of your free memory. Depending on how active the server was during the time of the backup, this may take a few minutes.

Now, the actual restore (which is really just copying files to their appropriate locations).

innobackupex-1.5.1 --defaults-file=/var/data/mysql/my.cnf \
	--copy-back /var/data/backups/2010-07-15_18-12-07/

It uses the datadir (and any custom locations for data/log files) defined in the my.cnf to copy files back to their respective locations.

Finally, in your datadir you will see the file “xtrabackup_slave_info”. It contains the master’s log file and position where the backup ends at.

Here’s a little trick to save the trouble of copying the backup between servers. Innobackupex also supports stream mode (via tar). I like netcat for internal network copying, but ssh is another alternative if you’re worried about data stream security. Also, if you’re not on a GNU machine, be sure to install gtar because it needs to ignore 0s (the ‘i’ option).

Destination Server:

cd /var/data/backups
nc -l 9999 | tar xfi -

Source Server:

innobackupex-1.5.1 --slave-info --defaults-file=/var/data/mysql/my.cnf --databases=mydb \
	--stream=tar /var/data/backups/ | nc 9999

Be sure to start the netcat listener on the destination server before executing the backup command on the source server (and that you use a port that is open). Also, don’t forget to copy your my.cnf to your new server.
Now you can apply-log and copy-back on the destination server.

To see the full capabilities of XtraBackup and Innobackupex, read through the manual/how-to here