MySQL Innobackupex backup +binlog Full recovery of log (command-line execution mode)

Objective:
MySQL Full recovery, we can use the full backup +binlog to restore the database to the point of failure.
Backups can be hot standby and logical Backups (mysqldump), and full recovery can be achieved as long as the backup and Binlog are complete.

1. Preparing the lab environment
Mysql> select version ();
+------------+
| Version () |
+------------+
| 5.6.25-log |
+------------+
1 row in Set (0.00 sec)
mysql> CREATE DATABASE Com_rec;
Query OK, 1 row Affected (0.00 sec)
mysql> use Inc_rec;
Database changed
Mysql> CREATE TABLE andy (id int);
Query OK, 0 rows affected (0.08 sec)
Mysql> insert INTO Andy values (1), (2);
Query OK, 2 rows Affected (0.00 sec)
Records:2 duplicates:0 warnings:0
2. Fully prepared
[Email protected] full]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=oracle--port=3606/ xtrabackup/full/
Xtrabackup:transaction Log of LSN (1662519) to (1662519) was copied.
170609 17:34:34 completed ok!
3. View the fully-prepared build file
[Email protected] full]# ll/xtrabackup/full/
Total 4
Drwxr-x---. 6 root root 4096 June 9 17:34 2017-06-09_17-34-30
4. Simulate Business new data
Mysql> insert INTO Andy values (3), (4);
Query OK, 2 rows affected (0.14 sec)
Records:2 duplicates:0 warnings:0
Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)
5. Incremental backup
--Create and assign an incremental backup directory
[Email protected] full]# mkdir-p/xtrabackup/incr/
[Email protected] full]# chown-r mysql:mysql/xtrabackup/incr/
[Email protected] full]# ll/xtrabackup/
Total 8
Drwxr-xr-x. 3 MySQL mysql 4096 June 9 03:53 full
Drwxr-xr-x. 2 MySQL mysql 4096 June 9 04:00 Incre
--Official start of incremental backup
[Email protected] full]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=oracle--incremental \
--incremental-basedir=/xtrabackup/full/2017-06-09_17-34-30//xtrabackup/incr/
####################################### #下面是增量备份输出
。。。 Omitted
Xtrabackup:transaction Log of LSN (1665808) to (1665808) was copied.
170609 17:36:46 completed ok!
6. Re-simulate new business, the record is saved in Binlog, and does not exist in any backup, this record is used to verify the full recovery
Mysql> insert INTO Andy values (5);
Query OK, 1 row Affected (0.00 sec)
7. Note the position point after the operation
Mysql> Show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 1104 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)
8. Toggle Binlog Log
mysql> flush logs;
Query OK, 0 rows Affected (0.00 sec)
9. Use the Binlog Events command to view a record of our last insert
Mysql> show Binlog events in ' binlog.000005 ';
+---------------+------+-------------+-----------+-------------+----------------------------------------------- +
| Log_name | Pos | Event_type | server_id | End_log_pos | Info |
+---------------+------+-------------+-----------+-------------+----------------------------------------------- +
| binlog.000005 | 4 | Format_desc | 1 | 120 | Server ver:5.6.25-log, Binlog ver:4 |
| binlog.000005 | 120 | Query | 1 | 221 | Drop Database Inc_rec |
| binlog.000005 | 221 | Query | 1 | 324 | Create Database Com_rec |
| binlog.000005 | 324 | Query | 1 | 430 | Use ' Com_rec '; CREATE TABLE andy (id int) |
| binlog.000005 | 430 | Query | 1 | 515 | BEGIN |
| binlog.000005 | 515 | Query | 1 | 625 | Use ' Com_rec '; Insert into Andy values (1), (2) |
| binlog.000005 | 625 | Xid | 1 | 656 | COMMIT/* xid=67 */|
| binlog.000005 | 656 | Query | 1 | 741 | BEGIN |
| binlog.000005 | 741 | Query | 1 | 851 | Use ' Com_rec '; Insert into Andy values (3), (4) |
| binlog.000005 | 851 | Xid | 1 | 882 | COMMIT/* xid=81 */|
| binlog.000005 | 882 | Query | 1 | 967 | BEGIN |
| binlog.000005 | 967 | Query | 1 | 1073 | Use ' Com_rec '; Insert into Andy values (5) |
| binlog.000005 | 1073 | Xid | 1 | 1104 | COMMIT/* xid=96 */|
| binlog.000005 | 1104 | Rotate | 1 | 1148 | binlog.000006;pos=4 |
+---------------+------+-------------+-----------+-------------+----------------------------------------------- +
Rows in Set (0.00 sec)

10. Check the location of the Binlog and DataDir, prevent the MV DataDir error Operation Binlog, affect the recovery (Binlog and DataDir must be placed separately)
Mysql> Show variables like '%log_bin% ';
+---------------------------------+------------------------------------+
| variable_name | Value |
+---------------------------------+------------------------------------+
| Log_bin | On |
| Log_bin_basename | /data/mysql/binarylog/binlog |
| Sql_log_bin | On |
+---------------------------------+------------------------------------+
6 rows in Set (0.00 sec)

