Innobackupex-based full-standby recovery

For the hot standby of MySQL database, Xtrabackup is the choice of most DBA friends. Xtrabackup embedded a Innobackupex can be used to hot standby MySQL database. This article describes the recovery of all prepared based on the Innobackupex tool and gives a demonstration for everyone to refer to.
All-in-a-Innobackupex for reference: Innobackupex fully prepared database

1, innobackupex recovery principle
    After creating a backup, the data are not ready to be restored. There might is uncommitted transactions to being undone or transactions in the logs to be replayed. Doing those pending operations would make the data les consistent and it is the purpose of the prepare stage. Once this have been done and the data is a ready-to-be used.
    to prepare a, backup with Innobackupex, the--apply-log and the path to the backup di Rectory as an argument:

Innobackupex replayed the committed transactions in the log Les (some transactions could has been done while the backup was being do) and rolled back the uncommitted ones. Once the information lay in the tablespace (the InnoDB les), and the "log" Les are re-created.

There is an inconsistency in the transaction during the backup (copy data), that is, when copy starts, some transactions are started, some are just beginning, and the copy is not committed or rolled back before or after the end.
These uncertain transactions need to be recovered to determine whether the final commit or rollback is ultimately committed. The operation at this stage is called the prepare phase.
This prepare phase relies on the Xtrabackup log (from InnoDB logfile) at the time of backup to achieve consistency using the--apply-log parameter.
The--apply-log parameter will be rolled forward or rolled back according to Xtrabackup log, and the InnoDB logfile file will be rebuilt after completion.

The--use-memory option the preparing process can be a speed up by using more memory in it. It depends on the free or available RAM on your system, it defaults to 100MB. In general, the most memory available to the process,the better. The amount of memory used in the process can is speci?ed by multiples of bytes:
During recovery, the--use-memory option accelerates the prepare process, which is set to 100MB by default if the system has enough memory available.

Innobackupex Recovery

2, demo recovery fully prepared

