【MySQL】开源数据闪回工具—binlog2sql介绍

​一、binlog2sql介绍

binlog2sql是国内MySQL大佬danfengcao使用python开发，许多MySQL爱好者参与改进的一款MySQL binlog解析软件。根据不同选项，可以得到原始SQL、回滚SQL、去除主键的SQL等。

github地址为：https://github.com/danfengcao/binlog2sql

该工具主要用于：

• 数据快速回滚（闪回）；

• 从binlog生成标准SQL；

适用MySQL版本：MySQL5.6 、MySQL5.7

 

二、安装binlog2sql

# 安装gitshell> yum install -y git​# 安装pip工具shell> yum install -y epel-releaseshell> yum install -y python-pip​# 安装binlog2sqlshell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sqlshell> pip install -r requirements.txt

 

三、使用前提

基于最小侵入原则，我们一般在管理节点执行操作，只需把binlog2sql安装在管理节点机器即可。

要使用binlog2sql，MySQL服务器需要设置以下参数：

[mysqld]server_id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = full

四、使用方式

需要创建一个用户并授权

用户：binlog2sql

管理节点IP：192.168.1.1

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'binlog2sql'@'192.168.1.1' identified by 'xxxxx';

例如：

python binlog2sql.py -h ip  -ubinlog2sql -pxxxx -P3306 -d db -t t1 t2 --sql-type='delete' --start-file='mysql-bin.000104' --start-datetime='2020-07-16 16:46:57' --stop-datetime='2020-07-16 17:00:29' -B > rollback.sql

 

五、参数说明：

[root@localhost binlog2sql]# python binlog2sql.py --helpusage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]]                     [-P PORT] [--start-file START_FILE]                     [--start-position START_POS] [--stop-file END_FILE]                     [--stop-position END_POS] [--start-datetime START_TIME]                     [--stop-datetime STOP_TIME] [--stop-never] [--help]                     [-d [DATABASES [DATABASES ...]]]                     [-t [TABLES [TABLES ...]]] [--only-dml]                     [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]                     [--back-interval BACK_INTERVAL]

 

MySQL链接配置参数-h host; –p password; –u user ; -P port​解析模式参数：--stop-never : 持续解析binlog，同步至执行命令时最新的binlog位置，默认false。-K , --no-primary-key ：对insert语句去除主键，默认false。-B , --flashback : 生成回滚SQL，可解析大文件，不受内存限制，默认false。与stop-never或no-primary-key不能同时添加。--back-interval :在-B模式下，每打印1000条回滚SQL，SLEEP多少秒，默认为1。​范围控制参数：--start-file ：起始解析文件，只需文件名，无需全路径。必需参数。--stop-file / --end-file : 终止解析文件，默认为start-file同一个文件，若解析模式为stop-never，则该参数失效。--start-position / --start-pos : 起始解析位置，默认start-file文件起始位置。--stop-position / --stop-pos : 终止解析位置，默认stop-file终点位置。--start-datetime ：起始解析时间，格式为’%Y-%m-%d %H:%M:%S’。--stop-datetime ：终止解析时间，格式为’%Y-%m-%d %H:%M:%S’​对象过滤参数：-d , --databases : 只解析目标DB的sql，多个数据库用空格隔开，如：-d db1 db2。-t  , --tables : 只解析目标表的sql，多张表用空格隔开，如：-t table1 table2。--only-dml ：只解析DML，忽略DDL。--sql-type : 只解析指定类型，支持INSERT,UPDATE,DELETE。多个类型用空格隔开，如--sql-type INSERT UPDATE，默认全解析。

 

六、案例

经常有研发上线没经过测试，或者测试不规范，造成线上多余数据被误删除或更改，如下是一个典型案例：

如果没有备份，只能通过解析binlog恢复，另外如果binlog还不是row模式，那估计真的要丢失了。

如果没有工具，只能通的mysqlbinlog命令，解析出来的结果中含有大量无关的信息，只有当时操作的记录，还需要反向拼接，对排查的增加了难度。

市面上也有一些其他开源工具，原理都差不多，但使用效率上binlog2sql更胜一筹，通过binlog2sql可以很容易搞定：

python binlog2sql.py -h ip          #指定目标ip，建议从库-u binlog2sql  #指定数据库用户 -p xxxx        #指定密码-P 3306        #指定数据库端口 -d db          #指定库名 -t t1 t2       #需要恢复的表，多个用空格分隔--sql-type='delete'                      #需要恢复的类型--start-file='mysql-bin.000104'          #指定binlog文件 --start-datetime='2020-07-16 16:46:57'   #开始时间 --stop-datetime='2020-07-16 17:00:29'    #结束时间 -B                                       #生成反向拼接sql> rollback.sql                           #追加到文件

拿到sql文件后不要直接恢复，一定让研发确认下没问题后在恢复，恢复前对数据进行下备份。养成备份的好习惯，千万不要偷懒。