MySQL Enterprise Backup User's Guide (Version 8.0.23)
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 19.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. The following options are used:
--datadir
supplies the
location of the data directory for restoring the data. You
must specify this option for any restore operation, either
at the command line or in a defaults file.
--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.16/datadir \
--backup-image=/home/mysqlbackup/backups/my.mbi --backup-dir=/home/mysqlbackup/backup-tmp \
copy-back-and-apply-log
MySQL Enterprise Backup Ver 8.0.16-commercial for linux-glibc2.12 on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
mysqlbackup --datadir=/home/admin/bin/mysql-commercial-8.0.16/datadir
--backup-image=/home/mysqlbackup/backups/my.mbi
--backup-dir=/home/mysqlbackup/backup-tmp copy-back-and-apply-log
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!".
190402 16:56:37 MAIN INFO: Starting to log actions.
190402 16:56:37 MAIN INFO: Backup directory exists: '/home/mysqlbackup/backup-tmp'
190402 16:56:37 MAIN INFO: Backup Image MEB version string: 8.0.16 [2019-03-26 18:51:33]
190402 16:56:37 MAIN INFO: MySQL server version is '8.0.16'
190402 16:56:37 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.
190402 16:56:37 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.
190402 16:56:37 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.
190402 16:56:37 MAIN INFO: Server is not a community server.
190402 16:56:37 MAIN INFO: KEF source path:'/home/mysqlbackup/backup-tmp/meta/keyring_kef'
190402 16:56:37 MAIN INFO: KEF target path:'/home/admin/bin/mysql-commercial-8.0.16/datadir/keyring_kef'
190402 16:56:37 MAIN INFO: TDE Keyring service initialized.
190402 16:56:37 MAIN INFO: MEB logfile created at /home/mysqlbackup/backup-tmp/meta/MEB_2019-04-02.16-56-37_copy_back_img_to_datadir.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /home/admin/bin/mysql-commercial-8.0.16/datadir
innodb_data_home_dir = /home/admin/bin/mysql-commercial-8.0.16/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /home/admin/bin/mysql-commercial-8.0.16/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /home/admin/bin/mysql-commercial-8.0.16/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
190402 16:56:37 MAIN INFO: Creating 14 buffers each of size 16777216.
190402 16:56:37 MAIN INFO: Copy-back-and-apply-log operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
190402 16:56:37 PCR1 INFO: Copying database directory: meta
190402 16:56:37 RDR1 INFO: Copying ibdata1.
190402 16:56:37 RDR1 INFO: Copying undo_002.
190402 16:56:37 RDR1 INFO: Copying undo_001.
190402 16:56:37 RDR1 INFO: Copying sys/sys_config.ibd.
190402 16:56:37 RDR1 INFO: Copying pets/cats.ibd.
190402 16:56:37 RDR1 INFO: Copying mysql/backup_history.ibd.
190402 16:56:37 RDR1 INFO: Copying mysql.ibd.
190402 16:56:37 RDR1 INFO: Copying binlog.000001.
190402 16:56:37 RDR1 INFO: Copying binlog.000002.
190402 16:56:37 RDR1 INFO: Copying binlog.000003.
190402 16:56:37 PCR3 INFO: Copying database directory: mysql
190402 16:56:37 PCR3 INFO: Copying database directory: performance_schema
190402 16:56:37 PCR3 INFO: Copying database directory: pets
190402 16:56:37 PCR3 INFO: Copying database directory: sys
190402 16:56:37 RDR1 INFO: Copying binlog.000004.
190402 16:56:37 MAIN INFO: Total files as specified in image: 138
190402 16:56:37 MAIN INFO: MySQL server version is '8.0.16-commercial'
190402 16:56:37 MAIN INFO: Restoring ...8.0.16-commercial version
190402 16:56:37 MAIN INFO: Copy-back operation completed successfully.
190402 16:56:37 MAIN INFO: Source Image Path = /home/mysqlbackup/backups/my.mbi
190402 16:56:37 MAIN INFO: MySQL server version is '8.0.16-commercial'
190402 16:56:37 MAIN INFO: Restoring ...8.0.16-commercial version
190402 16:56:37 MAIN INFO: Creating 14 buffers each of size 65536.
190402 16:56:37 MAIN INFO: Apply-log operation starts with following threads
1 read-threads 1 process-threads 6 apply-threads
190402 16:56:37 MAIN INFO: Using up to 100 MB of memory.
190402 16:56:37 MAIN INFO: ibbackup_logfile's creation parameters:
start lsn 19841024, end lsn 19841405,
start checkpoint 19841405.
190402 16:56:37 MAIN INFO: Loading the space id : 0, space name : /home/admin/bin/mysql-commercial-8.0.16/datadir/ibdata1.
190402 16:56:37 MAIN INFO: Apply log: Loading the undo space id : 4294967278, space name : /home/admin/bin/mysql-commercial-8.0.16/datadir/undo_002.
190402 16:56:37 MAIN INFO: Apply log: Loading the undo space id : 4294967279, space name : /home/admin/bin/mysql-commercial-8.0.16/datadir/undo_001.
190402 16:56:37 MAIN INFO: Loading the space id : 3, space name : /home/admin/bin/mysql-commercial-8.0.16/datadir/mysql/backup_history.ibd.
190402 16:56:37 MAIN INFO: Loading the space id : 2, space name : /home/admin/bin/mysql-commercial-8.0.16/datadir/pets/cats.ibd.
190402 16:56:37 MAIN INFO: Loading the space id : 1, space name : /home/admin/bin/mysql-commercial-8.0.16/datadir/sys/sys_config.ibd.
190402 16:56:37 MAIN INFO: Loading the space id : 4294967294, space name : /home/admin/bin/mysql-commercial-8.0.16/datadir/mysql.ibd.
190402 16:56:37 PCR1 INFO: Starting to parse redo log at lsn = 19841394, whereas checkpoint_lsn = 19841405.
190402 16:56:37 PCR1 INFO: Doing recovery: scanned up to log sequence number 19841405.
190402 16:56:37 PCR1 INFO: Starting to apply a batch of log records to the database....
InnoDB: Progress in percent:
190402 16:56:37 PCR1 INFO: Updating last checkpoint to 19841405 in redo log/
190402 16:56:37 PCR1 INFO: Setting log file size to 50331648
190402 16:56:37 PCR1 INFO: Setting log file size to 50331648
190402 16:56:37 PCR1 INFO: Log file header:
format = 3
pad1 = 0
start lsn = 19841024
checkpoint lsn = 19841405
checksum = 2051460933
creator = MEB 8.0.16
190402 16:56:37 PCR1 INFO: We were able to parse ibbackup_logfile up to
lsn 19841405.
190402 16:56:37 PCR1 INFO: Last MySQL binlog file position 0 379, file name binlog.000004
190402 16:56:37 PCR1 INFO: The first data file is '/home/admin/bin/mysql-commercial-8.0.16/datadir/ibdata1'
and the new created log files are at '/home/admin/bin/mysql-commercial-8.0.16/datadir'
190402 16:56:37 MAIN INFO: No Keyring file to process.
190402 16:56:37 MAIN INFO: Apply-log operation completed successfully.
190402 16:56:37 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”.