Create a partial backup

xtrabackup supports taking partial backups when the innodb_file_per_table option is enabled. There are multiple ways to create partial backups:

• matching the tables names with a regular expression

• providing a list of table names in a file

• providing a list of databases

Warning

• Restore: Do not copy back the prepared backup. Restore partial backups by importing the tables (see Restore a partial backup and Restore single tables for the procedure), not by using the –copy-back option. Copying back the files is possible in some scenarios but can lead to database inconsistencies and is not recommended.

• Incremental backups: Do not run incremental backups after a partial backup.

For the purposes of this manual page, we assume that there is a database named test which contains tables named t1 and t2.

Warning

If any of the matched or listed tables is deleted during the backup, xtrabackup fails.

There are multiple ways to specify which part of the whole data to back up:

• Use the --tables option to list the table names

• Use the --tables-file option to list the tables in a file

• Use the --databases option to list the databases or specific tables

• Use the --databases-file option to list the databases or specific tables in a file

Do not use --tables and --databases together

Do not use both --tables and --databases in the same command. The resulting backup does not reliably contain the data you intended to back up. When you restore, you risk data loss or discover that critical tables were never included. A backup that does not match your intent risks data loss.

The two options use different filtering mechanisms and conflict when used together:

• --tables uses regular expressions and implies a partial backup.
• --databases uses exact matching and implies a full backup of the listed databases.

For example, a database listed in --databases is fully backed up even when you wanted only specific tables from that database via --tables; or tables you expected to be included are omitted. To combine specific tables from one database with full backups of other databases, use --tables-file with --databases instead of --tables (see Filtering behavior with examples).

The –-tables option

The first method involves the xtrabackup --tables option. This option accepts either: * A comma-separated list of fully qualified table names in the format database.table (for example, db1.t1,db1.t2,db2.t3) * A POSIX regular expression surrounded by single quotes that XtraBackup matches against the fully-qualified database name and table name in databasename.tablename format

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

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

To back up only the test.t1 table, 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. XtraBackup backs up only the tables named in the file and matches names exactly, case-sensitive, with no pattern or regular expression matching. Use the databasename.tablename format and fully-qualified table names.

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

The --databases and -–databases-file options

Both options specify which databases or tables to back up and use the same filtering logic. The only difference is where you supply the list:

	--databases	--databases-file
Where	Comma-separated list on the command line	Path to a file, one database or database.table per line
Format	mysql,mydb,mydb.t1,mydb.t2	Same databasename or databasename.tablename format, one per line
Use when	Short list, one-off command	Long list, or a list you reuse in a script

The --databases option accepts a comma-separated list of database names. To include all tables in a database, add .* after the database name (for example, mydb.*). Regular expressions are not supported.

In addition to your selected databases, make sure to specify the mysql, sys, and performance_schema databases. You need these databases when restoring with xtrabackup --copy-back.

Note

A backup runs over a period of time. If a table is created while the backup is running, XtraBackup may copy that table into the backup even though you did not list that table in --databases or --databases-file, but only if (1) the table’s database is already in the scope of your filter (so the new table falls under a database you included), and (2) the table exists when the backup processes that database.

xtrabackup --backup --databases='mysql,sys,performance_schema,test' --target-dir=/data/backups/

Filtering behavior with examples

Do not use --tables and --databases in the same command. The backup may not contain the data you intended: XtraBackup applies --databases first as a high-level filter, and that filter can override your --tables regex. You can end up with a backup that omits tables you needed or includes tables you did not intend to back up, which risks data loss when you restore.

How filtering works

To understand why using --tables and --databases in the same command fails, consider the order of checks. When determining whether to back up a table, XtraBackup runs a database check first, then a table check.

Database check (--databases)

XtraBackup checks whether the database is included or excluded.

• If you use --databases, any database not in the list is skipped immediately.
• If a database is in the --databases list, XtraBackup normally includes every table in that database. The only exception: when you also use --tables-file, XtraBackup includes only the tables named in that file for any database that appears in both the list and the file—not all tables in that database.

