概述Binlog2sql是一款由Python开发的开源MySQLBinlog分析工具，可以将Binlog解析为原始SQL，也支持将Binlog解析为回滚SQL，去除主键INSERTSQL，是的DBA、运维人员恢复数据的好帮手。一、安装配置1.1目的数据快速回滚（闪回）主从切换后新主丢失数据的修复从binlog生成标准SQL，自带的衍生函数支持MySQL5.6、5.71.2安装shell>gitclonehttps://github.com/danfengcao/binlog2sql.git&&cdbinlog2sqlshell>pipinstall-rrequirements.txt2.用法2.1使用前配置2.1.1参数配置[mysqld]server_id=1log_bin=/var/log/mysql/mysql-bin.logmax_binlog_size=1Gbinlog_format=rowbinlog_row_image=full2.1.2用户select、super/replicationclient、replicationslave需要的最小权限集合建议授权GRANTSELECT、REPLICATIONSLAVE、REPLICATIONCLIENTON*.*TO权限说明select：需要读取服务器端的information_schema.COLUMNS表获取表结构的元信息，拼接成可视化的sql语句super/replicationclient：两个权限都可以，需要执行'SHOWMASTERSTATUS'获取server端replicationslave上的binlog列表：通过BINLOG_DUMP协议获取binlog内容的权限2.2基本使用2.2.1基本使用分析OutofstandardSQLshell>pythonbinlog2sql.py-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttest3test4--start-file='mysql-bin.000002'输出：INSERTINTO`test`.`test3`(`addtime`,`data`,`id`)VALUES('2016-12-1013:03:38','english',4);#start570end736UPDATE`test`.`test3`SET`addtime`='2016-12-1012:00:00',`data`='中文',`id`=3WHERE`addtime`='2016-12-1013:03:22'AND`data`='中文'AND`id`=3LIMIT1;#start763end954DELETEFROM`test`.`test3`WHERE`addtime`='2016-12-1013:03:38'AND`data`='english'AND`id`=4LIMIT1;#start981end1147解析回滚SQLshell>pythonbinlog2sql.py--flashback-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttest3--start-file='mysql-bin.000002'--start-position=763--stop-position=1147输出：INSERTINTO`test`.`test3`(`addtime`,`data`,`id`)VALUES('2016-12-1013:03:38','english',4);#start981end1147UPDATE`test`.`test3`SET`addtime`='2016-12-1013:03:22',`data`='Chinese',`id`=3WHERE`addtime`='2016-12-1012:00:00'AND`data`='Chinese'AND`id`=3LIMIT1;#start763end9542.2.2optionmysql连接配置-hhost;-P端口；-你的用户；-p密码分析模式--stop-never是否连续解析binlog是可选的。默认为False，执行命令时同步到最新的binlog位置。-K,--no-primary-key删除INSERT语句的主键。选修的。默认False-B,--flashback生成回滚SQL，可以解析大文件，不受内存限制。选修的。默认值为假。不能与stop-never或no-primary-key一起添加。--back-interval-B模式下，每打印一千行回滚SQL就加一句SLEEP，如果不想加SLEEP，请设置为0。可选。默认值为1.0。解析范围控制--start-file开始解析文件，只需要文件名，不需要完整路径。必须。--start-position/--start-pos开始解析位置。选修的。默认为开始文件的起始位置。--stop-file/--end-file停止解析文件。选修的。默认为与起始文件相同的文件。如果解析模式为stop-never，则该选项无效。--stop-position/--end-pos结束解析位置。选修的。默认是停止文件的最后位置；如果解析模式为stop-never，则该选项无效。--start-datetime开始解析时间，格式'%Y-%m-%d%H:%M:%S'。选修的。默认不过滤。--stop-datetime停止解析时间，格式'%Y-%m-%d%H:%M:%S'。选修的。默认不过滤。对象过滤-d,--databases只解析目标db的sql，多个数据库之间用空格分隔，如-ddb1db2。选修的。默认为空。-t,--tables只解析目标表的sql，多个表用空格分隔，如-ttbl1tbl2。选修的。默认为空。--only-dml只解析dml，忽略ddl。选修的。默认值为假。--sql-type只解析指定类型，支持INSERT、UPDATE、DELETE。多个类型用空格分隔，如--sql-typeINSERTDELETE。选修的。默认情况下，添加、删除和修改都会被解析。如果使用了该参数但没有填写类型，则三个都不会被解析。2.3应用案例2.3.1误删除整表数据，需要紧急回滚flashback详见example目录的《闪回原理与实战》example/mysql-flashback-priciple-and-practice.mdtestlibrarytbltableoriginaldatamysql>选择*fromtbl;+----+--------+--------------------+|id|name|addtime|+----+--------+--------------------+|1|赵小|2016-12-1000:04:33||2|小倩|2016-12-1000:04:48||3|小孙|2016-12-1320:25:00||4|小李|2016-12-1200:00:00|+----+--------+--------------------+4rowsinset(0.00sec)mysql>deletefromtbl;QueryOK,4rowsaffected(0.00sec)20:28，tbl表被误操作清空mysql>select*fromtbl;Emptyset(0.00sec)恢复数据的步骤：1、登录mysql，查看当前binlog文件mysql>showmasterstatus;+------------------+------------+|Log_name|File_size|+---------------+----------+|mysql-bin.000051|967||mysql-bin.000052|965|+-----------------+----------+2。最新的binlog文件是mysql-bin.000052。我们来定位一下误操作SQL的binlog位置。误操作者只能知道误操作的大概时间，我们根据大概时间过滤数据。shell>pythonbinlog2sql/binlog2sql.py-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttbl--start-file='mysql-bin.000052'--start-datetime='2016-12-1320:25:00'--stop-datetime='2016-12-1320:30:00'输出：插入`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-1320:26:00',4,'小李');#start317end487time2016-12-1320:26:26UPDATE`test`.`tbl`SET`addtime`='2016-12-1200:00:00',`id`=4,`name`='小李'WHERE`addtime`='2016-12-1320:26:00'AND`id`=4AND`name`='小李'LIMIT1;#start514end701time2016-12-1320:27:07DELETEFROM`test`.`tbl`WHERE`addtime`='2016-12-1000:04:33'AND`id`=1AND`name`='小赵'LIMIT1;#start728end938time2016-12-1320:28:05DELETEFROM`test`.`tbl`WHERE`addtime`='2016-12-1000:04:48'AND`id`=2AND`name`='littlemoney'LIMIT1;#start728end938time2016-12-1320:28:05DELETEFROM`test`.`tbl`WHERE`addtime`='2016-12-1320:25:00'AND`id`=3AND`name`='小孙'LIMIT1;#start728end938time2016-12-1320:28:05DELETEFROM`test`.`tbl`WHERE`addtime`='2016-12-1200:00:00'AND`id`=4AND`name`='小李'LIMIT1;#start728end938time2016-12-1320:28:053。我们得到误操作sql的具体位置在728-938之间，然后根据位置进一步过滤，使用闪回方式生成回滚sql，检查回滚sql是否正确（注意：真环境，这一步会经常结合grep、editor等进一步过滤出需要的sql)shell>pythonbinlog2sql/binlog2sql.py-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttbl--start-file='mysql-bin.000052'--start-position=3346--stop-position=3556-B>rollback.sql|cat输出：INSERTINTO`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-1200:00:00',4,'小李');#start728end938time2016-12-1320:28:05INSERTINTO`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-1320:25:00',3,'小孙');#start728end938time2016-12-1320:28:05INSERTINTO`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-1000:04:48',2,'littlemoney');#start728end938time2016-12-1320:28:05INSERTINTO`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-1000:04:33',1,'小赵');#start728end938time2016-12-1320:28:054，确认回滚sql无误，执行backscroll语句。登录mysql，确认数据回滚成功。shell>mysql-h127.0.0.1-P3306-uadmin-p'admin'
