MySQL binlog日志解析工具--binlog_inspector

IT那活儿 2021-12-23

3015

点击上方“IT那活儿”，关注后了解更多精彩内容！！

一、简介

1. binlog_inspector通过解释mysql/mariadb binlog/relaylog实现以下三大功能:

1.1 flashback/闪回/回滚，实现DML的回滚到任意时间或者位置。

生成的SQL形式如下：

```sqlbeginDELETE FROM `binlog_inspector`.`emp` WHERE `id`=1# datetime=2017-10-23_00:14:28 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=417 stoppos=575commit```
复制

1.2 前滚，把binlog/relaylog的DML解释成易读的SQL语句。

生成的SQL形式如下：

```sqlbegin# datetime=2017-10-23_00:14:28 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=417 stoppos=575INSERT INTO `binlog_inspector`.`emp` (`id`,`name`,`sr`,`icon`,`points`,`sa`,`sex`) VALUES (1,'张三1','华南理工大学&SCUT',X'89504e47',1.1,1.1,1)commit```
复制

1.3 统计分析，统计各个表的DML情况，找出大事务与长事务。

2. 以上功能均可指定任意的单库多库，单表多表，任意时间点，任意binlog位置。

2.1 支持mysql5.5及以上，也支持mariadb的binlog，支持传统复制的binlog，也支持GTID的binlog。

2.2 支持直接指定文件路径的binlog，也支持主从复制， binlog_inspector作为从库从主库拉binlog来过解释。

2.3 支持目标binlog中包含了DDL(增加与减少表字段，变化表字位置)的场景。

3. 限制

3.1 binlog格式必须为row,且binlog_row_image=full

3.2 只能回滚DML，不能回滚DDL

3.3 支持V4格式的binlog， V3格式的没测试过

二、适用场景

1. 数据被误操作，需要把某几个表的数据不停机回滚到某个时间点

2. 数据异常，帮忙从binlog中找出这个表的某些数据是什么时间修改成某些值的

3. IO高TPS高，帮忙查出那些表在频繁更新

4. 需要把这个表从昨晚1点到3点的更新提供给开发查问题

5. 帮忙找出某个时间点数据库是否有大事务或者长事务

三、特点

1. 速度快。解释512MB的binlog：

1.1 生成回滚的SQL只需要1分26秒(6线程)

1.2 生成前滚的SQL只需要1分26秒(6线程)

1.3 生成表DML统计信息，大事务与长事务统计信息只需要55秒

1.4 mysqlbinlog解释同样的binlog只需要36秒

2. 支持V4版本的binlog，支持传统与GTID的binlog，支持mysql5.5与mairiadb5.5及以上版本的binlog，也同样支持relaylog(结果中注释的信息binlog=xxx startpos=xxx stoppos=xx是对应的主库的binlog信息)

--mtype=mariadb

3. 支持以时间及位置条件过滤，并且支持单个以及多个连续binlog的解释。

3.1 解释binlog的开始位置：

--start-binlog=mysql-bin.000101

--start-pos=4

3.2 解释binlog的结束位置：

--stop-binlog=mysql-bin.000105

--stop-pos=4

3.3 解释binlog的开始时间

--start-datetime="2018-04-21 00:00:00"

3.4 解释binlog的结束时间

--stop-datetime="2018-04-22 11:00:00"

4. 支持以库及表条件过滤, 以逗号分隔

--databases=db1,db2

--tables=tb1,tb2

5. 支持以DML类型(update,delete,insert)条件过滤

--sqltypes=delete,update

6. 支持分析本地binlog，也支持复制协议， binlog_inspector作为一个从库从主库拉binlog来本地解释

--mode=file //解释本地binlog

--mode=repl //binlog_inspector作为slave连接到主库拉binlog来解释

7. 输出的结果支持一个binlog一个文件，也可以一个表一个文件

--file-each-table

例如对于binlog mysql-bin.000101, 如果一个表一个文件，则生成的文件形式为db.tb.rollback.101.sql(回滚)，db.tb.forward.101.sql(前滚)，

否则是rollback.101.sql(回滚),forward.101.sql(前滚)

8. 输出的结果是大家常见的易读形式的SQL，支持表名前是否加数据库名

--prefix-database```sqlbegin# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;commit```
复制

否则为：

```sqlbegin# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822UPDATE `emp` SET `sa`=1001 WHERE `id`=5;# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930UPDATE .`emp` SET `name`=null WHERE `id`=5;commit```
复制

9. 输出结果支持是否保留事务

--keep-trx```sqlbegin# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;commit```
复制

不保留则是这样：

```sql# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;```
复制

如果复制因为特别大的事务而中断，则可以以不保留事务的形式生成前滚的SQL, 在从库上执行，然后跳过这个事务，再启动复制，免去重建从库的麻烦，特别是很大的库。

