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