模擬生產場景中需要基于某個事務點的恢復，使用存量備份與存量binlog

生成測試數據

(root@localhost) [(none)]> create database NanJing;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [test]> use NanJing;
Database changed
(root@localhost) [NanJing]> create table xianlin (id int);
Query OK, 0 rows affected (0.04 sec)
創建procedure生產測試數據
(root@localhost) [NanJing]> DELIMITER $$
(root@localhost) [NanJing]> CREATE PROCEDURE InsertSampleData()-> BEGIN->     DECLARE i INT DEFAULT 1;->->     WHILE i <= 1000 DO->         INSERT INTO xianlin (id) VALUES (i);->         SET i = i + 1;->     END WHILE;-> END$$
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [NanJing]> DELIMITER ;
(root@localhost) [NanJing]> CALL InsertSampleData();
Query OK, 1 row affected (2.12 sec)
(root@localhost) [NanJing]> select count(1) from xianlin;
+----------+
| count(1) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)步驟說明:
1.mysqldump備份集可以恢復至1000數據
2.利用binlog恢復剩余1000條數據
binlog起終點:master-data=2  ->  尋找drop之前的標號

模擬0級備份

[root@node01 ~]# mysqldump -uroot -p -S /tmp/mysql.sock -A --master-data=2 --single-transaction --max-allowed-packet=128M -R -E --triggers --set-gtid-purged=auto >/data/dumpAll_`date +%F`.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
Enter password:

模擬備份之后的數據變化

(root@localhost) [NanJing]> DROP PROCEDURE InsertSampleData;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [NanJing]> DELIMITER $$
(root@localhost) [NanJing]> CREATE PROCEDURE InsertSampleData()-> BEGIN->     DECLARE i INT DEFAULT 1001;->->     WHILE i <= 2000 DO->         INSERT INTO xianlin (id) VALUES (i);->         SET i = i + 1;->     END WHILE;-> END$$
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [NanJing]> DELIMITER ;
(root@localhost) [NanJing]> CALL InsertSampleData();
Query OK, 1 row affected (2.10 sec)
(root@localhost) [NanJing]> select count(1) from xianlin;
+----------+
| count(1) |
+----------+
|     2000 |
+----------+
1 row in set (0.01 sec)

模擬數據丟失

(root@localhost) [mysql]> drop database NanJing;
Query OK, 1 row affected (0.01 sec)

查看重要事務節點與binlog信息

由于我們的備份是設置記錄有GTID信息與MASTER，所以可以通過dump備份來查看這兩個關鍵信息
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '4dee4290-485c-11ef-8500-000c2946a607:1-1005'; (info:備份存在1-1005事務號事務內容)
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=285564; (info:備份存在POSTION號285564之前內容)查看binlog內容信息，獲取tail GTID
[root@node01 data]# cd 3306/log/
[root@node01 log]# ll
-rw-r-----. 1 mysql mysql      180 Jul 30 21:44 mysql-bin.000013
-rw-r-----. 1 mysql mysql   570354 Jul 31 22:03 mysql-bin.000014
-rw-r-----. 1 mysql mysql      448 Jul 31 18:29 mysql-bin.index
-rw-r--r--. 1 mysql mysql   528035 Jul 31 21:56 mysql-err.log
-rw-r-----. 1 mysql mysql  3820064 Jul 31 22:00 slow.log
(root@localhost) [(none)]> show binlog events in 'mysql-bin.000014';
| mysql-bin.000014 | 570164 | Gtid  | 51 | 570241 | SET @@SESSION.GTID_NEXT= '4dee4290-485c-11ef-8500-000c2946a607:2008'  --drop事務號為2008
| mysql-bin.000014 | 570241 | Query | 51 | 570354 | drop database NanJing /* xid=11339 */   

根據GTID導出binlog

利用mysqlbinlog工具截取上次全量備份之后的binlog
1006 - 2007
[root@node01 log]# mysqlbinlog --skip-gtids --include-gtids='4dee4290-485c-11ef-8500-000c2946a607:1006-2007' mysql-bin.000014 >/data/bin.sql
[root@node01 log]# ll /data/bin.sql
-rw-r--r--. 1 root root 751934 Jul 31 22:58 /data/bin.sql

恢復

由于恢復也會產生log所以設置sql_log_bin = 0；
(root@localhost) [(none)]> # set this session generate no log;
(root@localhost) [(none)]> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]> source /data/dumpAll_2030-07-31.sql
(root@localhost) [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| Benjamin           |
| NanJing            |
+--------------------+
(root@localhost) [test]> use NanJing
Database changed
(root@localhost) [NanJing]> select count(1) from xianlin;
+----------+
| count(1) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)
(root@localhost) [test]> source /data/bin.sql
(root@localhost) [NanJing]> select count(1) from xianlin;
+----------+
| count(1) |
+----------+
|     2000 |
+----------+
1 row in set (0.01 sec)