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 paper describes the recovery of the full prepared of the tool based on Innobackupex and gives a demonstration for everyone to participate.

Full preparation for Innobackupex: Innobackupex full 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 backup (copy data), that is, when copy starts. Some transactions have started. Some just started. The copy is either submitted or rolled back before or after the end.
These uncertain transactions need to be restored at the end to determine whether they are finally committed or rolled back. 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--apply-log parameters.
--apply-log parameters will be rolled forward or rolled back according to Xtrabackup log. 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, assuming that the system has large enough memory available. The value is set to 100MB by default.

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--The following is the content after the backup 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, Clear table TB for test 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. --Start 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 directory changes, can see 09:14 for new or changed files. The main result is the system Tablespace data file and the InnoDB log file--the same time the checkpoint file about InnoDB is updated (note that the hot spare involves only the InnoDB engine, All INNODB related will change at apply-log time-renaming the original directory to the new location. and create the original directory 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!--permission changes 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 stage, 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 the specified Data folder (folder should be empty folder)
F, copy back before you need to close the original instance, assuming that the recovery to a different instance does not need
G, copy back after the completion of the corresponding permission changes
H. Start the restored instance and perform the relevant validation

idkey=e61fdd22421b38cb5eef12db3c564ac0b5840f0f5cae4958e8b646740a79e680 "target=" _blank ">

Innobackupex-based full-standby recovery