Documentation Home
MySQL Enterprise Backup 8.0 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
HTML Download (TGZ) - 186.9Kb
HTML Download (Zip) - 217.1Kb


4.2.3 Restoring a Database

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. If the data directory is not empty, you have to use the --force option to allow overwriting of the old data, or mysqlbackup will error out.

  • 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 16.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. The following options are used:

  • --defaults-file supplies the configuration for restoring the data. Required only if you want the data to be restored in a configuration different from that of the backed-up server; see Section 17.3, “Server Repository Options” for options you may use. It must be the first option to appear in a mysqlbackup command, if ever used.

  • --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 --datadir=/home/admin/bin/mysql-commercial-8.0.11/datadir \
  --backup-image=/home/mysqlbackup/backups/my.mbi --backup-dir=/home/mysqlbackup/backup-tmp \
    copy-back-and-apply-log
MySQL Enterprise Backup version 8.0.11 Linux-3.8.13-68.3.4.el6uek.x86_64-x86_64 [2018-03-15  14:13:10] 
Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

180404 15:49:28 MAIN    INFO: A thread created with Id '140512179550016' 
180404 15:49:28 MAIN    INFO: Starting with following command line ...
 bin/mysqlbackup --datadir=/home/admin/bin/mysql-commercial-8.0.11/datadir 
        --backup-image=/home/mysqlbackup/backups/my.mbi 
        --backup-dir=/home/mysqlbackup/backup-tmp copy-back-and-apply-log 

180404 15:49:28 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!".

180404 15:49:28 MAIN    INFO: Backup Image MEB version string: 8.0.11 [2018-03-15  14:13:10]
180404 15:49:28 MAIN    INFO: MySQL server version is '8.0.11'
180404 15:49:28 MAIN WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter migh
t have a different default. In that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target ser
ver configuration.
180404 15:49:28 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 configura
tion.
180404 15:49:28 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might
have a different default. In that case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
180404 15:49:28 MAIN    INFO: KEF source path:'/home/mysqlbackup/backup-tmp/meta'
180404 15:49:28 MAIN    INFO: KEF target path:'/home/admin/bin/mysql-commercial-8.0.11/datadir'
180404 15:49:28 MAIN    INFO: TDE Keyring service initialized.
180404 15:49:28 MAIN    INFO: MEB logfile created at /home/mysqlbackup/backup-tmp/meta/MEB_2018-04-04.15-49-28_copy_back_
img_to_datadir.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = /home/admin/bin/mysql-commercial-8.0.11/datadir
  innodb_data_home_dir           = /home/admin/bin/mysql-commercial-8.0.11/datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /home/admin/bin/mysql-commercial-8.0.11/datadir
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = /home/admin/bin/mysql-commercial-8.0.11/datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = Null
  innodb_checksum_algorithm      = crc32

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                        = /home/mysqlbackup/backup-tmp/datadir
  innodb_data_home_dir           = /home/mysqlbackup/backup-tmp/datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /home/mysqlbackup/backup-tmp/datadir
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = /home/mysqlbackup/backup-tmp/datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

180404 15:49:28 MAIN    INFO: Creating 14 buffers each of size 16777216.
180404 15:49:28 MAIN    INFO: Copy-back-and-apply-log operation starts with following threads
		1 read-threads    6 process-threads    1 write-threads
