binlog_inspector

简介

binlog_inspector通过解释mysql/mariadb binlog/relaylog实现以下三大功能:
    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
        ```
    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',X'89504e47',1.1,1.1,1)
        commit
        ```
    3）统计分析， 统计各个表的DML情况， 找出大事务与长事务。   

*以上功能均可指定任意的单库多库， 单表多表， 任意时间点， 任意binlog位置。
*支持mysql5.5及以上，也支持mariadb的binlog， 支持传统复制的binlog， 也支持GTID的binlog。
*支持直接指定文件路径的binlog， 也支持主从复制， binlog_inspector作为从库从主库拉binlog来过解释。
*也支持目标binlog中包含了DDL(增加与减少表字段， 变化表字位置)的场景。

限制

*binlog格式必须为row,且binlog_row_image=full
*只能回滚DML， 不能回滚DDL
*支持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的解释。
    解释binlog的开始位置：
        --start-binlog=mysql-bin.000101
        --start-pos=4
    解释binlog的结束位置：
        --stop-binlog=mysql-bin.000105
        --stop-pos=4
    解释binlog的开始时间    
        --start-datetime="2018-04-21 00:00:00"
    解释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只包含最少必须的字段, 前提下是表含有唯一索引
    --min-columns
    ```sql
    UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
    DELETE FROM `binlog_inspector` WHERE `id`=5;
    ```
    否则为
    ```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）支持大insert拆分成小insert语句。
    --insert-rows=100
    对于一个insert 1000行的插入， 会生成10个insert语句，每个语句插入100行
13）支持目标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',X'e58d8ee58d97e79086e5b7a5e5a4a7e5ada62653435554',1.100000023841858,1.1,1,X'89504e47');
   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,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）使用
    *生成前滚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
    *生成回滚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
    *只生成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

感谢

   感谢https://github.com/siddontang的binlog解释库， 感谢dropbox的sqlbuilder库， 没有他们的库就没有binlog_inspector. 自2017年10月在唯品会DBA内部使用至今， 暂没发现有重大的bug, 有任何的bug或者使用反馈， 欢迎联系laijunshou@gmail.com.