Incomplete recovery based on Innobackupex

    for the incomplete recovery of MySQL, We can restore the database to any moment with the help of Innobackupex multiple backups plus binlog. The incomplete recovery here (also called point-in-time recovery) is relative to full recovery. This paper mainly demonstrates how to do an incomplete recovery based on Innobackupex for your reference.

    for knowledge points about Innobackupex backup recovery, please refer to the following links:
        Innobackupex full database
        extracting binary logs using Mysqlbinlog
         Innobackupex-based Full recovery
        Innobackupex-based provisioning and recovery
        Full recovery based on Innobackupex

1, the concept of incomplete recovery
    Incomplete recovery, point-in-time recovery, is the use of backups plus Binlog logs to restore the database to any specified point in the day.
    Incomplete recovery relies on full database backup and Binlog backup, as long as 2 exist, any data loss, misoperation, can be restored to any specified point in time. The concept of
    incomplete recovery is not limited to hot spare and logical backup (mysqldump), and can be implemented without full recovery.

A. Create demo environment [email protected][(none)]> show variables like ' version '; --current MySQL version +---------------+------------+| variable_name | Value |+---------------+------------+| Version | 5.6.12-log |+---------------+------------+[email protected][(none)]> reset Master;   Query OK, 0 rows affected (0.03 sec) [email protected][(none)]> use tempdb; [Email protected]   [tempdb]> CREATE TABLE TB (ID smallint,val varchar (20)); [Email protected]  [tempdb]> INSERT into TB values (1, ' Fullbak '); --Create a fully prepared shell> Innobackupex--user=robin-password=xxx--port=3606--socket=/tmp/mysql3606.sock--defaults-file=/ ETC/MY3606.CNF \ >/hotbak/full--no-timestamp B, create an add--insert a record to tb[email protected][tempdb]> inserts before creating the add-on   Into TB values (2, ' Incbak '); shell> Innobackupex--user=robin-password=xxx--port=3606--socket=/tmp/mysql3606.sock--defaults-file=/etc/ MY3606.CNF \ >--incremental/hotbak/inc--incremental-basedir=/hotbak/full--no-timestamp --Add another record [email protected][tempdb]> insert into TB values (3, ' pointrecover '); Query OK, 1 row affected (0.01 sec)-note the current point in time for subsequent incomplete recovery [email protected][tempdb]> system date; Thu Dec 11:53:54 CST 2014--analog misoperation [email protected][tempdb]> truncate TABLE TB; Query OK, 0 rows affected (0.01 sec) C, again fully shell> Innobackupex--user=robin-password=xxx--port=3606--socket=/tmp/mys Ql3606.sock--defaults-file=/etc/my3606.cnf >/hotbak/full2 added a new table after the full preparation of the--no-timestamp--[email protected][ Tempdb]> CREATE TABLE tb_after_truncate (ID int,val varchar (20)); Query OK, 0 rows affected (0.02 sec)

3. Demonstrate the recovery process

--The following to clarify the idea:--current backup situation: Full Plus + add + full--we truncate the table TB after the add-on, and then create a full-ready, a new table tb_after_truncate. --At this point we need to restore the database to truncate (misoperation)--The solution: we need to use the first full standby + add-on +binglog to restore to the truncate before the current second full standby. A, first do based on the full preparation of the apply, note that at this time the use of--redo-only shell> Innobackupex--apply-log--redo-only--user=robin-password=xxx--port= 3606 \ >--defaults-file=/etc/my3606.cnf/hotbak/full B, based on the add-on apply, there is no--redo-only at this time, if there are multiple addenda, only the last one is not specified--redo- Only shell> Innobackupex--apply-log--user=robin-password=xxx--port=3606--defaults-file=/etc/my3606.cnf \ > /hotbak/full--incremental-dir=/hotbak/inc C, copy back shell> mysqldown-p3606--copy back before close instance shell> NE Tstat-nltp|grep mysql|grep 3606 shell> mv/data/inst3606/data3606/data/inst3606/data3606bk SHELL> mkdir-p/da ta/inst3606/data3606 shell> Innobackupex--user=robin-password=xxx--port=3606--copy-back/hotbak/full-- DEFAULTS-FILE=/ETC/MY3606.CNF shell> Chown-r mysql:mysql/data/inst3606/data3606 D, starting the restored instance SHELL> Mysqld_safe--defaults-file=/etc/my3606.cnf &SHELL> mysql-uroot-pxxx-p3606-s/tmp/mysql3606.sock >-E "Sele CT * from TEMPDB.TB "warning:using a password in the command line interface can be insecure.+------+---------+| ID |    Val |+------+---------+| 1 |    Fullbak | | 2 | Incbak |+------+---------+--Gets the increment after the log positionshell> cd/hotbak/inc/shell> more Xtrabackup_binlog_ infoinst3606bin.000001 774--used stop-datetime here to append the log to truncate shell> mysqlbinlog/data/inst3606/log/bin/ inst3606bin.000001--start-position=774--stop-datetime= "2014-12-25 11:53:54" > |mysql-urobin-pxxx-p3606-s/tmp/ mysql3606.sock--Verify that the results are as follows, and you can see that the shell> mysql-uroot-pxxx-p3606-s/tmp/mysql3606.sock >-E "select * f has been restored to truncate Rom TEMPDB.TB "warning:using a password on the command line interface can be insecure.+------+--------------+| ID |    Val |+------+--------------+| 1 |    Fullbak | | 2 |    Incbak | | 3 | Pointrecover |+------+--------------+--If we need to continue to restore the back of the transaction, we can find the truncate position before and after, and then skip this positionshell> mysqlbinlog/data/inst3606/log/bin/ inst3606bin.000001--start-datetime= "2014-12-25 11:53:54" |grep truncate-a5truncate table tb/*!*/;# at 1180#141225 11:55:35 Server ID 3606 end_log_pos 1260 CRC32 0x12f55fc5 Query thread_id=928 exec_time=0 error_code=0set TIMESTAM p=1419479735/*!*/;/*!\c latin1 *//*!*/;--create table tb_after_truncate (ID int,val varchar)/*!*/;# at 1392#141225 13:06:47 Server ID 3606 end_log_pos 1415 CRC32 0xf956f311 stopdelimiter; # End of Log file--we found the position as 1260, skipping 126 0 further additions binlogshell> mysqlbinlog/data/inst3606/log/bin/inst3606bin.000001--start-position=1260 > |mysql- Urobin-pxxx-p3606-s/tmp/mysql3606.sock--Verify the appended results, you can see that the table tb_after_truncate exists [[email protected] ~]$ Mysql-uroot -pxxx-p3606-s/tmp/mysql3606.sock >-E "desc tempdb.tb_after_truncate" warning:using a password on the command line I Nterface can be insecure.+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID | Int (11) |     YES | |       NULL | || Val | varchar (20) |     YES | |       NULL | |+-------+-------------+------+-----+---------+-------+

4. Summary
A, incomplete recovery (point-in-time recovery) is essentially equivalent to the full recovery operation mode
B, incomplete recovery we need to determine the point of time or binlog that needs to be restored position
C, once you have identified a point in time for recovery, select all backups since the last full backup to recover
D. Use the Binlog log to append to a definite point in time after recovery is complete
E, append Binlog logs can be based on position, or can be based on datetime

F, can also skip the point of failure, continue to append the following Binlog log to the latest, such as the demonstration at the end of this article

Incomplete recovery based on Innobackupex