MySQL Enterprise Backup User's Guide (Version 4.0.3)
To restore a MySQL instance from a backup to a database server:
Shut down the database server.
Delete all files inside the server's data directory. Also
delete all files inside the directories specified by the
--innodb_data_home_dir
,
--innodb_log_group_home_dir
, and
--innodb_undo_directory
options for
restore, if the directories are different from the data
directory.
Use, for example, the
copy-back-and-apply-log
command,
which converts the raw backup into a prepared backup by
updating it to a consistent state, and then copies the
tables, indexes, metadata, and any other required files onto
a target server. For the various options that you can
specify for this operation, see
Section 14.3, “Restore Operations”.
In the example below, the single-file backup created in the
example given in Section 4.2.1, “Backing Up an Entire MySQL Instance” is
restored using the
copy-back-and-apply-log
command.
Besides the usual connection parameters, the following options
are used:
--defaults-file
supplies
the configuration for restoring the data. It must be the
first option to appear in a mysqlbackup
command, if ever used. In most cases, you can supply to
mysqlbackup with this option the
configuration file for the target server to which you are
restoring the data. However, when the following InnoDB
settings for the backup are different from those on the
target server, it is important to supply the values for
the backup to mysqlbackup during
restore and to mysqld when you start
the restored server (otherwise, the restore might fail,
and you might have problem starting the restored server):
If you are not sure about those settings for your backup,
they are stored in the backup-my.cnf
file during the backup—you can find the file either
in the temporary directory you specified with
--backup-dir
when you created the single-image backup, or in a backup
directory you can create by unpacking the backup image
using the
extract
command. If the values of these options differ from those
on the target server, add them to the configuration file
you are supplying to mysqlbackup and
also to the configuration file you are going to use to
start the server afterwards; alternatively, you can also
supply them as command line options to
mysqlbackup and
mysqld.
For some of the options listed above (namely,
innodb_data_file_path
,
innodb_log_file_size
,
innodb_log_files_in_group
,
and
innodb_undo_tablespaces
),
mysqlbackup checks the values you
supply for them to ensure that you will be able to start
the target server afterwards with those values: it throws
an error if any of them does not match with the actual
values for the backup. Warnings are given if those values
are not specified for mysqlbackup in
either the configuration file or on the command line
(which is the case in the example below).
--datadir
supplies the
location of the data directory for restoring the data. You
must specify this option for any restore operation.
--backup-image
provides the
path of the single-file backup.
--backup-dir
provides the
location of an empty folder to store some temporary files
created during the restore procedure.
$ ./mysqlbackup --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql \ --backup-image=/home/admin/backups/my.mbi --backup-dir=/home/admin/restore-tmp copy-back-and-apply-log MySQL Enterprise Backup version 4.0.0 Linux-3.2.0-25-generic-pae-i686 [2015/09/29] Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... ./bin/mysqlbackup --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql --backup-image=/home/admin/backups/my.mbi --backup-dir=/home/admin/restore-tmp copy-back-and-apply-log mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful 'copy-back-and-apply-log' run mysqlbackup prints "mysqlbackup completed OK!". mysqlbackup: INFO: Backup Image MEB version string: 4.0.0 [2015/09/29] 151008 12:35:23 mysqlbackup: INFO: MEB logfile created at /home/admin/restore-tmp/meta/MEB_2015-10-08.12-35-23_copy_back_img_to_datadir.log -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /var/lib/mysql innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /var/lib/mysql innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = crc32 -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /home/admin/restore-tmp/datadir innodb_data_home_dir = /home/admin/restore-tmp/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /home/admin/restore-tmp/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = crc32 mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 151008 12:35:23 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads 1 read-threads 6 process-threads 1 write-threads 151008 12:35:23 mysqlbackup: INFO: Copying database directory: meta 151008 12:35:23 mysqlbackup: INFO: Copying datadir/ibdata1. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mydb/mytable.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying database directory: datadir/mydb 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/engine_cost.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying database directory: datadir/mysql 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/gtid_executed.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/help_category.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/help_keyword.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/help_relation.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/help_topic.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/innodb_index_stats.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/innodb_table_stats.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/plugin.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/server_cost.ibd. 151008 12:35:23 mysqlbackup: INFO: Copying datadir/mysql/servers.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/mysql/slave_master_info.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/mysql/slave_relay_log_info.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/mysql/slave_worker_info.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/mysql/time_zone.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/mysql/time_zone_leap_second.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/mysql/time_zone_name.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/mysql/time_zone_transition.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/mysql/time_zone_transition_type.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying datadir/sys/sys_config.ibd. 151008 12:35:24 mysqlbackup: INFO: Copying database directory: datadir/performance_schema 151008 12:35:24 mysqlbackup: INFO: Copying database directory: datadir/sys 151008 12:35:24 mysqlbackup: INFO: Copying database directory: datadir/mydb 151008 12:35:24 mysqlbackup: INFO: Copying database directory: datadir/mysql 151008 12:35:24 mysqlbackup: INFO: Copying database directory: datadir/performance_schema 151008 12:35:25 mysqlbackup: INFO: Copying database directory: datadir/sys 151008 12:35:26 mysqlbackup: INFO: Total files as specified in image: 297 151008 12:35:26 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql 151008 12:35:26 mysqlbackup: INFO: Copy-back operation completed successfully. mysqlbackup: INFO: Source Image Path = /home/admin/backups/my.mbi mysqlbackup: INFO: Restoring ...5.7.9 version mysqlbackup: INFO: Creating 14 buffers each of size 65536. 151008 12:35:26 mysqlbackup: INFO: Apply-log operation starts with following threads 1 read-threads 1 process-threads mysqlbackup: INFO: Using up to 100 MB of memory. 151008 12:35:26 mysqlbackup: INFO: ibbackup_logfile's creation parameters: start lsn 2478592, end lsn 2478749, start checkpoint 2478740. mysqlbackup: INFO: InnoDB: Doing recovery: scanned up to log sequence number 2478749. mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database... . InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648. mysqlbackup: INFO: InnoDB: Setting log file size to 50331648. 151008 12:35:29 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to lsn 2478749. 151008 12:35:29 mysqlbackup: INFO: The first data file is '/var/lib/mysql/ibdata1' and the new created log files are at '/var/lib/mysql' mysqlbackup: INFO: Restoring ...5.7.9 version 151008 12:35:29 mysqlbackup: INFO: Apply-log operation completed successfully. 151008 12:35:29 mysqlbackup: INFO: Full Backup has been restored successfully. mysqlbackup completed OK!
Now the original database directory is restored from the backup.
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 mysql
user, and the group
attribute to the mysql
group.
$ chown -R mysql:mysql /path/to/datadir
You are now ready to start the restored database server. For more discussions on how to perform different kinds of restores, see Section 5.1, “Performing a Restore Operation”.