180404 15:49:28 RDR1    INFO: A thread created with Id '140511909193472' 
180404 15:49:28 PCR1    INFO: A thread created with Id '140511900800768' 
180404 15:49:28 PCR1    INFO: Copying database directory: meta
180404 15:49:28 PCR4    INFO: A thread created with Id '140511875622656' 
180404 15:49:28 PCR6    INFO: A thread created with Id '140511858837248' 
180404 15:49:28 PCR2    INFO: A thread created with Id '140511892408064' 
180404 15:49:28 PCR5    INFO: A thread created with Id '140511867229952' 
180404 15:49:28 PCR3    INFO: A thread created with Id '140511884015360' 
180404 15:49:28 WTR1    INFO: A thread created with Id '140511850444544' 
180404 15:49:28 RDR1    INFO: Copying ibdata1.
180404 15:49:28 RDR1    INFO: Copying undo_001.
180404 15:49:28 RDR1    INFO: Copying undo_002.
180404 15:49:28 RDR1    INFO: Copying sys/sys_config.ibd.
180404 15:49:28 RDR1    INFO: Copying pets/cats.ibd.
180404 15:49:28 RDR1    INFO: Copying mysql.ibd.
180404 15:49:28 RDR1    INFO: Path to expand /home/admin/bin/mysql-commercial-8.0.11/datadir
180404 15:49:28 RDR1    INFO: Path expanded to /home/admin/bin/mysql-commercial-8.0.11/datadir
180404 15:49:28 RDR1    INFO: Copying binlog.000001.
180404 15:49:28 RDR1    INFO: Path to expand /home/admin/bin/mysql-commercial-8.0.11/datadir
180404 15:49:28 RDR1    INFO: Path expanded to /home/admin/bin/mysql-commercial-8.0.11/datadir
180404 15:49:28 RDR1    INFO: Copying binlog.000002.
180404 15:49:28 PCR1    INFO: Copying database directory: mysql
180404 15:49:28 PCR1    INFO: Copying database directory: performance_schema
180404 15:49:28 PCR1    INFO: Copying database directory: pets
180404 15:49:28 PCR1    INFO: Copying database directory: sys
180404 15:49:28 MAIN    INFO: Total files as specified in image: 137
180404 15:49:28 MAIN    INFO: MySQL server version is '8.0.11-commercial'
180404 15:49:28 MAIN    INFO: Restoring ...8.0.11-commercial version
180404 15:49:28 MAIN    INFO: MySQL server version is '8.0.11-commercial'
180404 15:49:28 MAIN    INFO: MySQL server compile os version is 'linux-glibc2.12'
180404 15:49:28 MAIN    INFO: Writing config file for server '8.0.11-commercial'.
180404 15:49:28 MAIN    INFO: Creating server config files server-my.cnf and server-all.cnf in /home/admin/bin/mysql-comm
ercial-8.0.11/datadir
180404 15:49:28 MAIN    INFO: Variable 'datadir'='/home/admin/bin/mysql-commercial-8.0.11/datadir/'->'/home/admin/bin/mys
ql-commercial-8.0.11/datadir' option 'datadir' type 0
180404 15:49:28 MAIN    INFO: Variable 'datadir'='/home/admin/bin/mysql-commercial-8.0.11/datadir/'->'/home/admin/bin/mys
ql-commercial-8.0.11/datadir' option 'datadir' type 0
180404 15:49:28 MAIN    INFO: Variable 'innodb_buffer_pool_filename'='ib_buffer_pool'->'ib_buffer_pool' option 'innodb_bu
ffer_pool_filename' type 8
180404 15:49:28 MAIN    INFO: Variable 'innodb_buffer_pool_filename'='ib_buffer_pool'->'ib_buffer_pool' option 'innodb_bu
ffer_pool_filename' type 8
180404 15:49:28 MAIN    INFO: Variable 'innodb_checksum_algorithm'='crc32'->'crc32' option 'innodb_checksum_algorithm' ty
pe 10
180404 15:49:28 MAIN    INFO: Variable 'innodb_checksum_algorithm'='crc32'->'crc32' option 'innodb_checksum_algorithm' ty
pe 10
180404 15:49:28 MAIN    INFO: Variable 'innodb_data_file_path'='ibdata1:12M:autoextend'->'ibdata1:12M:autoextend' option 
'innodb_data_file_path' type 2
180404 15:49:28 MAIN    INFO: Variable 'innodb_data_file_path'='ibdata1:12M:autoextend'->'ibdata1:12M:autoextend' option 
'innodb_data_file_path' type 2
180404 15:49:28 MAIN    INFO: Variable 'innodb_data_home_dir'=''->'/home/admin/bin/mysql-commercial-8.0.11/datadir' optio
n 'innodb_data_home_dir' type 1
180404 15:49:28 MAIN    INFO: Variable 'innodb_log_file_size'='50331648'->'50331648' option 'innodb_log_file_size' type 5
180404 15:49:28 MAIN    INFO: Variable 'innodb_log_file_size'='50331648'->'50331648' option 'innodb_log_file_size' type 5
180404 15:49:28 MAIN    INFO: Variable 'innodb_log_files_in_group'='2'->'2' option 'innodb_log_files_in_group' type 4
180404 15:49:28 MAIN    INFO: Variable 'innodb_log_files_in_group'='2'->'2' option 'innodb_log_files_in_group' type 4
180404 15:49:28 MAIN    INFO: Variable 'innodb_log_group_home_dir'='./'->'/home/admin/bin/mysql-commercial-8.0.11/datadir
' option 'innodb_log_group_home_dir' type 3
180404 15:49:28 MAIN    INFO: Variable 'innodb_log_group_home_dir'='./'->'/home/admin/bin/mysql-commercial-8.0.11/datadir
' option 'innodb_log_group_home_dir' type 3
180404 15:49:28 MAIN    INFO: Variable 'innodb_page_size'='16384'->'16384' option 'innodb_page_size' type 9
180404 15:49:28 MAIN    INFO: Variable 'innodb_page_size'='16384'->'16384' option 'innodb_page_size' type 9
180404 15:49:28 MAIN    INFO: Variable 'innodb_undo_directory'='./'->'/home/admin/bin/mysql-commercial-8.0.11/datadir' op
tion 'innodb_undo_directory' type 6
180404 15:49:28 MAIN    INFO: Variable 'innodb_undo_directory'='./'->'/home/admin/bin/mysql-commercial-8.0.11/datadir' op
tion 'innodb_undo_directory' type 6
180404 15:49:28 MAIN    INFO: Variable 'innodb_undo_tablespaces'='2'->'2' option 'innodb_undo_tablespaces' type 7
180404 15:49:28 MAIN    INFO: Variable 'innodb_undo_tablespaces'='2'->'2' option 'innodb_undo_tablespaces' type 7
180404 15:49:28 MAIN    INFO: Copy-back operation completed successfully.
180404 15:49:28 MAIN    INFO: Source Image Path = /home/mysqlbackup/backups/my.mbi


