Innobackupex-based full recovery and Innobackupex-based recovery

Innobackupex-based full recovery and Innobackupex-based recovery
For full MySQL recovery, we can use Innobackupex's multiple backups and binlog to restore the database to the fault point. Here, full recovery is relative to point-in-time recovery (also called Incomplete recovery ). This article mainly demonstrates how to perform a full 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

1. Concept of full recovery

Full recovery refers to the time when the database is restored to the latest by using the backup plus binlog.
Full recovery depends on the existence of the complete database backup and binlog. As long as the two are complete, we can completely restore them to the latest state.
The concept of full recovery is not limited to the hot backup and logical backup (mysqldump) methods, you can achieve full recovery.

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)]> 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

3. Demonstrate the recovery process

-- Add a record again. The record is saved in binlog instead of any backup. This record is used to verify full recovery of robin @ localhost [tempdb]> insert into tb values (3, 'inbinlog'); Query OK, 1 row affected (0.01 sec) -- current binlog location robin @ localhost [(none)]> show master status; + metric + ---------- + -------------- + ---------------- + metric + | File | Position | Binlog_Do_DB | metric | usage | + metric + ---------- + -------------- + ------------------ + dimensions + | inst3606bin. 000014 | 1200 | + -------------------- + ---------- + -------------- + ------------------ + ------------------- + -- use the binlog events command to view the last insert record, robin @ localhost [(none)]> show binlog events in 'inst3606bin. 000014 'limits; + region + ------ + ------------ + ----------- + region + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + region + ------ + ------------ + ----------- + ------------- + region + | inst3606bin. 000014 | 668 | Query | 3606 | 751 | BEGIN | inst3606bin. 000014 | 751 | Query | 3606 | 862 | use 'tempdb'; insert into tb values (2, 'incbak') | inst3606bin. 000014 | 862 | Xid | 3606 | 893 | COMMIT/* xid = 449096 */| inst3606bin. 000014 | 893 | Query | 3606 | 973 | flush engine logs | inst3606bin. 000014 | 973 | Query | 3606 | 1056 | BEGIN | inst3606bin. 000014 | 1056 | Query | 3606 | 1169 | use 'tempdb'; insert into tb values (3, 'inbinlog') | inst3606bin. 000014 | 1169 | Xid | 3606 | 1200 | COMMIT/* xid = 449997 */| + -------------------- + ------ + ------------ + ----------- + region + -- view the location of the binlog, robin @ localhost [(none)]> show variables like 'Log _ bin_basename '; + bytes + | Variable_name | Value | + ---------------- + bytes + | log_bin_basename |/data/inst3606/log/bin/inst3606bin | + ---------------- + bytes + a, based on full backup apply, 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 -- close the instance SHELL before copy back> netstat-nltp | grep mysql | grep 3606 SHELL> mv/data/inst3606/data3606/data/inst3606/data3606bk SHELL> mkdir-p/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/my3606.cnf & SHELL> SQL-P3606robin @ localhost [(none)]> use tempdb; -- as shown below, we can see record 3, 'inbinlog' record is not restored robin @ localhost [tempdb]> select * from tb; + ------ + --------- + | id | val | + ------ + --------- + | 1 | fullbak | -- Author: Leshami | 2 | Incbak | -- Blog: http://blog.csdn.net/leshami+------+---------+2 rows in set (0.00 sec) e. Use binlog to completely restore SHELL> cd/hotbak/inc/SHELL> more xtrabackup_binlog_info -- Obtain the binlog position inst3606bin from innobackupex. 000014 893 -- use the latest SHELL appended with mysqlbinlog> mysqlbinlog/data/inst3606/log/bin/inst3606bin. 000014 -- start-position = 893 \> | mysql-urobin-pxxx-P3606-S/tmp/mysql3606.sock -- verify, you can see 3rd records and the recovered SHELL> mysql-urobin-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 | 3 | Inbinlog | + ------ + ---------- +

4. Summary
A. For full recovery, we need to use a combination of backup and binlog.
B. During recovery, use the apply-log method with read-only to prepare full backup.
C. Use the apply-log method with read-only to add a prepare to the backup. read-only is not required for the last Incremental backup.
D. Stop the original Instance and start the recovered instance after copy-back.
E. Obtain the last binlog and location information from the Innobakcupex backup information.

F. Use mysqlbinlog to append logs to the latest time