Mysql> Show variables like '%datadir% ';
+---------------+--------------+
| variable_name | Value |
+---------------+--------------+
| DataDir | /data/mysql/|
+---------------+--------------+
1 row in Set (0.00 sec)
11. Recovery
11.1 First do based on the fully prepared apply, note that at this time the use of--redo-only
[Email protected] full]# Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log--redo-only/xtrabackup/ full/2017-06-09_17-34-30/
170609 04:19:30 completed ok!
11.2 Restore Incremental backup set:--there is no--redo-only at this time, if there are multiple increments, only the last one does not need to specify--redo-only
[Email protected] full]# Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log/xtrabackup/full/ 2017-06-09_17-34-30/--incremental-dir=/xtrabackup/incr/2017-06-09_17-36-39/
170609 04:24:45 completed ok! #结果出现completed OK indicates complete success
Description:/xtrabackup/full/2017-06-09_17-34-30/is a fully prepared base directory, Incremental-dir is an incremental backup directory
12. Close the instance you want to recover
[Email protected] data]#/etc/init.d/mysql stop-p3306
Netstat-nltp|grep Mysql|grep 3606
13. Rename the original folder to a new location and create the original folder
[Email protected] full]# Mv/data/mysql/data/mysqlbak
[Email protected] full]# mkdir-p/data/mysql
14. Perform copy-recovered files to the original data location
[Email protected] full]# Innobackupex--defaults-file=/etc/my.cnf--user=root--copy-back/xtrabackup/full/ 2017-06-09_17-34-30/
170609 04:33:06 completed ok! #结果出现completed OK indicates complete success
Description:/xtrabackup/full/2017-06-09_17-34-30/is a fully-prepared base directory
15. Permissions Modification
[[email protected] ~]# mkdir-p/data/mysql/binarylog (description: Here I binlog in DataDir under the path, so to create a separate directory for Binlog)
--Binlog log MV back to the original location (if Binlog is not datadir, do not operate)
[Email protected] xtrabackup]# ll/data/mysqlbak/binarylog/
Total 28
-RW-RW----. 1 MySQL MySQL 164 June 9 06:12 binlog.000001
-RW-RW----. 1 MySQL MySQL 386 June 9 06:14 binlog.000002
-RW-RW----. 1 MySQL MySQL 143 June 9 06:53 binlog.000003
-RW-RW----. 1 MySQL MySQL 143 June 9 07:35 binlog.000004
-RW-RW----. 1 MySQL MySQL 1148 June 9 17:45 binlog.000005
-RW-RW----. 1 MySQL MySQL 143 June 9 17:48 binlog.000006
-RW-RW----. 1 MySQL MySQL 216 June 9 17:45 Binlog.index

[Email protected] data]# mv/data/mysqlbak/binarylog/*/data/mysql/binarylog/
[Email protected] xtrabackup]# ll/data/mysql/binarylog/
Total 28
-RW-RW----. 1 MySQL MySQL 164 June 9 06:12 binlog.000001
-RW-RW----. 1 MySQL MySQL 386 June 9 06:14 binlog.000002
-RW-RW----. 1 MySQL MySQL 143 June 9 06:53 binlog.000003
-RW-RW----. 1 MySQL MySQL 143 June 9 07:35 binlog.000004
-RW-RW----. 1 MySQL MySQL 1148 June 9 17:45 binlog.000005
-RW-RW----. 1 MySQL MySQL 143 June 9 17:48 binlog.000006
-RW-RW----. 1 MySQL MySQL 216 June 9 17:45 Binlog.index

[Email protected] data]# chown-r mysql:mysql/data/mysql
16. Start the restored instance
Mysqld_safe--DEFAULTS-FILE=/ETC/MY.CNF &
17. Login Check
[Email protected] ~]# mysql-uroot-poracle
Mysql> Use Com_rec
Mysql> select * from Andy;
+------+
| ID |
+------+
| 1 |
| 2 |
| 3 |
| 4 | > recovered successfully, but did not revert to the latest, missing id=5, ' Inbinlog ' record has not been restored.
+------+
18. Complete recovery with Binlog
[Email protected] ~]# cd/xtrabackup/incr/2017-06-09_17-36-39/
--Get Binlog's position from Innobackupex
[Email protected] 2017-06-09_17-36-39]# more Xtrabackup_binlog_info
binlog.000005882
--Use mysqlbinlog append the latest
[Email protected] 2017-06-09_17-36-39]# Mysqlbinlog--start-position=882--stop-position=1104/data/mysql/binarylog /binlog.000005 | Mysql-uroot-poracle
Add:
This is true, and the binlog of the row pattern can be done in this way. But there are a few drawbacks to this.
A. If the parsed binlog in the process of execution error, how to deal with it? Direct plus-f enforcement?
B. How do I stop in the middle of execution and run next? For example, I want to adjust MySQL parameters (need to restart) and then continue to run?
C. Only single thread execution. And Mysqlbinlog parsing is performed through pipelines, which has a higher performance overhead.
19. Verify that you can see the last record and the recovery
Mysql> select * from Andy;
+------+
| ID |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in Set (0.00 sec)

Summarize
A, for full recovery, we need to use a combination of backup plus binlog to achieve
b, during the recovery period, first use the Apply-log mode with read-only to prepare fully prepared
C, the next use with read-only Apply-log way to prepare, only the last one can not be added read-only
D, stop the original instance, and start the restored instance after Copy-back
E. Get the last Binlog log and location information from Innobakcupex backup information
F. Append the log to the latest time using the Mysqlbinlog method

MySQL Innobackupex backup +binlog Full recovery of log (command-line execution mode)