mysql256次利用_技术分享 | binlog 实用解析工具 my2sql

作者：赵黎明

爱可生 MySQL DBA 团队成员，Oracle 10g OCM，MySQL 5.7 OCP，擅长数据库性能问题诊断、事务与锁问题的分析等，负责处理客户 MySQL 及我司自研 DMP 平台日常运维中的问题，对开源数据库相关技术非常感兴趣。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

前言

大部分 DBA 应该都已经熟悉并使用过一些闪回工具，诸如：binlog2sql、MyFlash。 今天要介绍的是另一款基于 Go 编写的 binlog 解析工具：my2sql，他的同门师兄还有 my2fback、binlog_inspector(binlog_rollback)。 为什么不直接称其为闪回工具呢？因为闪回只是它其中一个功能，除此之外，还可用于从 binlog 中解析出执行过的 SQL 语句用于数据补偿，或者对线上执行的事务进行分析(捕获大/长事务)。

Github 地址：https://github.com/liuhr/my2sql

对比 • binlog2sql：Python 编写(执行时需要有 Python2.7、Python3.4+ 的环境)，用于生成回滚/前滚 SQL 进行数据恢复/补偿

• MyFlash：C 语言编写(需要动态编译成二级制脚本后执行)，用于生成反向 binlog 文件(二进制)进行数据恢复

• my2sql：Go 语言编写(可直接下载 linux 二进制版本执行)，除了闪回，还提供了前滚和事务分析的功能

主要参数 • -work-type：指定工作类型(前滚、闪回、事务分析)，合法值分别为：2sql(默认)、rollback、stats

• -sql：过滤 DML 语句的类型，合法值为：insert、update、delete

• -ignorePrimaryKeyForInsert：对于 work-type 为 2sql 的 insert 操作，忽略主键(适合大量数据导入的场景)

• -big-trx-row-limit int：判定为大事务的阈值(默认 500 行)，合法值区间：10-30000 行

• -long-trx-seconds int：判定为长事务的阈值(默认 300 秒)，合法值区间：1-3600 秒

• -databases：过滤库，默认为全部库

• -tables：过滤表，默认为全部表

• -start-file：指定开始的 binlog 文件

• -start-pos：指定 binlog 文件中开始的点位

• -start-datetime：指定开始的时间

• -stop-datetime：指定结束的时间

• -output-dir：指定文件生成目录

• -output-toScreen：指定输出到屏幕

• -tl：指定时区(time location)，默认为 local(Asia/Shanghai)

应用场景

场景 1：闪回 DML 误操作数据

-- 创建测试表

mysql> show create table t\G

*************************** 1. row ***************************

Table: t

Create Table: CREATE TABLE `t` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`k` int(11) NOT NULL DEFAULT '0',

`c` char(120) NOT NULL DEFAULT '',

`pad` char(60) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `k_1` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> select count(*) from t;

+----------+

| count(*) |

+----------+

| 10000 |

+----------+

1 row in set (0.00 sec)

-- 查看测试表校验值

mysql> checksum table t;

+-------+------------+

| Table | Checksum |

+-------+------------+

| zlm.t | 1966401574 |

+-------+------------+

1 row in set (0.02 sec)

-- 查看当前binlog

mysql> show master status;

+------------------+----------+--------------+------------------+----------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+----------------------------------------------+

| mysql-bin.000003 | 15994082 | | | 1d7ef0f4-4593-11eb-9f04-02000aba3c3e:1-23123 |

+------------------+----------+--------------+------------------+----------------------------------------------+

1 row in set (0.00 sec)

-- 删除5000条记录

mysql> delete from t where id<5001;

Query OK, 5000 rows affected (0.04 sec)

-- 执行my2sql生成回滚语句(rollback模式)

04:38 PM dmp1 (master) ~# ./my2sql -user zlm -password zlm -host 10.186.60.62 -port 3332 -work-type rollback -start-file mysql-bin.000003 -start-pos 15994082 --add-extraInfo -output-dir /tmp/my2sql_test

[2020/12/25 16:38:17] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 10.186.60.62 3332 zlm utf8 false false false Local false 0 0s 0s 0 false false 0}

