Jump to Navigation

MySQL: Backups using xtrabackup

This is a guide about creating MySQL backups by using Percona xtrabackup.

Using xtrabackup backup has the following characteristics:

  • Online backup;
  • Full backup;
  • All databases and tables are always backed up and restored1;
  • For both MyISAM and InnoDB tables;
  • Must be run locally
  • A short read lock at the end of the backup.

Creating a backup

To create a consistent, full backup, on a database with MyISAM and/or InnoDB tables, use:

# Connection to MySQL. Use long options.
CONN="--user root --password root"
# Directory to put the backup in
innobackupex $CONN --backup --no-timestamp --rsync --slave-info --safe-slave-backup $TARGETDIR

Options used:

  • Connection options (--user, --password, --socket, --host, --port): settings to connect to the database. Note that the options are the same as for calling mysql, except that you need to use the long variants.
  • --backup: to indicate that this is a backup operation.
  • --no-timestamp: put the backup in TARGETDIR, instead of in a time-stamped subdirectory of TARGETDIR.
  • --rsync: use rsync to copy non-InnoDB files. This helps keep the database lock time down.
  • --slave-info: If this is a slave server, generate a file xtrabackup_slave_info. Ignored for non-slave servers.
  • --safe-slave-backup: if this is a slave server, temporarily stop the SQL replication thread. Ignored for non-slave servers.

The InnoDB tables will be backed up using xtrabackup. Next, the database will be locked using a read lock and all other database files will be copied.

Creating an incremental backup

To create a consistent incremental backup based on a full or incremental backup created earlier, use:

# Connection to MySQL. Use long options.
CONN="--user root --password root"
# Directory the full or incremental backup we base this incremental on was created in
# Directory to put the backup in
innobackupex $CONN --backup --incremental --incremental-basedir=$PREVDIR \
--no-timestamp --rsync --slave-info --safe-slave-backup $TARGETDIR

This will create an incremental backup of the InnoDB tables, and a full backup of all other database files.

This adds the following options:

  • --incremental: to make this backup an incremental backup
  • --incremental-basedir: the location of the previous full or incremental backup upon which this backup will be based.

Instead of using --incremental-basedir, you can also use --incremental-lsn=LSN, with the LSN as found in the file xtrabackup_info of the backup you want to use as a base, in the field innodb_to_lsn. This is useful if you do not have the base backup online.

Restoring a backup

Before a backup is ready to be restored, it has to be "prepared". The backup as written by xtrabackup is not yet internally consistent; any changes that happened during the backup have been registered, but not yet applied to the database files. Preparing can happen on any server2 and no connection to or files of the MySQL instance that was backed up are necessary.

To prepare a full backup, use:

# Directory the backup is found
# Memory to use. If you have the memory, more is better.
innobackupex --apply-log --use-memory=$MEM $BACKUPDIR

Options used:

  • --apply-log: to apply all completed transactions that were active during the backup and to rollback any non-completed transactions.
  • --use-memory: the amount of memory to use during preparing. The more memory is available the faster the prepare process will finish. Default is 100M.

To restore the full backup you should:

  • Stop MySQL;
  • Clean the datadir (ususally /var/lib/mysql) of all files;
  • Issue a innobackupex --copy-back BACKUPDIR;
  • Issue a chown -R mysql /var/lib/mysql;
  • Start MySQL.

Note that, if your binlogs are in the datadir (/var/lib/mysql), they will get lost by this procedure. Save them first if you want to do a roll forward.

Restoring an incremental backup

Before an incremental backup can be restored, it has to be "prepared". In this case, that means: applying all incremental backups one by one to the the base backup. This will overwrite the base backup and make it useless for other restores. Making a copy of it is a good idea. The incremental backup directories will not be changed.

# Directory the base backup is found
INCREMENTALDIRS="/var/tmp/mysql-backup-incr1 /var/tmp/mysql-backup-incr2"
# Memory to use. If you have the memory, more is better.
innobackupex --apply-log --use-memory=$MEM --redo-only $BACKUPDIR
for incrdir in $INCREMENTALDIRS; do
innobackupex --apply-log --use-memory=$MEM --redo-only --incremental-dir=$incrdir $BACKUPDIR
innobackupex --apply-log --use-memory=$MEM $BACKUPDIR

Additional options used:

  • --redo-only: when combined with --apply-log, apply completed transactions but do not roll back non-completed transactions.
  • --incremental-dir: the directory in which the incremental backup is found.

Restore the backup like you would with a normal full backup.

  • 1. Not completely true, but partial restores are a bit iffy
  • 2. as long as you have an identical versions of Percona xtrabackup as on the server the backup was created

Technical_article | by Dr. Radut