Documentation Home
MySQL Enterprise Backup 3.12 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.1Mb
PDF (A4) - 1.1Mb
HTML Download (TGZ) - 173.3Kb
HTML Download (Zip) - 198.8Kb


5.2.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 default 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.10 Restoring Selected Tables from a TTS 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.11 Restoring and Renaming a Table from a TTS Backup

# Using fully qualified table names:
mysqlbackup --socket=/tmp/restoreserver.sock --datadir=/logs/restoreserverdata --backup-dir=/logs/backup \
  --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=/logs/backup \
  --include-tables="^sales\.cars" --rename="cars to autos" copy-back-and-apply-log



User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.