A, current environment [email protected][(none)]> show variables like ' version '; +---------------+------------+| variable_name | Value |+---------------+------------+| Version | 5.6.12-log |+---------------+------------+[email protected][(none)]> CREATE Database fullbakdb; Query OK, 1 row affected (0.01 sec) [email protected][(none)]> use FULLBAKDB[EMAIL PROTECTED][FULLBAKDB] > CREATE table TB (ID smallint,val varchar (20)); [Email protected] [fullbakdb]> INSERT into TB values (1, ' Robin '), (2, ' Leshami '); [Email protected] [fullbakdb]> SELECT * FROM tb;+------+---------+| ID |    Val |+------+---------+| 1 |    Robin | | 2 | Leshami |+------+---------+b, fully prepared database shell> Innobackupex--user=robin-password=xxx--port=3606--socket=/tmp/ Mysql3606.sock \ >--defaults-file=/data/inst3606/data3606/my3606.cnf/data/bak/hotbak--Below is the content after the backup is complete shell> pwd /data/bak/hotbakshell> lldrwxr-xr-x 7 root root 4096 2014/12/22 09:04 2014-12-22_09-04-05--View Backup BuildRelated files shell> ll 2014-12-22_09-04-05total 77944-rw-r--r--1 root root 357 2014/12/22 09:04 backup-my.cnfdrwx------ 2 root root 4096 2014/12/22 09:04 fullbakdb-rw-r-----1 root root 79691776 2014/12/22 09:04 ibdata1drwx------2 root R oot 4096 2014/12/22 09:04 mysqldrwxr-xr-x 2 root root 4096 2014/12/22 09:04 performance_schemadrwx------2 root Ro  OT 4096 2014/12/22 09:04 recoverdrwx------2 root root 4096 2014/12/22 09:04 sakila-rw-r--r--1 root root 26 2014/12/22 09:04 xtrabackup_binlog_info-rw-r-----1 root root 2014/12/22 09:04 xtrabackup_checkpoints-rw-r--r-- 1 root root 684 2014/12/22 09:04 xtrabackup_info-rw-r-----1 root root 2560 2014/12/22 09:04 Xtrabackup_logfilec, Empty the table TB to test the recovery function [email protected][fullbakdb]> truncate TABLE TB; Query OK, 0 rows affected (0.01 sec) [email protected][fullbakdb]> select * from TB; Empty Set (0.00 sec) d, recovery fully prepared--close legacy instance shell> mysqldown-p3606shell> netstat-nltp|grep Mysql|grep 3606--PrepareFull file shell> innobackupex--apply-log--user=robin-password=xxx--port=3606--socket=/tmp/mysql3606.sock >-- Defaults-file=/data/inst3606/data3606/my3606.cnf/data/bak/hotbak/2014-12-22_09-04-05innodb Backup Utility V1.5.1-xtrabackup; Copyright 2003, Innobase Oy ...  Non-important information ignored, the same as ... xtrabackup:starting InnoDB instance for recovery. --Start InnoDB instance recovery ....    Innodb:starting crash recovery. --began to crash recovery ... 141222 09:13:59 innobackupex:restarting xtrabackup with Command:xtrabackup--defaults-file= "/data/inst3606/data3606/ My3606.cnf "--defaults-group=" mysqld "--prepare--target-dir=/data/bak/hotbak/2014-12-22_09-04-05--tmpdir=/tmpfor Creating ib_logfile*--note here,--prepare is used, and InnoDB Logfilextrabackup:notice:xtrabackup_logfile was created already us Ed to '--prepare '. xtrabackup:using, the following InnoDB configuration for recovery: ...--author:l   eshami--Blog:http://blog.csdn.net/leshami Innodb:shutdown completed;        Log sequence number 391275633141222 09:14:02 innobackupex:completed ok! --Successful recovery--view information about the recovered file shell> ll 2014-12-22_09-04-05total 178404-rw-r--r--1 root root 357 2014/12/22 09:04 backup- my.cnfdrwx------2 root root 4096 2014/12/22 09:04 fullbakdb-rw-r-----1 root root 79691776 2014/12/22 09:14 ibdata1-r w-r--r--1 root root 50331648 2014/12/22 09:14 ib_logfile0-rw-r--r--1 root root 50331648 2014/12/22 09:13 ib_logfile1drwx ------2 root root 4096 2014/12/22 09:04 mysqldrwxr-xr-x 2 root root 4096 2014/12/22 09:04 performance_schemadrwx- -----2 root root 4096 2014/12/22 09:04 recoverdrwx------2 root root 4096 2014/12/22 09:04 sakila-rw-r--r--1 roo T root 2014/12/22 09:04 xtrabackup_binlog_info-rw-r--r--1 root root 2014/12/22 09:14 Xtrabackup_binlog_ Pos_innodb-rw-r-----1 root root 2014/12/22 09:14 xtrabackup_checkpoints-rw-r--r--1 root root 684 2014/12/2 2 09:04 xtrabackup_info-rw-r-----1Root root 2097152 2014/12/22 09:13 xtrabackup_logfile--Observe the contents of the above folder changes, you can see 09:14 for new or changed files, The main result is that the system Tablespace data file and the InnoDB log file are generated-and the checkpoint file for InnoDB is updated (note that the hot spare only involves the InnoDB engine, All INNODB related will change at Apply-log-Rename the original folder to the new location and create the original folder shell> mv/data/inst3606/data3606/data/inst3606/ data3606bkshell> mkdir-p/data/inst3606/data3606--Copy the recovered data file back to its original location shell> Innobackupex--user=robin- Password=xxx--port=3606--defaults-file=/data/inst3606/data3606bk/my3606.cnf \ >--copy-back/data/bak/hotbak/ 2014-12-22_09-04-05 ... Non-important information ignored, the same as ... innobackupex:starting to copy files in '/data/bak/hotbak/2014-12-22_09-04-05 '--                      Start copying the backed up files back to the original path innobackupex:back to original data directory '/data/inst3606/data3606 '-The original path location ............ Copy all data files, index files, format files, etc... innobackupex:starting to copy InnoDB system tablespace--Replication Systems tablespace innobackupex:in '/ Data/bak/hotbak/2014-12-22_09-04-05 ' Innobackupex:back to original InnoDB data directory '/data/inst3606/data3606 ' InnobacKupex:copying '/data/bak/hotbak/2014-12-22_09-04-05/ibdata1 ' to '/data/inst3606/data3606/ibdata1 ' Innobackupex: Starting to copy InnoDB undo Tablespaces--Copy undo table Space Innobackupex:in '/data/bak/hotbak/2014-12-22_09-04-05 ' Innobackupex:back to '/data/inst3606/data3606 ' innobackupex:starting to copy InnoDB log files--copy redo table space Innobac Kupex:in '/data/bak/hotbak/2014-12-22_09-04-05 ' innobackupex:back to original InnoDB log directory '/data/inst3606/ data3606 ' innobackupex:copying '/data/bak/hotbak/2014-12-22_09-04-05/ib_logfile1 ' to '/data/inst3606/data3606/ib_ Logfile1 ' innobackupex:copying '/data/bak/hotbak/2014-12-22_09-04-05/ib_logfile0 ' to '/data/inst3606/data3606/ib_ Logfile0 ' innobackupex:finished copying back files.141222 09:34:47 innobackupex:completed OK!--permissions modified shell> cp/data/i Nst3606/data3606bk/my3606.cnf/data/inst3606/data3606/my3606.cnfshell> Chown-r mysql:mysql/data/inst3606/ data3606--starting the restored instance shell> Mysqld_safe--defaults-file=/data/inst3606/data3606bk/mY3606.CNF &--Verify Recovery shell> sql-p3606[email protected][(None)]> select * FROM fullbakdb.tb;+------+---- -----+| ID |    Val |+------+---------+| 1 |    Robin | | 2 | Leshami |+------+---------+[email protected][(none)]> drop database fullbakdb; Query OK, 1 row affected (0.01 sec)

3. Summary
A, xtrabackup the purpose of recovery if the transaction (data) is guaranteed to be consistent, Xtrabackup log records the status of these transactional backups
b, the recovery process is divided into 2 stages, one is the prepare phase, one is the copy back stage, the original instance is closed before resuming
c, prepare phase will be based on the information captured from the InnoDB logfile (recorded in the Xtrabackup log) for the corresponding roll forward or rollback
D, prepare phase creates a new InnoDB logfile (empty) after a successful roll-forward or rollback
E, copy back stage is the successful recovery of all the files to the original or specified data directory (directory should be empty directory)
F, copy back before you need to close the original instance, if you restore to a different instance does not need
G, copy back should be done after the corresponding permission to modify
H. Start the restored instance and perform the relevant validation

Innobackupex-based full-standby recovery