10. 支持输出是否包含时间与binlog位置信息

--extra-info

包含额外的信息则为：

```sql# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;```
复制

否则为：

```sqlUPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;```
复制

11. 支持生成的SQL只包含最少必须的字段, 前提下是表含有唯一索引

默认为：

```sqlUPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;DELETE FROM `binlog_inspector` WHERE `id`=5;```--full-columns 则为```sqlUPDATE `binlog_inspector`.`emp` SET `id`=5, `age`=21, `sex`='M',`sa`=1001, `name`='Danny' WHERE `id`=5 and `age`=21 and `sex`='M' and `sa`=900 and `name`='Danny';DELETE FROM `binlog_inspector` WHERE `id`=5 and `age`=21 and `sex`='M' and `sa`=900 and `name`='Danny';```
复制

12. 支持优先使用唯一索引而不是主键来构建where条件

--prefer-unique-key

有时不希望使用主健来构建wheret条件，如发生双写时，自增主健冲突了，这时使用非主健的唯一索引来避免生成的SQL主健冲突

13. 支持生成的insert语句不包含主健

--insert-ignore-primary

发生双写时，自增主健冲突了，这时使用这个参数来让生成的insert语句不包括主健来避免生成的SQL主健冲突

14. 支持大insert拆分成小insert语句。

--insert-rows=100

对于一个insert 1000行的插入，会生成10个insert语句，每个语句插入100行

15. 支持自定义DDL语句过滤正则表达式来输出目标DDL

--ddl-regexp

默认为"^\s*(alter|create|rename|truncate|drop)", 大小写不敏感

16. 支持目标binlog中包含DDL(增减字段,变化字段位置)的情形

binlog只保存了各个字段的位置，并没有保存各个字段的名字。在前滚与回滚的模式下, binlog_inspector需要拿到表结构信息来生成易读的SQL, 如果表结构有变化, 那如何处理？

例如表tmp的DDL如下：

```sqlcreate table emp (name varchar(50), sr text, points float, sa decimal(10,3), sex enum("f", "m"), icon blob)alter table emp add column id int  firsttruncate table empalter table emp add primary key (id)alter table emp modify id int auto_incrementalter TABLE emp add column updatetime datetime comment '更新时间', add createtime timestamp default current_timestamp comment '创建时间'alter TABLE emp drop column updatetime```
复制

但binlog_inspector这时获取到的表结构表结构如下：

```sqlCREATE TABLE `emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,`sr` text,`points` float DEFAULT NULL,`sa` decimal(10,3) DEFAULT NULL,`sex` enum('f','m') DEFAULT NULL,`icon` blob,`createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8```
复制

不清楚之前的表结构，就会出现错乱:

```sqlbegin;# datetime=2018-02-05_10:12:41 database=binlog_inspector table=emp binlog=mysql-bin.000001 startpos=1614 stoppos=1772INSERT INTO `binlog_inspector`.`emp` (`id`,`name`,`sr`,`points`,`sa`,`sex`) VALUES ('张三1',X'e58d8ee58d97e79086e5b7a5e5a4a7e5ada62653435554',1.100000023841858,1.1,1,X'89504e47');commit;```
复制

binlog_inspector会输出所有DDL的语句到ddl_info.log这个文件，有时间与位置信息，如:

```sqldatetime binlog startpos stoppos sql2018-02-05_10:12:18 mysql-bin.000001 1115 1320 create table emp (name varchar(50), sr text, points float, sa decimal(10,3), sex enum("f", "m"), icon blob)2018-02-05_10:15:10 mysql-bin.000001  8556       8694       alter table emp add column id int  first2018-02-05_10:16:41 mysql-bin.000001  8759       8856       truncate table emp2018-02-05_10:16:42 mysql-bin.000001  8921       9055       alter table emp add primary key (id)2018-02-05_10:17:21 mysql-bin.000001  9120       9262       alter table emp modify id int auto_increment2018-02-05_13:46:18 mysql-bin.000001  400409     400653     alter TABLE emp add column updatetime datetime comment '更新时间', add createtime timestamp default current_timestamp comment '创建时间'```
复制

表结构信息会dump到文件table_columns.json文件，如：

```json{"binlog_inspector.emp": {"_/0/0": {"database": "binlog_inspector","table": "emp","columns": [{"column_name": "id","column_type": "int"},{"column_name": "name","column_type": "varchar"},{"column_name": "sr","column_type": "text"},{"column_name": "points","column_type": "float"},{"column_name": "sa","column_type": "decimal"},{"column_name": "sex","column_type": "enum"},{"column_name": "icon","column_type": "blob"},{"column_name": "createtime","column_type": "timestamp"}],"primary_key": ["id"],"unique_keys": [],"ddl_info": {"binlog": "_","start_position": 0,"stop_position": 0,"ddl_sql": ""}}}}```
复制

