- 5.1.1 Restoring a Compressed Backup
- 5.1.2 Restoring an Encrypted Backup Image
- 5.1.3 Restoring an Incremental Backup
- 5.1.4 Restoring Backups Created with the
- 5.1.5 Restoring a Backup from Cloud Storage to a MySQL Server
- 5.1.6 Restoring External InnoDB Tablespaces to Different Locations
- 5.1.7 Advanced: Preparing and Restoring a Directory Backup
The mysqlbackup commands to perform a restore
copy-back (for directory backup
only; see Section 5.1.7, “Advanced: Preparing and Restoring a Directory Backup”). Normally, the
restoration process requires the database server to be already
shut down (or, at least not operating on the directory you are
restoring the data to), except for restorations of backups created
--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.
Example 5.1 Restoring a Database
mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<image_name> \ --backup-dir=<backupTmpDir> --datadir=<restoreDir> copy-back-and-apply-log
achieves two things:
Extracts the backup from the image file and copies it to the data directory on the server to be restored.
Performs an apply log operation to the restored data to bring them up-to-date.
See Section 4.2.4, “Restoring a Database” for an explanation of
the important options used in a restore operation like
The restored data includes the
table, where MySQL Enterprise Backup records details of each backup. The table
allows you to perform future incremental backups using the
When performing a restore, make sure the target directories for restore data are all clean, containing no old or unwanted data files (this might require manual removal of files at the locations specified by the
--innodb_undo_directoryoptions). The same cleanup is not required for restoring backups created with the
--use-ttsoption (in which case other requirements described in Section 5.1.4, “Restoring Backups Created with the
--use-ttsOption” apply though).
After a full restore, depending on how you are going to start the restored server, you might need to adjust the ownership of the restored data directory. For example, if the server is going to be started by the user
mysql, use the following command to change the owner attribute of the data directory and the files under it to the
mysqluser, and the group attribute to the
$ chown -R mysql:mysql /path/to/datadir
Because mysqlbackup does not support MySQL server's online DDL feature feature, a backup might become corrupted if, during the backup process, a DDL operation that took advantage of the feature occurred. To avoid the issue, mysqlbackup turns the server's system variable
old_alter_tableto “1” at the beginning of a backup if it is “0,” so that any DDL operations that take place during the backup are handled with the old table copy method. mysqlbackup then turns the variable back to “0” near the end of the backup operation.
Notice that in cases where mysqlbackup quits unexpectedly and does not turn
old_alter_tableback to its original value, the user will have to turn the value back to “0” manually on the server, in order to return the server to its original configuration. This should be performed if the statement “Server system variable 'old_alter_table' was set to '0'. Setting it to '1'” appears in the early output of mysqlbackup, but the statement “Setting server system variable 'old_alter_table' back to '0'” does not appear before mysqlbackup quits.
The following subsections describe a number of different scenarios for restoring a backup.