MySQL Enterprise Backup User's Guide (Version 3.12.5)

4.2.4 Restoring a Database

To restore a MySQL instance from a backup to a database server:

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:

 
$  ./mysqlbackup  --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql \ 
    --backup-image=/home/admin/backups/my.mbi --backup-dir=/home/admin/backup-tmp copy-back-and-apply-log

MySQL Enterprise Backup version 3.12.5 Linux-2.6.18-274.el5-i686 [2014/11/12] 
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: 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/backup-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: 3.12.5 [2014/11/12]
141204 13:10:39 mysqlbackup: INFO: MEB logfile created at /home/admin/backup-tmp/meta/MEB_2014-12-04.13-10-39_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 = innodb

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /home/admin/backup-tmp/datadir
  innodb_data_home_dir = /home/admin/backup-tmp/datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /home/admin/backup-tmp/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
141204 13:10:39 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads
		1 read-threads    6 process-threads    1 write-threads
141204 13:10:39 mysqlbackup: INFO: Copying database directory: meta
141204 13:10:39 mysqlbackup: INFO: Copying datadir/ibdata1.
141204 13:10:39 mysqlbackup: INFO: Copying datadir/mysql/innodb_index_stats.ibd.
141204 13:10:39 mysqlbackup: INFO: Copying datadir/mysql/innodb_table_stats.ibd.
141204 13:10:39 mysqlbackup: INFO: Copying datadir/mysql/slave_master_info.ibd.
141204 13:10:39 mysqlbackup: INFO: Copying datadir/mysql/slave_relay_log_info.ibd.
141204 13:10:39 mysqlbackup: INFO: Copying database directory: datadir/mysql
141204 13:10:39 mysqlbackup: INFO: Copying datadir/mysql/slave_worker_info.ibd.
141204 13:10:39 mysqlbackup: INFO: Copying datadir/test2/tb1.ibd.
141204 13:10:39 mysqlbackup: INFO: Copying database directory: datadir/performance_schema
141204 13:10:39 mysqlbackup: INFO: Copying database directory: datadir/test
141204 13:10:39 mysqlbackup: INFO: Copying database directory: datadir/test2
141204 13:10:40 mysqlbackup: INFO: Copying database directory: datadir/mysql
141204 13:10:41 mysqlbackup: INFO: Copying database directory: datadir/performance_schema
141204 13:10:42 mysqlbackup: INFO: Copying database directory: datadir/test
141204 13:10:42 mysqlbackup: INFO: Copying database directory: datadir/test2
141204 13:10:43 mysqlbackup: INFO: Total files as specified in image: 161
141204 13:10:43 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql
141204 13:10:43 mysqlbackup: INFO: Copy-back operation completed successfully.
 mysqlbackup: INFO: Source Image Path = /home/admin/backups/my.mbi


 mysqlbackup: INFO: Creating 14 buffers each of size 65536.
141204 13:10:43 mysqlbackup: INFO: Apply-log operation starts with following threads
		1 read-threads    1 process-threads
 mysqlbackup: INFO: Using up to 100 MB of memory.
141204 13:10:43 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 1631744, end lsn 1631766,
          start checkpoint 1631766.
InnoDB: Doing recovery: scanned up to log sequence number 1631766
 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
141204 13:10:44 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 1631766.
 mysqlbackup: INFO: Last MySQL binlog file position 0 120, file name mysqld-bin.000004:120
141204 13:10:44 mysqlbackup: INFO: The first data file is '/var/lib/mysql/ibdata1'
          and the new created log files are at '/var/lib/mysql'
141204 13:10:44 mysqlbackup: INFO: Apply-log operation completed successfully.
141204 13:10:44 mysqlbackup: INFO: Full Backup has been restored successfully.

mysqlbackup completed OK!

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.

$ 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.2, “Performing a Restore Operation”.