Example with --tables-file (full databases in --databases, specific tables in the file): To back up all of mysql, performance_schema, and sys, and only testdb.table1 and testdb.table2, put the two table names in a file. List only the full databases in --databases; do not list testdb there.

echo -e "testdb.table1\ntestdb.table2" > tables.txt
xtrabackup --backup --databases="mysql,performance_schema,sys" --tables-file=tables.txt --target-dir=/data/backups/

The database check includes mysql, performance_schema, and sys from --databases. The tables in the file (testdb.table1, testdb.table2) are included by the table check. Result: every table from the three system databases; from testdb, only table1 and table2.

Table check (--tables / --tables-file)

For each database that passed the database check, XtraBackup decides which tables to copy. The table check uses either --tables or --tables-file:

• --tables: You supply a comma-separated list of database.table names or a POSIX regular expression. XtraBackup matches each table’s full name against that list or regex. Only matching tables are copied.

• --tables-file: You supply a path to a file with one database.table per line (exact match, no regex). XtraBackup copies only the tables named in the file. Any database that has tables in the file is limited to those tables, even if that database is also in --databases.

When both --databases and --tables-file are used: databases in --databases are included, but for any database that also has tables in the file, only the tables in the file are copied (not all tables in that database). When only --databases is used (no --tables-file): every table in each listed database is copied.

Solution: combine specific tables and whole databases

To back up specific tables from one database (for example, testdb.table1, testdb.table2) and full backups of other databases (for example, mysql, performance_schema, sys), use one of the two methods below. Do not use both --tables and --databases in the same command. For that goal, mixing them fails:

• Case 1: xtrabackup --backup --tables="testdb.table1" --databases="mysql" — testdb is not in --databases, so the database is skipped. No tables from testdb are included.
• Case 2: xtrabackup --backup --tables="testdb.table1" --databases="mysql,testdb" — testdb is in --databases. XtraBackup includes all tables in testdb (table1, table2, and table3), ignoring the --tables regex.

Use one of the following methods instead.

Method 1: Using --tables-file (recommended)

Create a file listing the specific tables, one per line:

testdb.table1
testdb.table2

Run XtraBackup with both --tables-file and --databases. Do not list the database of those specific tables in --databases; --tables-file supplies the table list for that database. List only the databases you want in full (for example, system databases):

xtrabackup --backup --tables-file=tables.txt --databases="mysql,performance_schema,sys" --target-dir=/data/backups/

In this configuration, --tables-file backs up only the listed tables from testdb, and --databases backs up the system databases in full.

Avoid duplication

If you use --tables-file to back up specific tables from a database (for example, testdb), do not list that database in --databases. Listing that database would override the partial selection and back up the entire database.

Method 2: Using --databases for everything

You can list specific tables in --databases using the database.table format. This backs up the system databases in full and only the listed tables from testdb:

xtrabackup --backup --databases="mysql,performance_schema,sys,testdb.table1,testdb.table2" --target-dir=/data/backups/

For a full backup of a single database, add .* after the database name (for example, mydb.*). See the databases option reference.

Earlier example (sales and mysql)

You want only sales.orders and sales.order_items, and also the full mysql database. If you use both --tables and --databases:

xtrabackup --backup --databases='mysql,sales' --tables='^sales\.(orders|order_items)$' --target-dir=/data/backups/

the backup may contain only the two sales tables, or all tables in sales and mysql, or something in between. The result is implementation-dependent and cannot be trusted. Do not use this combination; use --tables-file with --databases instead.

The --databases-file option

The –databases-file option specifies a file that can contain multiple databases and tables in the databasename[.tablename] format, one element name per line in the file. XtraBackup matches names exactly, case-sensitive, with no pattern or regular expression matching.

Note

A backup runs over a period of time. If a table is created while the backup is running, XtraBackup may copy that table into the backup even though you did not list that table in --databases-file, but only if (1) the table’s database is already in the scope of your filter (so the new table falls under a database you included), and (2) the table exists when the backup processes that database.

Next, prepare the backup in order to restore the backup.