Percona XtraBackup is a tool from Percona that can be used to create physical backups. Its most important feature is that it operates while the server is running and requires no lock at all for InnoDB tables. Tables that use other storage engines only require a lock at the end of the copy. Percona XtraBackup supports incremental backups. Percona XtraBackup is available on the Percona website.
Percona XtraBackup consists of several components. The core component is the xtrabackup program. It embeds a modified version of InnoDB that is used to access the InnoDB tablespaces. The user usually does not invoke it directly. A Perl script called innobackupex performs the backup of other storage engines, table definition files, and triggers, and calls xtrabackup to back up the .ibd files. Percona XtraBackup is a complex tool. The xbcrypt command is a standalone tool that encrypts or decrypts backup files. In this book, we will only cover a general explanation of how to use innobackupex to create or restore complete and incremental backups. It is recommended that you read the documentation for more details on the XtraBackup features. The documentation is available on the Percona website at the following URL http://www.percona.com/doc/percona-xtrabackup/.
Percona XtraBackup is able to perform complete backups and partial backups. A partial backup is a copy that does not include all tables from all databases. After a first complete or partial backup is made, Percona XtraBackup can perform incremental backups. This section explains how to perform all the backup types.
To create a complete backup, we do not need any particular option. The following is a typical innobackupex invocation:
innobackupex --user=root --password=root /tmp/backup
Of course, the --user and --password options specify, respectively, the MariaDB user and password. The last argument is the directory that contains backup.
By default, a subdirectory is created within the backup directory, which contains the current backup. The directory's name will be the current datetime in the following format: 2014-04-10_16-30-18. This is generally a very practical way to archive backups. However, we may want to specify a path for this particular backup and not a general backup directory with an automatically named subdirectory. In this case, we can just add the --no-timestamp option.
By default, a backup is complete; that is, it includes all tables from all databases. However, it is also possible to specify which tables and databases must be included in the copy.
The --databases option can be used to specify databases and optionally, tables to be included. The value for this option is a space-separated list. Each argument can be a database or a table. When only the database is specified, all tables from that database are included. For example, to include all tables from db_1 and the table user from db_2, we can use this syntax:
innobackupex --user=root --password=root --databases="db_1 db_2.user" /tmp/backup
The list of databases and tables to be included can also be specified in a text file. The syntax for the list is the same, except that each database or table must be specified in a different line. The name and path of the file need to be passed to innobackupex using the --tables-file option, shown as follows:
innobackupex --user=root --password=root -–tables-file="tmp/backup/t_list" /tmp/backup
The --include option allows us to specify the table names as a regular expression, to be evaluated against fully specified table names:
innobackupex --user=root --password=root --include=^db1[.]tab[0-9] /tmp/backup
Percona XtraBackup copies the InnoDB files at different times with no locking at all. This means that, while a table was being copied, users could modify another table. As a result, the copied tables are probably not consistent with each other. Trying to restore an inconsistent backup would crash the server. To make the backup consistent, it needs to be prepared. The preparation is performed after the copy and does not require a connection to the server or access to any file stored in the server. The only limitation is that the version of xtrabackupex that performed a backup must be the same as the version of xtrabackupex that prepares that backup.
InnoDB has some transaction logs that can be used to retry or undo a transaction. Such logs are copied by Percona XtraBackup as part of the backup. During the preparation stage, the xtrabackup binary undoes some transactions and repeats other transactions. It can do this safely because it embeds a slightly modified InnoDB version. At the end of the process, all the .ibd files and the logs are perfectly consistent and ready to be imported into the server's data directory, if necessary.
To prepare a backup, we need to apply InnoDB logs. In fact, to prepare a backup, we just need to call innobackupex again with the --apply-logs option and the backup path:
xtrabackup --prepare /media/ext1/backup/2014-04-10_16-30-18
The preparation process may be slow for backups that include big InnoDB tables. Percona XtraBackup can prepare a backup more quickly if it uses a big amount of memory. The default amount of memory it uses is 100 MB, which is very low, because it could be used on the same server that runs MariaDB. To allow it to use more memory, we can specify the --use-memory option: --use-memory=2G.
If the preparation succeeds, the last line of the innobackupex output tells us the sequence number of the last log entry. If we plan to perform an incremental backup and Percona XtraBackup does not have access to this particular backup, we need to save that sequence number somewhere. This could happen, for example, because we are going to copy the backup on a removable device and delete it from the server.
The syntax to prepare a partial backup is slightly different. The following is a typical invocation:
xtrabackup --prepare --export /media/ext1/backup/2014-04-10_16-30-18
For each table not included in the backup, a warning might appear that informs us that the table is missing. This happens because the .frm files contain the definitions for those tables. We can safely ignore such warnings.
For each included table, a .exp file is created in the backup directory. Such files are required to restore partial backups.
To restore a backup, we can simply copy the needed files into the data directory. However, the xtrabackupex script provides an easier way to do this.
Percona XtraBackup cannot restore a complete backup into a running server. To restore a complete backup, we need to stop the server first. Then, we can call innobackupex with the --copy-back option and pass it the backup directory path. The innobackupex command automatically reads the path of the data directory from the configuration file, shown as follows:
innobackupex --copy-back /path/to/BACKUP-DIR