binlog2sql的应用

原文链接：https://www.cpweb.top/1032

一、场景

  每天晚上23：00进行数据库的全备和binlog备份。每天中午12:00，进行binlog备份。
  故障：某人员周二上午10点误删了一个核心表，binlog和全备都是好的。

	恢复思路：
		• 首先从全备中使用命令过滤出和核心表相关的语句，恢复到上一天晚上23：00状态。
		• 然后从全备和binlog日志中找出开始和结束的position号，使用binlog2sql去指定过滤核心表相关的数据。
		• 最后将过滤好数据拿到数据库中去恢复。

 

二、模拟环境

（1）模拟原始数据
mysql> create database userdata;
mysql> use userdata;
mysql> create table user1 (id int);
mysql> insert user1 values(1),(2),(3);
mysql> create table user2 (id int);
mysql> insert user2 values(4),(5),(6);

（2）模拟周一晚上23:00全备
[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --max_allowed_packet=64M | gzip > /backup/full_$(date +%F).sql.gz

（3）模拟周二白天数据变化
mysql> update userdata.user2 set id=1;

（4）模拟周二10点的误删核心表
mysql> delete from userdata.user2;   // 注意如果是drop删表的话，用binlog2sql恢复就不行。

 

三、过滤全备中核心表数据

[root@db01 backup]# gunzip full_2020-11-06.sql.gz
[root@db01 backup]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `user2`/!d;q' /backup/full_2020-11-06.sql > /backup/table1.sql
[root@db01 backup]# grep -i 'INSERT INTO `user2`' /backup/full_2020-11-06.sql >> /backup/table1.sql
[root@db01 backup]# cat table1.sql 

DROP TABLE IF EXISTS `user2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `user2` VALUES (4),(5),(6);

 

四、使用binlog2sql过滤数据

（1）获取开始position号
[root@db01 backup]# grep "\-\- CHANGE MASTER TO" /backup/full_2020-11-06.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1221;

（2）安装binlog2sql
[root@db01 ~]# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
[root@db01 binlog2sql]# yum install python3
[root@db01 binlog2sql]# pip3 install -r requirements.txt

（3）过滤核心表数据
[root@db01 binlog2sql]# python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p000000 -duserdata -t user2 --start-file='mysql-bin.000003' --start-pos='1221'
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=4 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=5 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=6 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
DELETE FROM `userdata`.`user2` WHERE `id`=1 LIMIT 1; #start 1503 end 1738 time 2020-11-06 05:32:09
DELETE FROM `userdata`.`user2` WHERE `id`=1 LIMIT 1; #start 1503 end 1738 time 2020-11-06 05:32:09
DELETE FROM `userdata`.`user2` WHERE `id`=1 LIMIT 1; #start 1503 end 1738 time 2020-11-06 05:32:09

  我们来看上面过滤，效果看起来挺不错，只要去掉后三行的delete操作就好了，但是因为以上是我们操作数据量少，所以一目了然。如果数据量很大，必然update和delete操作记录交错混杂，那么如何去过滤，当然花时间肯定可以过滤出。
  最方便还是要确定删表之前结束的position号，我们可以通过以下方式去分析：
  数据库内show查看binlog日志内容。

mysql> show binlog events in 'mysql-bin.000003';    # 下面列出的就是删表的事件。
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
......省略
| mysql-bin.000003 | 1568 | Query          |         1 |        1636 | BEGIN                                |
| mysql-bin.000003 | 1636 | Table_map      |         1 |        1688 | table_id: 149 (userdata.user2)       |
| mysql-bin.000003 | 1688 | Delete_rows    |         1 |        1738 | table_id: 149 flags: STMT_END_F      |
| mysql-bin.000003 | 1738 | Xid            |         1 |        1769 | COMMIT /* xid=595 */                 |
+------------------+------+----------------+-----------+-------------+--------------------------------------+

  因为delete是DML操作，相对DDL操作记录的信息可读性差，通过上面执行结果我们也可以看到，不好确定。
  我这数据量小，从Event_type列中的Delete_rows就分析出来，结束position号为1568。

  使用mysqlbinlog命令查看分析binlog日志文件。通过这种方式确定结束position号可能要准确些，确定结束position号为1568，当然数据量大确定起来也麻烦，因为输出信息超级多，多利用三剑客。哈哈，啰嗦了，也不绝对大家随意，只要可以分析出来，恢复数据就好。

[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -v --start-position=1221 mysql-bin.000003
......省略
# at 1568
#201106  5:32:09 server id 1  end_log_pos 1636 CRC32 0x453c8283 	Query	thread_id=24	exec_time=0	error_code=0
SET TIMESTAMP=1604658729/*!*/;
BEGIN
/*!*/;
# at 1636
#201106  5:32:09 server id 1  end_log_pos 1688 CRC32 0xba01b7e9 	Table_map: `userdata`.`user2` mapped to number 149
# at 1688
#201106  5:32:09 server id 1  end_log_pos 1738 CRC32 0x6fc8d5e4 	Delete_rows: table id 149 flags: STMT_END_F
### DELETE FROM `userdata`.`user2`
### WHERE
###   @1=1
### DELETE FROM `userdata`.`user2`
### WHERE
###   @1=1
### DELETE FROM `userdata`.`user2`
### WHERE
###   @1=1
# at 1738
......

  再次过滤核心表数据

[root@db01 binlog2sql]# python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p000000 -duserdata -t user2 --start-file='mysql-bin.000003' --start-pos=1221 --stop-pos=1568
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=4 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=5 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=6 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46

[root@db01 binlog2sql]# python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p000000 -duserdata -t user2 --start-file='mysql-bin.000003' --start-pos=1221 --stop-pos=1568 > /backup/table2.sql

 

五、恢复数据

mysql> set sql_log_bin=0;
mysql> use userdata;
mysql> source /backup/table1.sql;
mysql> select * from user2;
+------+
| id   |
+------+
|    4 |
|    5 |
|    6 |
+------+
mysql> source /backup/table2.sql;
mysql> select * from user2;
+------+
| id   |
+------+
|    1 |
|    1 |
|    1 |
+------+
mysql> set sql_log_bin=1;

 
  以上是我对binlog2sql一些基本用法和粗浅见解，有什么问题望大佬们指正。binlog2sql还有不少用法例如可以通过时间去过滤、使用flashback模式生成回滚sql等，这里就不介绍了，文档比我说的好，文档地址：https://github.com/danfengcao/binlog2sql 。

  参考文章：https://www.jianshu.com/p/6fbdcb7695cb