The mysqlbackup options to perform a restore
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
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
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
The restored data includes the
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
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
--innodb_undo_directory options). The same
cleanup is not required for restoring backups created with
--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
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
<restoreDir> on the server using
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
Example 4.6 Restoring an Encrypted Backup Image
Restore an encrypted backup image named
<restoreDir> on the server with
copy-back-and-apply-log, using the
encryption key contained in a file named
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
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.
Example 4.8 Restoring Selected Tables from a TTS Backup
Selected tables can be restored from a backup created with
tablespaces (TTS) (that is, a backup created with the
--use-tts option) 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” :
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
There are some special requirements for restoring backups created
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 (
.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.