No-downtime MySQL backup (InnoDB/InnoBackupEx)

The high prevalence of MySQL databases lends to its use in increasingly diverse projects, including those that require nonstop operation and minimal recovery time. Standard tools such as mysqldump are inconvenient for use in large databases that can reach tens of gigabytes. If you can still export the data in the reasonable time of a few hours, the restore operation can take tens of hours. Also, using mysqldump to backup causes table locks (making downtime during backup) and can be followed by data inconsistency in backup copy. Data inconsistency may occur with transactions involving tables that have already been backed up with those that have not been copied.

Short description of Percona XtraBackup
- “The innobackupex tool is a Perl script that acts as a wrapper for the xtrabackup C program. It is a patched version of the innobackup Perl script that Oracle distributes with the InnoDB Hot Backup tool. It enables more functionality by integrating xtrabackup and other functions such as file copying and streaming, and adds some convenience. It lets you perform point-in-time backups of InnoDB / XtraDB tables together with the schema definitions, MyISAM tables, and other portions of the server”[1].
The main functional features of Percona XtraBackup are related to major improvements in the mechanism of the MySQL backup for data in InnoDB engine. Data in Archive/MyISAM tables are still copied using regular table locking and copying mechanisms.
Below, some usage cases and functional features of XtraBackup are briefly described.

1. Initial database copy
To create a full backup, use the script with the options needed to connect to the server and only one argument; the path to the directory where the backup will be stored: innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

Check the last line of the output for a confirmation message:
innobackupex: Backup created in directory '/path/to/BACKUP-DIR/2011-12-25_00-00-09'
innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 1946 111225 00:00:53 innobackupex: completed OK!

As an example, a 60GB database is backed up in 19 minutes without service interruption (because of no mission critical data inside MyISAM/Archive tables).
After compression (tar -cz), archive takes 14GB of disk space. Also, it should be noted that compressed XtraBackup archive takes twice the amount of space compared to the compressed database dump created by mysqldump utility.

A database copy created using XtraBackup can be used for further incremental backups or can be prepared as a fully operational MySQL base directory sufficient to run MySQL instance (for accessing backed up data or to make initial database for replication).

2. Incremental copy
Running incremental backup:
$ innobackupex --incremental /data/backups --incremental-basedir=BASEDIR, where BASEDIR is a directory holding full or incremental backup and “/data/backups” is a directory for storing newly created incremental backups.
Incremental backups contain all changes made in the database from the last change in backup time up to the present.
For further details about incremental backups refer document [2].

3. Applying incremental copies to initial backup (Backup in-time forward)
Created incremental copies that can be used to advance in time from initial database copy. As an example, initial copy was created 10 days ago, applying incremental copies from the next 4 days will update database copy to condition about 6 days ago.
To apply incremental backups to initial backups execute innobackupex with the following parameters: innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-1, where “INCREMENTAL-DIR-1” is a directory holding an incremental copy with changes next after data copy in “BASE-DIR”.

4. Prepare database copy to production execution
In case of database restore as production replacement or for additional MySQL instance, backed-up copy requires preparation for production use:
innobackupex --apply-log BASE-DIR
This command is used to cleanup the backup directory, apply completed and roll-back incomplete transactions, and prepare BASE-DIR as MySQL base directory.

MySQL server can be run over this directory and serve requests.

5. Make database copy for Initial Replication.
Database replication requires binlog logging turned to “on” on source database. In this case, backed-up copy contains information about binlog position and can be used to start replication with master site that is the source of the copy. File xtrabackup_binlog_pos_innodb inside backup directory holds information about binlog position and is the source for values in command “CHANGE MASTER TO” that is used to configure replication with master site.
As an example, if file xtrabackup_binlog_pos_innodb contains “./mysql-bin.000037 941022681”, then command must be:
change master to master_log_file=”mysql-bin.000037”,master_log_pos=941022681;

These XtraBackup usage schemas can be successfully used to make transaction-consistent backup of large databases (tens-hundreds of gigabytes). The backup copy can be created at any time without service interruption and can be used as source data for replication and for database backup or online database copy.

[1] http://www.percona.com/software/percona-xtrabackup/
[2] http://www.percona.com/doc/percona-xtrabackup/innobackupex/incremental_b...