MySQL Enterprise Backup User's Guide (Version 8.0.17)

4.3.5 Making a Partial Backup

Note

To facilitate the creation of partial backups, MySQL Enterprise Backup has introduced two new options for partial backup since version 3.10: --include-tables and --exclude-tables. The new options are intended for replacing the older options like --include, which are incompatible with the new options and will be deprecated in the upcoming releases. In the discussions below we assume the new options are used for partial backups. For reference purpose, we have included information on the older options at the end of this section in Making a Partial Backup with Legacy Options.

By default, all the files under the database subdirectories in the data directory are included in the backup, so that the backup includes data from all MySQL storage engines, any third-party storage engines, and even any non-database files in that directory. This section explains options you can use to selectively back up or exclude data.

There are various ways to create different kinds of partial backup with MySQL Enterprise Backup:

For syntax details on all the options involved, see Section 18.8, “Partial Backup and Restore Options”.

Important

Typically, a partial backup is more difficult to restore than a full backup, because the backup data might not include the necessary interrelated pieces to constitute a complete MySQL instance. In particular, InnoDB tables have internal IDs and other data values that can only be restored to the same instance, not a different MySQL server. Always fully test the recovery procedure for any partial backups to understand the relevant procedures and restrictions.

Important

Because the InnoDB system tablespace holds metadata about InnoDB tables from all databases in an instance, restoring a partial backup on a server that includes other databases could cause the system to lose track of those InnoDB tables in the other databases. Always restore partial backups on a fresh MySQL server instance without any other InnoDB tables that you want to preserve.

The following are some command samples for partial backups.

Including all tables with names starting with emp into the backup:

mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --include-tables="\.emp" \
 backup-to-image

Taking a backup of all tables except tables from the mysql and performance_schema databases:

mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --exclude-tables="^(mysql|performance_schema)\." \
 backup-to-image

Taking a backup of all tables in the sales database, but excludes the table with the name hardware

mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 ---backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --include-tables="^sales\." --exclude-tables="^sales\.hardware$" \
 backup-to-image

Taking a backup of all tables in the sales reps database, but excludes the table with the name euro-asia (special characters like spaces or dashes are supported by the partial backup options since release 3.12.1):

mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --include-tables="^sales reps\." --exclude-tables="^sales reps\.euro-asia" \
 backup-to-image

Backing up all InnoDB tables:

mysqlbackup --defaults-file=/home/dbadmin/my.cnf --only-innodb backup-to-image

You can also make compressed and other kinds of selective backups by using the appropriate command options.

Making a Partial Backup with the Legacy Options

Important

Information in this subsection is only for using the legacy option of --include, which will be deprecated in the upcoming issues. For creating partial backups, it is strongly recommended that the new options of --include-tables and --exclude-tables be used instead. Note that you cannot combine the legacy and the new partial-backup options in a single command.

Note

Typically, a partial backup is more difficult to restore than a full backup, because the backup data might not include the necessary interrelated pieces to constitute a complete MySQL instance. In particular, InnoDB tables have internal IDs and other data values that can only be restored to the same instance, not a different MySQL server. Always fully test the recovery procedure for any partial backups to understand the relevant procedures and restrictions.

With its --include option, mysqlbackup can make a backup that includes some InnoDB tables but not others:

This operation requires the tables being left out to be stored in separate table_name.ibd files. To put an InnoDB table outside the system tablespace, create it while the innodb_file_per_table MySQL configuration option is enabled. Each .ibd file holds the data and indexes of one table only.

Those InnoDB tables created with innodb_file_per_table turned off are stored as usual in the InnoDB system tablespace, and cannot be left out of the backup.

For each table with a per-table data file a string of the form db_name.table_name is checked against the regular expression specified with the --include option. If the regular expression matches the complete string db_name.table_name, the table is included in the backup. The regular expression syntax used is the extended form specified in the POSIX 1003.2 standard. On Unix-like systems, quote the regular expression appropriately to prevent interpretation of shell meta-characters. This feature has been implemented with the RE2 regular expression library.

The backup directory produced contains a backup log file and copies of InnoDB data files.

IMPORTANT: Because the InnoDB system tablespace holds metadata about InnoDB tables from all databases in an instance, restoring a partial backup on a server that includes other databases could cause the system to lose track of those InnoDB tables in other databases. Always restore partial backups on a fresh MySQL server instance without any other InnoDB tables that you want to preserve.

Example 4.23 Making an Uncompressed Partial Backup of InnoDB Tables

In this example, we have configured MySQL so that some InnoDB tables have their own tablespaces. We make a partial backup including only those InnoDB tables in test database whose name starts with ib. The contents of the database directory for test database are shown below. Of these 10 tables six (alex1, alex2, alex3, blobt3, ibstest0, ibstest09) are stored in per-table data files (.ibd files).

$ ls /sqldata/mts/test
alex2.ibd  ibstest0.ibd alex1.ibd  blobt3.ibd  alex3.ibd  ibtest09.ibd

We run the mysqlbackup with the --include option:

# Back up some InnoDB tables.
$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --include="^test\.ib.*"

# Contents in the backup directory's subdirectory for the test database:
$ ls /sqldata-backup/test
ibstest0.ibd   ibtest09.ibd

The backup directory's subdirectory for the test database contains only backups of ibstest0 and ibtest09 tables, because other InnoDB tables do not match the include pattern ^test\.ib.*.


Example 4.24 Making a Compressed Partial Backup

We have configured MySQL so that every InnoDB table has its own tablespace. We make a partial backup including only those InnoDB tables whose name starts with alex or blob. The contents of the database directory for test database is shown below.

$ ls /sqldata/mts/test
alex2.ibd  ibstest0.ibd  alex1.ibd  blobt3.ibd  alex3.ibd  ibtest09.ibd

We run mysqlbackup with the --compress and --include options:

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --compress \
  --include=".*\.(alex|blob).*"

The backup directory for the database test is shown below. The .ibz files are compressed per-table data files.

$ ls /sqldata-backup/test
alex1.ibz   alex2.ibz   alex3.ibz   blobt3.ibz