Innobackupex-based Incomplete recovery and Innobackupex-based recovery

Innobackupex-based Incomplete recovery and Innobackupex-based recovery
For incomplete MySQL recovery, we can use Innobackupex's multiple backups and binlog to restore the database to any point in time. Incomplete recovery (also called point-in-time recovery) is relative to full recovery. This article mainly demonstrates how to perform incomplete restoration based on Innobackupex for your reference.

For information about Innobackupex Backup recovery, refer to the following link:
Innobackupex full backup database
Use mysqlbinlog to extract binary logs
Innobackupex-based full Backup Recovery
Innobackupex-Based Incremental backup and recovery
Innobackupex-based full recovery

1. Concepts of Incomplete recovery
Incomplete recovery: Instant recovery refers to the restoration of the database to any specified time point using the backup plus binlog.
Incomplete recovery depends on the complete database backup and binlog backup. Any data loss or misoperation can be recovered to any specified time point as long as the two exist.
Incomplete recovery is not limited to hot backup and logical backup (mysqldump.

2. Demonstrate the backup process

A. Create the demo environment robin @ localhost [(none)]> show variables like 'version '; -- current MySQL version + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + robin @ localhost [(none)]> reset master; Query OK, 0 rows affected (0.03 sec) robin @ localhost [(none)]> use tempdb; robin @ localhost [tempdb]> create table tb (id smallint, val varchar (20); robin @ localhost [tempdb]> insert into tb values (1, 'fullbak '); -- create a full-Backup SHELL> innobackupex -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock -- defaults-file =/etc/my3606.cnf \>/hotbak/ full -- no-timestamp B, create an incremental backup -- insert a record to tbrobin @ localhost [tempdb]> insert 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, robin @ localhost [tempdb]> insert into tb values (3, 'pointrecover'); Query OK, 1 row affected (0.01 sec) -- write down the current time point for future Incomplete recovery of robin @ localhost [tempdb]> system date; thu Dec 25 11:53:54 CST 2014 -- simulate misoperations robin @ localhost [tempdb]> truncate table tb; Query OK, 0 rows affected (0.01 sec) c. Re-use the full-Backup SHELL> innobackupex -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock -- defaults-file =/etc/my3606.cnf \>/hotbak/ full2 -- no-timestamp -- Add a new table after full backup: robin @ localhost [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 is a clear idea: -- current backup: Full backup + Incremental backup + full backup -- We truncate the table tb after the Incremental backup, and then create a full backup, A table tb_after_truncate is created. -- In this case, we need to restore the database to truncate (misoperation) before -- Solution: we need to use the first full backup + Incremental backup + binglog to restore to truncate, the second backup is unavailable. A. apply based on full backup first. Note, -- redo-only SHELL> innobackupex -- apply-log -- redo-only -- user = robin-password = xxx -- port = 3606 \> -- defaults-file =/etc/ my3606.cnf/hotbak/full B, apply based on Incremental backup, -- There is no -- redo-only at this time. If there are multiple incremental backups, you do not need to specify -- 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 -- disable the instance SHELL> netstat-nltp | grep mysql | grep 3606 SHELL> mv/data/inst3606/data3606/data/inst3606/data3606bk SHELL> mkdir-p before copy back /data/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. Restart the recovered instance SHELL> mysqld_safe -- defaults-file =/etc/my36 06. cnf & SHELL> mysql-uroot-pxxx-P3606-S/tmp/mysql3606.sock \>-e "select * from tempdb. tb "Warning: Using a password on the command line interface can be insecure. + ------ + --------- + | id | val | + ------ + --------- + | 1 | fullbak | 2 | Incbak | + ------ + --------- + -- get the incremental log positionSHELL> cd/ hotbak/inc/SHELL> more xtrabackup_binlog_infoinst3606bin.000001 774 -- stop-datetime is used to append logs to SH before truncate. ELL> mysqlbinlog/data/inst3606/log/bin/inst3606bin. 000001 -- start-position = 774 -- stop-datetime = "11:53:54" \> | mysql-urobin-pxxx-P3606-S/tmp/mysql3606.sock -- the verification result is as follows, we can see that SHELL> mysql-uroot-pxxx-P3606-S/tmp/mysql3606.sock \>-e "select * from tempdb has been restored to truncate. 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 resume subsequent transactions, we can find the location before and after truncate, and skip this positionSHELL> mysqlbinlog/data/inst3606/log/bin/inst3606bin. 000001 -- start-datetime = "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 = 0 set timestamp = 1419479735 /*! */;/*! \ C latin1 *//*! */; -- Create table tb_after_truncate (id int, val varchar (20 ))/*! */; # At 1392 #141225 13:06:47 server id 3606 end_log_pos 1415 CRC32 0xf956f311 StopDELIMITER; # End of log file -- the position we find is 1260, skip binlogSHELL before 1260> mysqlbinlog/data/inst3606/log/bin/inst3606bin. 000001 -- start-position = 1260 \> | mysql-urobin-pxxx-P3606-S/tmp/mysql3606.sock -- verify the append result, the table tb_after_truncate contains [mysql @ app ~]. $ Mysql-uroot-pxxx-P3606-S/tmp/mysql3606.sock \>-e "desc tempdb. tb_after_truncate "Warning: Using a password on the command line interface 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 basically the same as full recovery
B. Incomplete recovery we need to determine the time point to be restored or binlog position
C. Once the time point for recovery is determined, select all the backups since the last full backup for recovery.
D. Use binlog to append the data to a specified time point after the restoration is completed.
E. append binlog logs based on position or datetime

F. you can skip the fault point and append the binlog to the latest version, as shown in the demo at the end of this article.