[2020/12/25 16:38:17] [info] events.go:58 start thread 1 to generate redo/rollback sql

[2020/12/25 16:38:17] [info] events.go:58 start thread 2 to generate redo/rollback sql

[2020/12/25 16:38:17] [info] events.go:208 start thread to write redo/rollback sql into file

[2020/12/25 16:38:17] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000003, 15994082)

[2020/12/25 16:38:17] [info] stats_process.go:166 start thread to analyze statistics from binlog

[2020/12/25 16:38:17] [info] repl.go:15 start to get binlog from mysql

[2020/12/25 16:38:17] [info] binlogsyncer.go:777 rotate to (mysql-bin.000003, 15994082)

[2020/12/25 16:38:17] [info] events.go:242 finish processing mysql-bin.000003 16002473

[2020/12/25 16:38:22] [info] repl.go:83 deadline exceeded.

[2020/12/25 16:38:22] [info] repl.go:17 finish getting binlog from mysql

[2020/12/25 16:38:22] [info] stats_process.go:266 exit thread to analyze statistics from binlog

[2020/12/25 16:38:22] [info] events.go:183 exit thread 1 to generate redo/rollback sql

[2020/12/25 16:38:22] [info] events.go:183 exit thread 2 to generate redo/rollback sql

[2020/12/25 16:38:22] [info] events.go:257 finish writing rollback sql into tmp files, start to revert content order of tmp files

[2020/12/25 16:38:22] [info] rollback_process.go:15 start thread 1 to revert rollback sql files

[2020/12/25 16:38:22] [info] rollback_process.go:41 start to revert tmp file /tmp/my2sql_test/.rollback.3.sql into /tmp/my2sql_test/rollback.3.sql

[2020/12/25 16:38:22] [info] rollback_process.go:156 finish reverting tmp file /tmp/my2sql_test/.rollback.3.sql into /tmp/my2sql_test/rollback.3.sql

[2020/12/25 16:38:22] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files

[2020/12/25 16:38:22] [info] events.go:270 finish reverting content order of tmp files

[2020/12/25 16:38:22] [info] events.go:275 exit thread to write redo/rollback sql into file

-- 查看生成的文件

04:40 PM dmp1 /tmp/my2sql_test# ll

total 1228

-rw-r--r-- 1 root root 251 Dec 25 16:38 biglong_trx.txt

-rw-r--r-- 1 root root 288 Dec 25 16:38 binlog_status.txt

-rw-r--r-- 1 root root 1246880 Dec 25 16:38 rollback.3.sql

## 其中rollback.x.sql就是我们闪回数据需要的sql文件(x对应mysql-binlog.00000x的文件编号x)，由于执行命令时只指定了binlog开始的位置，实例中后续执行的DML事务也都会被记录

## 另外2个文件分别是从binlog中获取到的binlog状态和事务信息，之后的案例会详细展示说明，此处略过

-- 查看生成的回滚SQL文本文件

04:42 PM dmp1 /tmp/my2sql_test# tail -5 rollback.3.sql

INSERT INTO `zlm`.`t` (`id`,`k`,`c`,`pad`) VALUES (4,5027,'54133149494-75722987476-23015721680-47254589498-40242947469-55055884969-23675271222-20181439230-74473404563-55407972672','88488171626-98596569412-94026374972-58040528656-38000028170');

INSERT INTO `zlm`.`t` (`id`,`k`,`c`,`pad`) VALUES (3,4990,'51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495','00592560354-80393027097-78244247549-39135306455-88936868384');

INSERT INTO `zlm`.`t` (`id`,`k`,`c`,`pad`) VALUES (2,5025,'13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289','28733802923-10548894641-11867531929-71265603657-36546888392');

INSERT INTO `zlm`.`t` (`id`,`k`,`c`,`pad`) VALUES (1,5015,'68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441','22195207048-70116052123-74140395089-76317954521-98694025897');

# datetime=2020-12-25_16:33:30 database=zlm table=t binlog=mysql-bin.000003 startpos=15994218 stoppos=16002473

05:10 PM dmp1 /tmp/my2sql_test# cat rollback.3.sql |grep "INSERT INTO"|wc -l

5000