Simon Krenger

In another post I explained how to create a full backup using MySQL Enterprise Backup. Of course, the most important part of the backup is not “making the backup” but the restore of said backup! A backup is worthless if you cannot restore it.

In this article, I will restore my backup on a completely seperate server. Therefore I am following these steps:

1. Install the MySQL binaries on your server, following the standard installation for MySQL Enterprise Edition. Also install the mysqlbackup binaries on the same server.
2. Copy your backup files (if you followed my other post, this is a folder like “2016-10-07_04-00-07“) to the server, where you want to restore the backup.

Then, to start the restore, use the copy-back-and-apply-log command of mysqlbackup to restore your database:

$ mysqlbackup \
--defaults-file=/var/backups/simon/mysql/2016-10-07_04-00-07/server-my.cnf \
--backup-dir=/var/backups/simon/mysql/2016-10-07_04-00-07 \
copy-back-and-apply-log 

Note the following options for mysqlbackup:

• --defaults-file: Path to the MySQL configuration file (typically server-my.cnf) in your backup files
• --backup-dir: The full path to the directory where the backup files are located
• copy-back-and-apply-log: This command tells mysqlbackup to copy back all data files and apply the transaction logs

Here is an example output for the restore process:

MySQL Enterprise Backup version X.X.X [XXXX/XX/XX] 
Copyright (c) 2003, 2016, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 mysqlbackup 
        --defaults-file=/var/backups/simon/mysql/2016-10-07_04-00-07/server-my.cnf 
        --backup-dir=/var/backups/simon/mysql/2016-10-07_04-00-07 
        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!".

161007 21:45:27 mysqlbackup: INFO: MEB logfile created at /var/backups/simon/mysql/2016-10-07_04-00-07/meta/MEB_2016-10-07.21-45-27_copy_back_dir_to_datadir.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /usr/local/mysql/data/
  innodb_data_home_dir = /usr/local/mysql/data/
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /usr/local/mysql/data/
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /usr/local/mysql/data/
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /var/backups/simon/mysql/2016-10-07_04-00-07/datadir
  innodb_data_home_dir = /var/backups/simon/mysql/2016-10-07_04-00-07/datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /var/backups/simon/mysql/2016-10-07_04-00-07/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb

 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
161007 21:45:27 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads
		1 read-threads    1 write-threads
161007 21:45:27 mysqlbackup: INFO: Copying /var/backups/simon/mysql/2016-10-07_04-00-07/datadir/ibdata1.
161007 21:45:27 mysqlbackup: INFO: Copying /var/backups/simon/mysql/2016-10-07_04-00-07/datadir/mydb/tbs1.ibd.
161007 21:45:27 mysqlbackup: INFO: Copying /var/backups/simon/mysql/2016-10-07_04-00-07/datadir/mydb2/users.ibd.
[..]
161007 21:45:34 mysqlbackup: INFO: Completing the copy of all non-innodb files.
161007 21:45:35 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /usr/local/mysql/data/
161007 21:45:35 mysqlbackup: INFO: Copy-back operation completed successfully.


 mysqlbackup: INFO: Creating 14 buffers each of size 65536.
161007 21:45:35 mysqlbackup: INFO: Apply-log operation starts with following threads
		1 read-threads    1 process-threads
161007 21:45:35 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 2380955648, end lsn 2380965814,
          start checkpoint 2380955892.
InnoDB: Doing recovery: scanned up to log sequence number 2380965814
 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
161007 21:45:36 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 2380965814.
161007 21:45:36 mysqlbackup: INFO: The first data file is '/usr/local/mysql/data/ibdata1'
          and the new created log files are at '/usr/local/mysql/data/'
161007 21:45:37 mysqlbackup: INFO: Apply-log operation completed successfully.
161007 21:45:37 mysqlbackup: INFO: Full Backup has been restored successfully.

mysqlbackup completed OK!

Note that “mysqlbackup completed OK!” is a good indicator that the restore process was successful. After completion of the restore, you should be able to restart the MySQL server and then access your restored data.