结合上面的信息，手动修改table_columns.json，让其也保存有DDL前的表结构：

```json{"binlog_inspector.emp": {"mysql-bin.000001/8556/8694": {"database": "binlog_inspector","table": "emp","columns": [{"column_name": "name","column_type": "varchar"},{"column_name": "sr","column_type": "text"},{"column_name": "points","column_type": "float"},{"column_name": "sa","column_type": "decimal"},{"column_name": "sex","column_type": "enum"},{"column_name": "icon","column_type": "blob"}],"primary_key": [],"unique_keys": [],"ddl_info": {"binlog": "mysql-bin.000001","start_position": 8556,"stop_position": 8694,"ddl_sql": ""}},"_/0/0": {"database": "binlog_inspector","table": "emp","columns": [{"column_name": "id","column_type": "int"},{"column_name": "name","column_type": "varchar"},{"column_name": "sr","column_type": "text"},{"column_name": "points","column_type": "float"},{"column_name": "sa","column_type": "decimal"},{"column_name": "sex","column_type": "enum"},{"column_name": "icon","column_type": "blob"},{"column_name": "createtime","column_type": "timestamp"}],"primary_key": ["id"],"unique_keys": [],"ddl_info": {"binlog": "_","start_position": 0,"stop_position": 0,"ddl_sql": ""}}}}```
复制

并加上参数--table-columns=table_columns.json --only-table-columns让binlog_inspector从table_columns.json获取表结构信息，重新运行，生成的SQL无误了。

```sqlbegin;# datetime=2018-02-05_10:12:41 database=binlog_inspector table=emp binlog=mysql-bin.000001 startpos=1614 stoppos=1772INSERT INTO `binlog_inspector`.`emp` (`name`,`sr`,`points`,`sa`,`sex`,`icon`) VALUES ('张三1','**理工大学&SCUT',1.100000023841858,1.1,1,X'89504e47');commit;```
复制

四、安装与使用

1. 安装

https://github.com/GoDannyLai/binlog_inspector/releases中有编译好的linux与window二进制版本，可以直接使用，无其它依赖。

如果需要编译，请使用GO>=1.8.3版本来编译。使用的其中两个依赖库https://github.com/siddontang/go-mysql与https://github.com/dropbox/godropbox/database/sqlbuilder

有修改小部分的源码，请使用vendor中包，或者按照 `开源库所做的修改.txt` 中来修改https://github.com/siddontang/go-mysql与https://github.com/dropbox/godropbox/database/sqlbuilder

2. 使用

2.1 生成前滚SQL与DML报表:

./binlog_inspector --mode=repl --wtype=2sql --mtype=mysql --threads=4 --serverid=3331 --host=127.0.0.1 --port=330 --user=xxx --password=xxx --databases=db1,db2 --tables=tb1,tb2 --start-binlog=mysql-bin.000556 --start-pos=107 --stop-binlog=mysql-bin.000559 --stop-pos=4 --min-columns --file-each-table --insert-rows=20 --keep-trx --big-trx-rows=100 --long-trx-seconds=10 --output-dir=/home/apps/tmp --table-columns tbs_all_def.json
复制

2.2 生成回滚SQL与DML报表:

./binlog_inspector --mode=file --wtype=rollback --mtype=mysql --threads=4 --host=127.0.0.1 --port=3306 --user=xxx --password=xxx --databases=db1,db2 --tables=tb1,tb2 --start-datetime='2017-09-28 13:00:00' --stop-datetime='2017-09-28 16:00:00' --min-columns --file-each-table --insert-rows=20 --keep-trx --big-trx-rows=100 --long-trx-seconds=10 --output-dir=/home/apps/tmp --table-columns tbs_all_def.json /apps/dbdata/mysqldata_3306/log/mysql-bin.000556
复制

2.3 只生成DML报表:

./binlog_inspector --mode=file --wtype=stats --mtype=mysql --interval=20 --big-trx-rows=100 --long-trx-seconds=10 --output-dir=/home/apps/tmp mysql-bin.000556
复制

本文原创来源：IT那活儿微信公众号（上海新炬王翦团队）

mysql

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL binlog日志解析工具--binlog_inspector

点击上方“IT那活儿”，关注后了解更多精彩内容！！

本文原创来源：IT那活儿微信公众号（上海新炬王翦团队）

评论

相关阅读

MySQL binlog日志解析工具--binlog_inspector

点击上方“IT那活儿”，关注后了解更多精彩内容！！

本 文 原 创 来 源：IT那活儿微信公众号（上海新炬王翦团队）

评论

相关阅读

本文原创来源：IT那活儿微信公众号（上海新炬王翦团队）