180404 15:49:28 MAIN    INFO: MySQL server version is '8.0.11-commercial'
180404 15:49:28 MAIN    INFO: Restoring ...8.0.11-commercial version
180404 15:49:28 MAIN    INFO: Creating 14 buffers each of size 65536.
180404 15:49:28 MAIN    INFO: Apply-log operation starts with following threads
		1 read-threads    1 process-threads    6 apply-threads
180404 15:49:28 MAIN    INFO: Using up to 100 MB of memory.
180404 15:49:28 MAIN    INFO: ibbackup_logfile's creation parameters:
          start lsn 19618816, end lsn 19618974,
          start checkpoint 19618974.
180404 15:49:28 MAIN    INFO: InnoDB: Loading the space id : 0, space name : /home/admin/bin/mysql-commercial-8.0.11/data
dir/ibdata1.
180404 15:49:28 MAIN    INFO: InnoDB: Loading the undo space id : 4294967279, space name : /home/admin/bin/mysql-commerci
al-8.0.11/datadir/undo_001.
180404 15:49:28 MAIN    INFO: InnoDB: Loading the undo space id : 4294967278, space name : /home/admin/bin/mysql-commerci
al-8.0.11/datadir/undo_002.
180404 15:49:28 MAIN    INFO: InnoDB: Loading the space id : 2, space name : /home/admin/bin/mysql-commercial-8.0.11/data
dir/pets/cats.ibd.
180404 15:49:28 MAIN    INFO: InnoDB: Loading the space id : 1, space name : /home/admin/bin/mysql-commercial-8.0.11/data
dir/sys/sys_config.ibd.
180404 15:49:28 MAIN    INFO: InnoDB: Loading the space id : 4294967294, space name : /home/admin/bin/mysql-commercial-8.
0.11/datadir/mysql.ibd.
180404 15:49:28 ALW1    INFO: A thread created with Id '140511850444544' 
180404 15:49:28 PCR1    INFO: A thread created with Id '140512116815616' 
180404 15:49:28 RDR1    INFO: A thread created with Id '140512125208320' 
180404 15:49:28 ALW6    INFO: A thread created with Id '140512133601024' 
180404 15:49:28 ALW5    INFO: A thread created with Id '140512141993728' 
180404 15:49:28 ALW4    INFO: A thread created with Id '140511875622656' 
180404 15:49:28 ALW3    INFO: A thread created with Id '140511867229952' 
180404 15:49:28 ALW2    INFO: A thread created with Id '140511858837248' 
180404 15:49:28 PCR1    INFO: InnoDB: Starting to parse redo log at lsn = 19618835, whereas checkpoint_lsn = 19618974.
180404 15:49:28 PCR1    INFO: InnoDB: Doing recovery: scanned up to log sequence number 19618974.
180404 15:49:28 PCR1    INFO: InnoDB: Starting to apply a batch of log records to the database....
InnoDB: Progress in percent: 
180404 15:49:28 PCR1    INFO: Updating last checkpoint to 19618974 in redo log/
180404 15:49:28 PCR1    INFO: Setting log file size to 50331648
180404 15:49:29 PCR1    INFO: Setting log file size to 50331648
180404 15:49:29 PCR1    INFO: Log file header:
	 format = 3 
	 pad1 = 0 
	 start lsn = 19618816 
	 checkpoint lsn = 19618974 
	 checksum = 3937445895
	 creator = MEB 8.0.11
180404 15:49:29 PCR1    INFO: We were able to parse ibbackup_logfile up to
          lsn 19618974.
180404 15:49:29 PCR1    INFO: Last MySQL binlog file position 0 3980, file name binlog.000002
180404 15:49:29 PCR1    INFO: The first data file is '/home/admin/bin/mysql-commercial-8.0.11/datadir/ibdata1'
                              and the new created log files are at '/home/admin/bin/mysql-commercial-8.0.11/datadir'
180404 15:49:29 MAIN    INFO: No Keyring file to process.
180404 15:49:29 MAIN    INFO: Apply-log operation completed successfully.
180404 15:49:29 MAIN    INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 3 warnings

Now the original database directory is restored from the backup.

Starting the Restored Server.  When the following InnoDB settings are different on the backed-up and the restored server, it is important to configure the restored server with the settings from the backed up server (otherwise, your restored server might not start):

If you are not sure about those settings for your backed-up server, they were actually 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 restored the single-image backup, or in a backup directory you could 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 using to start the target server afterwards; alternatively, you can also supply them as command line options to mysqld.

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”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.