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 full restore (for example, when the backup data is used to set up a new MySQL server or used to replace all data of an existing MySQL server), 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 both the --datadir and --innodb_data_file_path 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), and usually not necessary for restoring a partial backup.

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=<backupDir>/backup-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=<backupDir>/backup-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

Restoring an incremental backup image named <inc_image_name> to <restoreDir> on the server with copy-back-and-apply-log, using the --incremental-backup-dir option to specify the temporary directory into which the image will be extracted (note that the full backup on which the incremental backup was based on has already been restored to <restoreDir>):

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

Do the same using --backup-dir to specify the temporary directory into which the image will be extracted:

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

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


Example 4.8 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=/bkups/backupdir/backupmy.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: