简介：
go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。
GitHub地址：https://github.com/liuhr/my2sql
my2sql有三个主要功能，今天只介绍闪回功能:
代码语言：javascript复制#执行闪回操作具体操作流程
#解析binlog生成标准SQL
#解析binlog 统计DML、长事务与大事务分析1.直接从从GitHub下载源码包
2.传到本地服务器解压
代码语言：javascript复制[root@localhost opt]# unzip my2sql-master.zip3.解压后直接cd进入编译好的路径下：（此处不用编译，作者已经把编译好的包放在releases/centOS_release_7.x
下）
代码语言：javascript复制[root@localhost centOS_release_7.x]# pwd
/opt/my2sql-master/releases/centOS_release_7.x也可以在GitHub上直接下载编译好的，GitHub上有下载链接
参数说明
-U
代码语言：javascript复制优先使用unique key作为where条件，默认false-mode
代码语言：javascript复制repl: 伪装成从库解析binlog文件，file: 离线解析binlog文件, 默认repl-local-binlog-file
代码语言：javascript复制当指定-mode=file 参数时，需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以连续解析多个binlog文件，只需要指定起始文件名，程序会自动持续解析下个文件-add-extraInfo
代码语言：javascript复制是否把database/table/datetime/binlogposition...信息以注释的方式加入生成的每条sql前，默认false# datetime=2020-07-16_10:44:09 database=orchestrator table=cluster_domain_name binlog=mysql-bin.011519 startpos=15552 stoppos=15773
UPDATE `orchestrator`.`cluster_domain_name` SET `last_registered`='2020-07-16 10:44:09' WHERE `cluster_name`='192.168.1.1:3306'-big-trx-row-limit n
代码语言：javascript复制transaction with affected rows greater or equal to this value is considerated as big transaction
找出满足n条sql的事务，默认500条-databases 、 -tables
代码语言：javascript复制库及表条件过滤, 以逗号分隔-sql
代码语言：javascript复制要解析的sql类型，可选参数insert、update、delete，默认全部解析-doNotAddPrifixDb
代码语言：javascript复制Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1)
默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql，也可以生成不带库名的sql-file-per-table
代码语言：javascript复制为每个表生成一个sql文件-full-columns
代码语言：javascript复制For update sql, include unchanged columns. for update and delete, use all columns to build where condition.
default false, this is, use changed columns to build set part, use primary/unique key to build where condition
生成的sql是否带全列信息，默认false-ignorePrimaryKeyForInsert
代码语言：javascript复制生成的insert语句是否去掉主键，默认false-output-dir
代码语言：javascript复制将生成的结果存放到制定目录-output-toScreen
代码语言：javascript复制将生成的结果打印到屏幕，默认写到文件-threads
代码语言：javascript复制线程数，默认8个-work-type
代码语言：javascript复制2sql：生成原始sql，rollback：生成回滚sql，stats：只统计DML、事务信息4.模拟表数据被删除，进行恢复
代码语言：javascript复制mysql> show create table sbtest1;
--------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
--------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`),
KEY `c_inx` (`c`),
KEY `cc` (`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=latin1 |
--------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)查询一下数据量
代码语言：javascript复制mysql> select count(*) from thn.sbtest1;
----------
| count(*) |
----------
| 500000 |
----------
1 row in set (0.05 sec)
#为了模拟方便，重新生成一个binlog
mysql> flush logs;删除表数据
代码语言：javascript复制mysql> delete from sbtest1 where id<400000;
Query OK, 399999 rows affected (7.41 sec)解析一下binlog，因为是测试，并没有其他热写入数据，我按时间区间去闪回
代码语言：javascript复制[root@localhost data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-binlog.000017 |more
#210818 19:37:08 server id 5 end_log_pos 386 CRC32 0xcf72d3af Table_map: `thn`.`sbtest1` mapped to number 108
# at 386
#210818 19:37:08 server id 5 end_log_pos 8548 CRC32 0x031211c6 Delete_rows: table id 108生成回滚SQL
代码语言：javascript复制[root@localhost centOS_release_7.x]# pwd
/opt/my2sql-master/releases/centOS_release_7.x
[root@localhost centOS_release_7.x]# ./my2sql -user root -password mysql -port 3306 -host 127.0.0.1 -databases thn -tables sbtest1 -work-type rollback -start-file mysql-binlog.000017 -start-datetime "2021-08-19 00:00:00" --stop-datetime "2021-09-23 12:00:00" --add-extraInfo -output-dir /tmp
[2021/08/18 19:41:29] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/08/18 19:41:29] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-binlog.000017, 4)
[2021/08/18 19:41:29] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/08/18 19:41:29] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/08/18 19:41:29] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/08/18 19:41:29] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/08/18 19:41:29] [info] repl.go:16 start to get binlog from mysql
[2021/08/18 19:41:29] [info] binlogsyncer.go:777 rotate to (mysql-binlog.000017, 4)
[2021/08/18 19:41:35] [info] repl.go:84 deadline exceeded.
[2021/08/18 19:41:35] [info] repl.go:18 finish getting binlog from mysql
[2021/08/18 19:41:35] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/08/18 19:41:35] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/08/18 19:41:35] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/08/18 19:41:35] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/08/18 19:41:35] [info] events.go:272 finish reverting content order of tmp files
[2021/08/18 19:41:35] [info] events.go:277 exit thread to write redo/rollback sql into file
[root@localhost centOS_release_7.x]# ./my2sql -user root -password mysql -port 3306 -host 127.0.0.1 -databases thn -tables sbtest1 -work-type rollback -start-file mysql-binlog.000017 -start-datetime "2021-08-17 00:00:00" --stop-datetime "2021-08-20 12:00:00" --add-extraInfo -output-dir /tmp
[2021/08/18 19:48:13] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/08/18 19:48:13] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-binlog.000017, 4)
[2021/08/18 19:48:13] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/08/18 19:48:13] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/08/18 19:48:13] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/08/18 19:48:13] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/08/18 19:48:13] [info] repl.go:16 start to get binlog from mysql
[2021/08/18 19:48:13] [info] binlogsyncer.go:777 rotate to (mysql-binlog.000017, 4)
[2021/08/18 19:48:14] [info] events.go:244 finish processing mysql-binlog.000017 10488556
[2021/08/18 19:48:15] [info] events.go:244 finish processing mysql-binlog.000017 20976726
[2021/08/18 19:48:15] [info] events.go:244 finish processing mysql-binlog.000017 31464896
[2021/08/18 19:48:15] [info] events.go:244 finish processing mysql-binlog.000017 41953066
[2021/08/18 19:48:16] [info] events.go:244 finish processing mysql-binlog.000017 52441236
[2021/08/18 19:48:16] [info] events.go:244 finish processing mysql-binlog.000017 62929406
[2021/08/18 19:48:16] [info] events.go:244 finish processing mysql-binlog.000017 73417576
[2021/08/18 19:48:21] [info] repl.go:84 deadline exceeded.
[2021/08/18 19:48:21] [info] repl.go:18 finish getting binlog from mysql
[2021/08/18 19:48:21] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/08/18 19:48:21] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/08/18 19:48:21] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/08/18 19:48:21] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/08/18 19:48:21] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/08/18 19:48:21] [info] rollback_process.go:41 start to revert tmp file /tmp/.rollback.17.sql into /tmp/rollback.17.sql
[2021/08/18 19:48:22] [info] rollback_process.go:156 finish reverting tmp file /tmp/.rollback.17.sql into /tmp/rollback.17.sql
[2021/08/18 19:48:22] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/08/18 19:48:22] [info] events.go:272 finish reverting content order of tmp files
[2021/08/18 19:48:22] [info] events.go:277 exit thread to write redo/rollback sql into file
#看到sql into file这就已经成功搞定了查看回滚的SQL
代码语言：javascript复制[root@localhost data]# cd /tmp/
[root@localhost tmp]# ll
-rw-r--r--. 1 root root 261 8月 18 19:48 biglong_trx.txt
-rw-r--r--. 1 root root 290 8月 18 19:48 binlog_status.txt
-rw-r--r--. 1 root root 103668233 8月 18 19:48 rollback.17.sql查看DML信息
代码语言：javascript复制[root@localhost tmp]# [root@localhost tmp]# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-binlog.000017 2021-08-18_19:37:08 2021-08-18_19:37:08 330 75925802 0 0 399999 thn sbtest1
## 查看生成的binlog_status.txt文件，会统计每个时间段（POS）区间内相关库表所产生的DML次数
## biglong_trx.txt文件记录的是大/长事务，此文件为空，说明没有大/长事务
## 由文件中记录的DML总执行次数可知，确实是删除了399999 条记录查看回滚SQL形式
代码语言：javascript复制[root@localhost tmp]# more rollback.17.sql
INSERT INTO `thn`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (399999,48268,'95132265201-45028665901-44037695346-99430732771-
94049966285-01025661979-46614439842-38764489961-91197548642-34427705307','54658966786-08986899978-98420410571-388688724
36-65095829410');
INSERT INTO `thn`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (399998,295137,'13049311991-28212069454-50756206952-46824748230
-09868329157-60277837353-92033860701-98090420440-28261455360-36687743868','97210253317-61299060306-09164181955-97529490
798-90188641840');恢复数据：
代码语言：javascript复制[root@localhost tmp]# mysql -u root -pmysql -P3306 -h127.0.0.1 thn < /tmp/rollback.17.sql 恢复成功后查询一下数据总量，数据已经完全恢复回来了
代码语言：javascript复制mysql> select count(*) from thn.sbtest1;
----------
| count(*) |
----------
| 500000 |
----------
1 row in set (0.05 sec)根据POS点解析binlog，生成回滚日志
也可以根据binlog的pos点解析，这里不再展示
命令：
代码语言：javascript复制./my2sql -user root -password mysql -port 3306 -host 127.0.0.1 -databases thn -tables student -work-type rollback -start-file mysql-binlog.000017 -start-pos 100 -stop-file mysql-binlog.000017 -stop-pos 1000 -output-dir /tmp限制
使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响
只能回滚DML， 不能回滚DDL
支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-start-datetime与结束时间-stop-datetime也会使用此指定的时区， 但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp
此工具是伪装成从库拉取binlog，需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限
案例
解析出回滚SQL
根据时间点解析出回滚SQL
代码语言：javascript复制#伪装成从库解析binlog
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode repl -work-type rollback -start-file mysql-binlog.000017 -start-datetime "2021-08-19 00:00:00" -stop-datetime "2021-08-20 12:00:00" -output-dir /tmp
#直接读取binlog文件解析
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode file -local-binlog-file /data/mysql/data/mysql-binlog.000017 -work-type rollback -start-file mysql-binlog.000017 -start-datetime "2021-08-19 00:00:00" -stop-datetime "2021-08-20 12:00:00" -output-dir /tmp根据pos点解析出回滚SQL
代码语言：javascript复制#伪装成从库解析binlog
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode repl -work-type rollback -start-file mysql-binlog.000017 -start-pos 4 -stop-file mysql-binlog.000017 -stop-pos 583918266 -output-dir /tmp
#直接读取binlog文件解析
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-binlog.000017 -work-type rollback -start-file mysql-binlog.000017 -start-pos 4 -stop-file mysql-binlog.000017 -stop-pos 583918266 -output-dir /tmp统计DML以及大事务
统计时间范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
代码语言：javascript复制#伪装成从库解析binlog
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode repl -work-type stats -start-file mysql-binlog.000017 -start-datetime "2021-08-19 00:00:00" -stop-datetime "2021-08-20 12:00:00" -big-trx-row-limit 500 -long-trx-seconds 300 -output-dir /tmp
#直接读取binlog文件解析
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-binlog.000017 -work-type stats -start-file mysql-binlog.000017 -start-datetime "2021-08-19 00:00:00" -stop-datetime "2021-08-20 12:00:00" -big-trx-row-limit 500 -long-trx-seconds 300 -output-dir /tmp统计一段pos点范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务
代码语言：javascript复制#伪装成从库解析binlog
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode repl -work-type stats -start-file mysql-binlog.000017 -start-pos 4 -stop-file mysql-binlog.000017 -stop-pos 583918266 -big-trx-row-limit 500 -long-trx-seconds 300 -output-dir /tmp
#直接读取binlog文件解析
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode file -local-binlog-file ./mysql-binlog.000017 -work-type stats -start-file mysql-binlog.000017 -start-pos 4 -stop-file mysql-binlog.000017 -stop-pos 583918266 -big-trx-row-limit 500 -long-trx-seconds 300 -output-dir /tmp从某一个pos点解析出标准SQL，并且持续打印到屏幕
代码语言：javascript复制#伪装成从库解析binlog
./my2sql -user root -password mysql -host 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file mysql-binlog.000017 -start-pos 4 -output-toScreen有兴趣的可以到GitHub上系统的研究一下my2sql，很实用的一个工具
抛半砖以求全玉，班门弄斧，不胜惶恐！
