当前位置：首页 > news >正文

Implement recovery based on PITR using dump file and binlog

news 2025/8/2 18:48:34

模拟生产场景中需要基于某个事务点的恢复，使用存量备份与存量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)