资讯专栏INFORMATION COLUMN

上云采购季！| 爆款云服务器低至4.6元/月，首单享0.9折

免费试用
首页/文章专栏/MySQL binlog日志解析工具--binlog_inspector

MySQL binlog日志解析工具--binlog_inspector

IT那活儿 / 2453人阅读
MySQL binlog日志解析工具--binlog_inspector

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


一、简介
1. binlog_inspector通过解释mysql/mariadb binlog/relaylog实现以下三大功能:
1.1 flashback/闪回/回滚， 实现DML的回滚到任意时间或者位置。
生成的SQL形式如下：
```sql
begin
DELETE 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=575
commit
```
1.2 前滚，把binlog/relaylog的DML解释成易读的SQL语句。
生成的SQL形式如下：
```sql
begin
# datetime=2017-10-23_00:14:28 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=417 stoppos=575
INSERT INTO `binlog_inspector`.`emp` (`id`,`name`,`sr`,`icon`,`points`,`sa`,`sex`) VALUES (1,张三1,华南理工大学&SCUT,X89504e47,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
```sql
begin
# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `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=23930
UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;
commit
```
否则为：
```sql
begin
# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `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=23930
UPDATE .`emp` SET `name`=null WHERE `id`=5;
commit
```
9. 输出结果支持是否保留事务
--keep-trx
```sql
begin
# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `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=23930
UPDATE `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=22822
UPDATE `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=23930
UPDATE `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=22822
UPDATE `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=23930
UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;
```


否则为：


```sql
UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;
```
11. 支持生成的SQL只包含最少必须的字段, 前提下是表含有唯一索引
默认为：
```sql
UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
DELETE FROM `binlog_inspector` WHERE `id`=5;
```
--full-columns 则为
```sql
UPDATE `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如下：
```sql
create 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  first
truncate table emp
alter table emp add primary key (id)
alter table emp modify id int auto_increment
alter TABLE emp add column updatetime datetime comment 更新时间, add createtime timestamp default current_timestamp comment 创建时间
alter TABLE emp drop column updatetime
```
但binlog_inspector这时获取到的表结构表结构如下：
```sql
CREATE 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
```
不清楚之前的表结构， 就会出现错乱:
```sql
begin;
# datetime=2018-02-05_10:12:41 database=binlog_inspector table=emp binlog=mysql-bin.000001 startpos=1614 stoppos=1772
INSERT INTO `binlog_inspector`.`emp` (`id`,`name`,`sr`,`points`,`sa`,`sex`) VALUES (张三1,Xe58d8ee58d97e79086e5b7a5e5a4a7e5ada62653435554,1.100000023841858,1.1,1,X89504e47);
commit;
```
binlog_inspector会输出所有DDL的语句到ddl_info.log这个文件， 有时间与位置信息，如:
```sql
datetime binlog startpos stoppos sql
2018-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  first
2018-02-05_10:16:41 mysql-bin.000001  8759       8856       truncate table emp
2018-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_increment
2018-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无误了。
```sql
begin;
# datetime=2018-02-05_10:12:41 database=binlog_inspector table=emp binlog=mysql-bin.000001 startpos=1614 stoppos=1772
INSERT INTO `binlog_inspector`.`emp` (`name`,`sr`,`points`,`sa`,`sex`,`icon`) VALUES (张三1,**理工大学&SCUT,1.100000023841858,1.1,1,X89504e47);
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那活儿微信公众号（上海新炬王翦团队）


云服务器 海外云主机 binlog mysql mysql binlog恢复 mysql_binlog 解析日志文件

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129710.html

相关文章

  • [Spring cloud 一步步实现广告系统] 15. 使用开源组件监听Binlog 实现增量索引

    摘要：不会记录数据表的列名在接下来的实现中，我们会将自己的系统包装成一个假的，通过开源工具来实现监听。因为我们只需要中的内容，那么我们也就只需要通过实现接口，来自定义一个监听器实现我们的业务即可。 MySQL Binlog简介 什么是binlog? 一个二进制日志，用来记录对数据发生或潜在发生更改的SQL语句，并以而进行的形式保存在磁盘中。 binlog 的作用？ 最主要有3个用途： ...

    darryrzhong 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<