MySQL Enterprise Backup 4.1 Release Notes
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_directoryoptions for restore, if the directories are different from the data directory.Use, for example, the
copy-back-and-apply-logcommand, 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 15.3, “Restore Operations”.
In the example below, the single-file backup created in the
example given in Section 4.2.2, “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-filesupplies 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.cnffile during the backup—you can find the file either in the temporary directory you specified with--backup-dirwhen you created the single-image backup, or in a backup directory you can create by unpacking the backup image using theextractcommand. 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, andinnodb_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).--datadirsupplies the location of the data directory for restoring the data. You must specify this option for any restore operation.--backup-imageprovides the path of the single-file backup.--backup-dirprovides the location of an empty folder to store some temporary files created during the restore procedure.
Press CTRL+C to copy$ ./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.1.0 Linux-2.6.39-400.215.10.el5uek-x86_64 [2017/02/13] Copyright (c) 2003, 2017, Oracle and/or its affiliates. All Rights Reserved. 170214 22:11:38 MAIN INFO: A thread created with Id '140027243398976' 170214 22:11:38 MAIN INFO: Starting with following command line ... ./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 170214 22:11:38 MAIN 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!". 170214 22:11:38 MAIN INFO: Backup Image MEB version string: 4.1.0 [2017/02/13] 170214 22:11:38 MAIN INFO: MySQL server version is '5.7.17' 170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target server configuration. 170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration. 170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In th at case you need to add 'innodb_log_file_size=50331648' to the target server configuration. 170214 22:11:38 MAIN INFO: Keyring service not initialized. MEB cannot validate the encryption keys. 170214 22:11:38 MAIN INFO: Valid keyring to be specified on server start to access the encrypted tables. 170214 22:11:38 MAIN INFO: MEB logfile created at /home/admin/restore-tmp/meta/MEB_2017-02-14.22-11-38_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_buffer_pool_filename = ib_buffer_pool innodb_page_size = Null 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_buffer_pool_filename = ib_buffer_pool innodb_page_size = 16384 innodb_checksum_algorithm = crc32 170214 22:11:38 MAIN INFO: Creating 14 buffers each of size 16777216. 170214 22:11:38 MAIN INFO: Copy-back-and-apply-log operation starts with following threads 1 read-threads 6 process-threads 1 write-threads 170214 22:11:38 RDR1 INFO: A thread created with Id '140026981721856' 170214 22:11:38 PCR1 INFO: A thread created with Id '140026973329152' 170214 22:11:38 PCR1 INFO: Copying database directory: meta 170214 22:11:38 PCR2 INFO: A thread created with Id '140026964936448' 170214 22:11:38 PCR3 INFO: A thread created with Id '140026956543744' 170214 22:11:38 PCR4 INFO: A thread created with Id '140026948151040' 170214 22:11:38 PCR5 INFO: A thread created with Id '140026939758336' 170214 22:11:38 PCR6 INFO: A thread created with Id '140026931365632' 170214 22:11:38 WTR1 INFO: A thread created with Id '140026922972928' 170214 22:11:38 RDR1 INFO: Copying ibdata1. 170214 22:11:38 RDR1 INFO: Copying mysql/engine_cost.ibd. 170214 22:11:38 RDR1 INFO: Copying mysql/gtid_executed.ibd. 170214 22:11:38 RDR1 INFO: Copying mysql/help_category.ibd. 170214 22:11:38 RDR1 INFO: Copying mysql/help_keyword.ibd. 170214 22:11:38 RDR1 INFO: Copying mysql/help_relation.ibd. 170214 22:11:38 RDR1 INFO: Copying mysql/help_topic.ibd. 170214 22:11:38 PCR2 INFO: Copying database directory: mysql 170214 22:11:39 RDR1 INFO: Copying mysql/innodb_index_stats.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/innodb_table_stats.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/plugin.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/server_cost.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/servers.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/slave_master_info.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/slave_relay_log_info.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/slave_worker_info.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/time_zone.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/time_zone_leap_second.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/time_zone_name.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/time_zone_transition.ibd. 170214 22:11:39 RDR1 INFO: Copying mysql/time_zone_transition_type.ibd. 170214 22:11:40 PCR5 INFO: Copying database directory: performance_schema 170214 22:11:40 PCR5 INFO: Copying database directory: pets 170214 22:11:40 RDR1 INFO: Copying pets/cats.ibd. 170214 22:11:40 PCR5 INFO: Copying database directory: sys 170214 22:11:40 RDR1 INFO: Copying sys/sys_config.ibd. 170214 22:11:40 PCR5 INFO: Copying database directory: mysql 170214 22:11:40 PCR5 INFO: Copying database directory: performance_schema 170214 22:11:40 PCR5 INFO: Copying database directory: pets 170214 22:11:40 PCR5 INFO: Copying database directory: sys 170214 22:11:40 MAIN INFO: Total files as specified in image: 297 170214 22:11:40 MAIN INFO: MySQL server version is '5.7.17' 170214 22:11:40 MAIN INFO: MySQL server compile os version is 'Linux' 170214 22:11:40 MAIN INFO: Writing config file for server '5.7.17'. 170214 22:11:40 MAIN INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql [Some lines of output not shown] 170214 22:11:40 MAIN INFO: Copy-back operation completed successfully. 170214 22:11:40 MAIN INFO: Source Image Path = /home/admin/backups/my.mbi 170214 22:11:40 MAIN INFO: MySQL server version is '5.7.17' 170214 22:11:40 MAIN INFO: Restoring ...5.7.17 version 170214 22:11:40 MAIN INFO: Creating 14 buffers each of size 65536. 170214 22:11:40 MAIN INFO: Apply-log operation starts with following threads 1 read-threads 1 process-threads 6 apply-threads 170214 22:11:40 MAIN INFO: Using up to 100 MB of memory. 170214 22:11:40 MAIN INFO: ibbackup_logfile's creation parameters: start lsn 2543104, end lsn 2543132, start checkpoint 2543123. 170214 22:11:40 ALW1 INFO: A thread created with Id '140026922972928' 170214 22:11:40 ALW6 INFO: A thread created with Id '140027204466432' 170214 22:11:40 ALW5 INFO: A thread created with Id '140027212859136' 170214 22:11:40 ALW4 INFO: A thread created with Id '140026948151040' 170214 22:11:40 ALW3 INFO: A thread created with Id '140026939758336' 170214 22:11:40 ALW2 INFO: A thread created with Id '140026931365632' 170214 22:11:40 RDR1 INFO: A thread created with Id '140027196073728' 170214 22:11:40 PCR1 INFO: A thread created with Id '140027187681024' 170214 22:11:40 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2543132. 170214 22:11:40 PCR1 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 170214 22:11:40 PCR1 INFO: InnoDB: Setting log file size to 50331648. 170214 22:11:45 PCR1 INFO: InnoDB: Setting log file size to 50331648. 170214 22:11:50 PCR1 INFO: We were able to parse ibbackup_logfile up to lsn 2543132. 170214 22:11:50 PCR1 INFO: The first data file is '/var/lib/mysql/ibdata1' and the new created log files are at '/var/lib/mysql' 170214 22:11:50 MAIN INFO: MySQL server version is '5.7.17' 170214 22:11:50 MAIN INFO: Restoring ...5.7.17 version 170214 22:11:50 MAIN INFO: Apply-log operation completed successfully. 170214 22:11:50 MAIN INFO: Full Backup has been restored successfully. mysqlbackup completed OK! with 3 warnings
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.
Press CTRL+C to copy$ 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”.