5.1.4 Restoring Backups Created with the --use-tts Option

There are some special requirements for restoring backups created with transportable tablespaces (TTS) (that is, created with the --use-tts option):

• The destination server must be running.

• Make sure that 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.

• The tables being restored must not exist on the destination server.

• A restore fails if the InnoDB file format of a per-table data file (.ibd file) to be restored does not match the value of the innodb_file_format system variable on the destination server. In that case, use the --force option with the restore commands to change temporarily the value of innodb_file_format on the server, in order to allow restores of per-table data files regardless of their format.

When restoring a single-file backup created with the option setting --use-tts=with-minimum-locking, the folder specified with --backup-dir, besides holding temporary output, status files, and metadata, is also used for extracting temporarily all the tables in the backup and for performing an apply-log operation to make the data up-to-date before restoring them to the server's data directory.

Selected tables can be restored from a backup created with transportable tablespaces (TTS) using the --include-tables and --exclude-tables options. The following command restores all tables in the “sales” database from the backup, but excludes the table with the name “hardware” :

Example 5.7 Restoring Selected Tables from a TTS Image Backup

mysqlbackup --socket=/tmp/restoreserver.sock --datadir=/logs/restoreserverdata --backup-dir=/logs/backup \
  --include-tables="^sales\." --exclude-tables="^sales\.hardware$" copy-back-and-apply-log

This following commands rename a table when restoring it from a TTS Backup by using the --rename option:

Example 5.8 Restoring and Renaming a Table from a TTS Backup

# Using fully qualified table names:
mysqlbackup --socket=/tmp/restoreserver.sock --datadir=/logs/restoreserverdata \
   --backup-dir=/BackupDirTemp --backup-image=/home/dbadmin/backups/tts-backup.mbi \
  --include-tables="^sales\.cars" --rename="sales.cars to sales.autos" copy-back-and-apply-log

# It works the same if database names are omitted in the argument for --rename:
mysqlbackup --socket=/tmp/restoreserver.sock --datadir=/logs/restoreserverdata \
  --backup-dir=/BackupDirTemp --backup-image=/home/dbadmin/backups/tts-backup.mbi \
  --include-tables="^sales\.cars" --rename="cars to autos" copy-back-and-apply-log