4.2 Performing a Restore Operation

The mysqlbackup options to perform a restore operation are copy-back and copy-back-and-apply-log. The restoration process requires the database server to be already shut down (except for restorations of backups created with the --use-tts option; see explanations below). The process copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required post-processing on them. For any restore operation, the options datadir, innodb_log_files_in_group, innodb_log_file_size, and innodb_data_file_path must be specified either in the target server's configuration file, in the file specified by the --defaults-file option, or as command-line options.

Example 4.4 Shutting Down and Restoring a Database

mysqladmin --user=root --password shutdown
mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \
  --backup-dir=/export/backups/full \
    copy-back

Note

The restored data includes the backup_history table, where MySQL Enterprise Backup records details of each backup. Restoring this table to its earlier state removes information about any subsequent backups that you did. This is the correct starting point for future incremental backups, particularly those using the --incremental-base option.

Important

When performing a restore, make sure the target data directories are all clean, containing no old or unwanted data files (this might require manual removal of files at the locations specified by the --datadir, --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options). The same cleanup is not required for restoring backups created with the--use-tts option (in which case other requirements described in Restoring Backups Created with the --use-tts Option apply though).

You can combine the apply-log and the copy-back operations (as well as a number of other operations, depending on the kind of backup you are restoring) into a single step by using the copy-back-and-apply-log option instead:

mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \
  --backup-dir=/export/backups/full \
    copy-back-and-apply-log

Example 4.5 Restoring a Compressed Backup

Restore a compressed backup at <backupDir> to <restoreDir> on the server using copy-back-and-apply-log:

mysqlbackup --defaults-file=<my.cnf> -uroot --backup-dir=<backupDir> --datadir=<restoreDir> \
  --uncompress copy-back-and-apply-log

Do the same for a compressed backup image named <image_name>, using the --backup-dir option to specify the temporary directory into which the image will be extracted:

mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<image_name> \
  --backup-dir=<backupTmpDir> --datadir=<restoreDir> --uncompress copy-back-and-apply-log

See Section 3.3.3, “Making a Compressed Backup” and Section 5.1.7, “Compression Options” for more details on compressed backups.

Example 4.6 Restoring an Encrypted Backup Image

Restore an encrypted backup image named <image_name> to <restoreDir> on the server with copy-back-and-apply-log, using the encryption key contained in a file named <keyFile> :

mysqlbackup --defaults-file=<my.cnf> --backup-image=<image_name> \
 --backup-dir=<backupTmpDir> --datadir=<restoreDir> --decrypt --key-file=<keyFile> copy-back-and-apply-log

See Section 5.1.14, “Encryption Options” for more details on backup encryption and decryption.

Example 4.7 Restoring an Incremental Backup Image

mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<inc_image_name> \
  --incremental-backup-dir=<incBackupTmpDir> --datadir=<restoreDir> --incremental \
  copy-back-and-apply-log

In this example, the incremental backup image named <inc_image_name> was restored to <restoreDir> on the server (where the full backup that the incremental backup image was based on has already been restored). The --incremental-backup-dir option is used to specify the temporary directory into which the image will be extracted (you can use --backup-dir for the same purpose). Repeat the step with other incremental backup images that you have, until the data has been restored to a desired point in time.

Alternatively you can bring your full backup up-to-date with your incremental backup. First, apply to the full backup any changes that occurred while the backup was running:

$ mysqlbackup --backup-dir=/full-backup/2010-12-08_17-14-11 apply-log
..many lines of output...
101208 17:15:10  mysqlbackup: Full backup prepared for recovery successfully!

101208 17:15:10 mysqlbackup: mysqlbackup completed OK!

Then, we apply the changes from the incremental backup:

$ mysqlbackup --incremental-backup-dir=/incr-backup/2010-12-08_17-14-48 
  --backup-dir=/full-backup/2010-12-08_17-14-11 apply-incremental-backup
...many lines of output...
101208 17:15:12 mysqlbackup: mysqlbackup completed OK!

Now, the data files in the full backup directory are fully up-to-date, as of the time of the last incremental backup. You can keep updating it with more incremental backups, so it is ready to be restored anytime.

See Section 3.3.2, “Making an Incremental Backup”, and Section 5.1.8, “Incremental Backup Options”, for more details on incremental backups.

Example 4.8 Restoring Selected Tables from a TTS Backup

Selected tables can be restored from a backup created with transportable tablespaces (TTS) (that is, a backup created with the --use-tts option) 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” :

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

See Restoring Backups Created with the --use-tts Option for the special requirements that apply when you restore selected tables from a TTS backup.

Example 4.9 Restoring a Single-file Backup from Cloud Storage to a MySQL Server

Restore a backup image from cloud storage to datadir on the server, using the --backup-dir option to specify the temporary directory into which the image will be extracted (see Section 5.1.15, “Cloud Storage Options” for information on the cloud service options):

mysqlbackup\
--defaults-file=<my.cnf> \
--cloud-service=s3 --cloud-aws-region=<aws region> \
--cloud-access-key-id=<aws access key id> --cloud-secret-access-key=< aws secret access key> \
--cloud-bucket=<s3 bucket name> --cloud-object-key=<aws object key> \
--backup-dir=/home/user/dba/s3backuptmpdir \
--datadir=/home/user/dba/datadir \
--backup-image=- \
copy-back-and-apply-log

Restoring Backups Created with the --use-tts Option

There are some special requirements for restoring backups 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.