Creating Partial Backups¶

There are two ways of specifying which part of the whole data will be backed up: enumerating the tables in a file (--tables-file) or providing a list of databases (--databases).

The --tables Option¶

The first method involves the xtrabackup --tables option. The option’s value is a regular expression that is matched against the fully qualified tablename, including the database name, in the form databasename.tablename.

To back up only tables in the test database, you can use the following command:

$ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
--tables="^test[.].*"

To back up only the table test.t1, you can use the following command:

$ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
--tables="^test[.]t1"

The --tables-file Option¶

The --tables-file option specifies a file that can contain multiple table names, one table name per line in the file. Only the tables named in the file will be backed up. Names are matched exactly, case-sensitive, with no pattern or regular expression matching. The table names must be fully qualified, in databasename.tablename format.

$ echo "mydatabase.mytable" > /tmp/tables.txt
$ xtrabackup --backup --tables-file=/tmp/tables.txt

The --databases and --databases-file options¶

xtrabackup --databases accepts a space-separated list of the databases and tables to backup in the format databasename[.tablename]. In addition to this list make sure to specify the mysql, sys, and performance_schema databases. These databases are required when restoring the databases using xtrabackup --copy-back.

$ xtrabackup --databases='mysql sys performance_schema ...'

xtrabackup --databases-file specifies a file that can contain multiple databases and tables in the databasename[.tablename] form, one element name per line in the file. Names are matched exactly, case-sensitive, with no pattern or regular expression matching.

Preparing Partial Backups¶

The procedure is analogous to restoring individual tables : apply the logs and use the --export option:

$ xtrabackup --prepare --export --target-dir=/path/to/partial/backup

When you use the xtrabackup --prepare option on a partial backup, you will see warnings about tables that don’t exist. This is because these tables exist in the data dictionary inside InnoDB, but the corresponding .ibd files don’t exist. They were not copied into the backup directory. These tables will be removed from the data dictionary, and when you restore the backup and start InnoDB, they will no longer exist and will not cause any errors or warnings to be printed to the log file.

An example of the error message you will see during the prepare phase follows.

InnoDB: Reading tablespace information from the .ibd files...
101107 22:31:30  InnoDB: Error: table 'test1/t'
InnoDB: in InnoDB data dictionary has tablespace id 6,
InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.

Restoring Partial Backups¶

Restoring should be done by restoring individual tables in the partial backup to the server.

It can also be done by copying back the prepared backup to a “clean” datadir (in that case, make sure to include the mysql database). System database can be created with:

$ sudo mysql_install_db --user=mysql