Percona xtrabackup A partial backup of the database

Xtrabackup can also implement partial backups, that is, to back up only one or some of the specified databases or some or some of the tables in a database. However, to use this feature, you must enable the innodb_file_per_table option, which is to save each table as a separate file. It also does not support the--stream option, which means that data is not supported to be piped to other programs for processing.


In addition, restoring a partial backup is also different from restoring all of the data, that is, you cannot directly copy back to the data directory by simply prepared a partial backup using the--copy-back option, but rather by importing the direction of the table to achieve the restore. Of course, in some cases, partial backups can also be restored directly through--copy-back, but the majority of the data that is restored in this way results in inconsistent data, so it is not recommended in any way.


(1) Create a partial backup

There are three ways to create a partial backup: a regular expression (--include), an enumeration table file (--tables-file), and a list of databases to back up (--databases).

(a) Use of--include
When using--include, it is required to specify the full name of the table to be backed up, i.e., the form databasename.tablename, such as:
# Innobackupex--include= ' ^mageedu[. Tb1 '/path/to/backup

(b) Use of--tables-file
The parameter for this option needs to be a file name with each row containing the full name of the table to be backed up, such as:
# echo-e ' mageedu.tb1\nmageedu.tb2 ' >/tmp/tables.txt
# Innobackupex--tables-file=/tmp/tables.txt/path/to/backup

(c) Use of--databases
This option takes a data name, and if you want to specify multiple databases, you need a space between them, and when you specify a database, you can specify only one of the tables. In addition, this option can also accept a file as a parameter, and each action in the file is an object to be backed up. Such as:
# Innobackupex--databases= ' mageedu.tb1 testdb "/path/to/backup

(2) Finishing (preparing) Partial backup

The process of prepare partial backups is similar to the process of exporting tables, using the--export option:
# Innobackupex--apply-log--export/pat/to/partial/backup

During the execution of this command, Innobackupex calls the Xtrabackup command to remove the missing table from the data dictionary, so there are many warning messages about the "table does not exist" class. It also shows information about creating an. exp file for the table that exists in the backup file.


(3) Restore partial backup

The process of restoring a partial backup is the same as importing a table. Of course, you can also restore directly to the data directory by copying a backup of the prepared state directly, not requiring the data directory to be in a consistent state at this time.

Percona xtrabackup A partial backup of the database