For MySQL Enterprise Backup 8.0.20 and later: Table-Level
Recovery (TLR) allows selected tables (or schemas) to be
restored from a backup (be it a full backup, a partial backup,
or a backup created using
tablespaces (TTS)) using the
--exclude-tables options. The
feature is also known as partial restore in
this manual. Here are some general requirements for performing a
TLR or partial restore:
The destination server must be running.
The required parameters for connecting to the server (port number, socket name, etc.) are provided as command-line options for mysqlbackup, or are specified in the
[client]section of a defaults file.
The destination server must be using the same page size that was used on the server on which the backup was made.
The innodb_file_per_table option must be enabled on the destination server.
For non-TTS backups: The tables being restored must already exist on the destination server, in the same table definition.
For TTS backups: The tables being restored must not already exist on the destination server.
While it is not necessary to specify the
--datadiroption when partially restoring a backup, if the option is specified, its value must match that of the target server, or the restore operation will fail (when restoring a TTS backup with release 8.0.16 or earlier, the
--datadiroption is required).
Here are some limitations for a TLR or partial restore:
Partial restores cannot be performed with incremental backups.
Binary, relay, and undo logs are not restored.
For non-TTS backups only, these additional limitations apply:
After partial restores, tables could contain changes from uncommitted transactions.
The auto-increment values of the restored tables for a partial restore might not be the same as they were at the end of the backup process.
Encrypted InnoDB tables cannot be included in a partial restore.
The following command restores the table
pets schema from the backup:
Example 5.7 Restoring A Selected Table from an Image Backup
mysqlbackup --socket=/tmp/restoreserver.sock --include-tables="^pets\.cats" --backup-dir=/dba/backuptmp \ --backup-image=/dba/my.mbi copy-back-and-apply-log
The following command restores all tables in the “sales” database from the backup, but excludes the table with the name “hardware” :
Example 5.8 Restoring Selected Tables in a Schema from an Image Backup
mysqlbackup --socket=/tmp/restoreserver.sock --include-tables="^sales\." \ --exclude-tables="^sales\.hardware$" --backup-dir=/dba/backuptmp --backup-image=/dba/my.mbi \ copy-back-and-apply-log
Also see Section 5.1.5, “Restoring Backups Created with the
Option” for additional
information on partial restores using TTS backups.
For MySQL Enterprise Backup 8.0.19 and before, partial restore is only supported for TTS backups.