There are some special requirements for restoring backups
tablespaces (TTS) (that is, created with the
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 (
.ibdfile) to be restored does not match the value of the
innodb_file_formatsystem variable on the destination server. In that case, use the
--forceoption with the restore commands to change temporarily the value of
innodb_file_formaton 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
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
apply-log operation to
make the data up-to-date before restoring them to the server's
Selected tables can be restored from a backup created with
tablespaces (TTS) using the
--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
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