4.2.3 Restoring a Database

To restore a MySQL instance from a backup:

• Shut down the database server.

• Use, for example, mysqlbackup with the copy-back-and-apply-log command, which makes 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 back to their original locations, as defined by the original MySQL configuration file. For the different combinations of options that you can specify as part of 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 in the command:

• --defaults-file supplies the configuration file backup-my.cnf, which was created in the temporary directory you specified with --backup-dir when you created the single-image backup (see Section 4.2.2, “Verifying a Backup” for more explanations) and is now at the location specified with the option. Without specifying the --defaults-file option, the restore can still be performed, as long as the related InnoDB parameters for the backed-up and the target server match; you are going to receive some warnings though from mysqlbackup.

• --datadir supplies the location of the data directory for restoring the data.

• --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=/home/admin/backup-tmp/backup-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=/home/admin/backup-tmp/backup-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, and you can restart the database server.