my2sql —— go语言版binlog解析及闪回工具

        之前学习过python语言版binlog解析及闪回工具 MySQL闪回工具简介 及 binlog2sql工具用法 最近听同事介绍有了新的go语言版的my2sql。优点是不需要安装一大堆依赖包，直接可以安装使用，并且解析更高效，试用一下。

一、 下载安装

1. 软件下载

GitHub - liuhr/my2sql: 解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息以及大事务分析信息。

2. 安装

unzip my2sql-master.zip
cd my2sql-master
go build .

安装完后可以看到my2sql命令

3. 使用要求

• 使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响
• 只能回滚DML， 不能回滚DDL
• 使用rollback功能时，要解析的binlog段，表结构要保持一致（例如：解析mysql-bin.000001文件，此binlog文件的的表有add column或drop column操作，则执行rollback可能会执行异常）
• 支持指定-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权限
• MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析

二、 使用测试

1. 执行测试语句

mysql> create table t1(a int);
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into t1 values(1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)
 
mysql> insert into t1 select * from t1;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> 
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
8 rows in set (0.00 sec)
 
-- 切换日志，方便后续解析操作
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
 
mysql> delete from t1;
Query OK, 8 rows affected (0.01 sec)
 
mysql> select * from t1;
Empty set (0.00 sec)
 
-- 查看当前日志
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000009 |      468 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2. 闪回功能，生成回滚语句

      最重要的参数是  -work-type rollback，其余指定解析哪些日志、哪段时间、哪个表、输出位置

cd my2sql-master
 
# 注意 -stop-file binlog.000009，解析时不会包含日志9
./my2sql -user root -password xxxx -port 3306 -host 127.0.0.1 -databases sbtest -tables t1 -work-type rollback -start-file binlog.000008 -stop-file binlog.000009 -start-datetime "2024-03-20 16:00:00" --stop-datetime "2024-03-20 17:00:00" -output-dir /tmp

[root@linux01 ~]# cd my2sql-master
[root@linux01 my2sql-master]# ./my2sql  -user root -password xxxx -port 3306 -host 127.0.0.1 -databases sbtest  -tables t1 -work-type rollback   -start-file binlog.000008 -stop-file binlog.000009 -start-datetime "2024-03-20 16:00:00" --stop-datetime "2024-03-20 17:00:00" -output-dir /tmp
[2024/03/20 16:30:24] [info] binlogsyncer.go:164 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 <nil> 0xc0000680c0 0x634d20}
[2024/03/20 16:30:24] [info] binlogsyncer.go:400 begin to sync binlog from position (binlog.000008, 4)
[2024/03/20 16:30:24] [info] events.go:221 start thread to write redo/rollback sql into file
[2024/03/20 16:30:24] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2024/03/20 16:30:24] [info] events.go:61 start thread 1 to generate redo/rollback sql
[2024/03/20 16:30:24] [info] events.go:61 start thread 2 to generate redo/rollback sql
[2024/03/20 16:30:24] [info] repl.go:16 start to get binlog from mysql
[2024/03/20 16:30:24] [info] binlogsyncer.go:816 rotate to (binlog.000008, 4)
[2024/03/20 16:30:24] [info] binlogsyncer.go:816 rotate to (binlog.000009, 4)
[2024/03/20 16:30:24] [info] binlogsyncer.go:816 rotate to (binlog.000009, 4)
[2024/03/20 16:30:24] [info] com.go:58 stop to get event. StopFilePos set. currentBinlog (binlog.000009, 125) StopFilePos (binlog.000009, 4)
[2024/03/20 16:30:24] [info] repl.go:18 finish getting binlog from mysql
[2024/03/20 16:30:24] [info] events.go:196 exit thread 1 to generate redo/rollback sql
[2024/03/20 16:30:24] [info] events.go:196 exit thread 2 to generate redo/rollback sql
[2024/03/20 16:30:24] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2024/03/20 16:30:24] [info] events.go:270 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2024/03/20 16:30:24] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2024/03/20 16:30:24] [info] rollback_process.go:41 start to revert tmp file /tmp/.rollback.8.sql into /tmp/rollback.8.sql
[2024/03/20 16:30:24] [info] rollback_process.go:156 finish reverting tmp file /tmp/.rollback.8.sql into /tmp/rollback.8.sql
[2024/03/20 16:30:24] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2024/03/20 16:30:24] [info] events.go:283 finish reverting content order of tmp files
[2024/03/20 16:30:24] [info] events.go:288 exit thread to write redo/rollback sql into file

/tmp目录中可以看到解析后的日志

        rollback.8.sql 表示解析binlog 8后生成的回滚语句，因为binlog 8时执行的是insert，这里生成的应该是delete语句。

3. 解析binlog，查看历史执行语句

最重要的参数为 -work-type 2sql，其他跟前面是一样的

cd my2sql-master
 
# 注意 -stop-file binlog.000009，解析时不会包含日志9
./my2sql -user root -password xxxx -port 3306 -host 127.0.0.1 -databases sbtest -tables t1 -work-type 2sql -start-file binlog.000008 -stop-file binlog.000009 -start-datetime "2024-03-20 16:00:00" --stop-datetime "2024-03-20 17:00:00" -output-dir /tmp

./my2sql  -user root -password xxxx -port 3306 -host 127.0.0.1 -databases sbtest  -tables t1 -work-type 2sql -start-file binlog.000008 -stop-file binlog.000009 -start-datetime "2024-03-20 16:00:00" --stop-datetime "2024-03-20 17:00:00" -output-dir /tmp
[2024/03/20 16:56:28] [info] binlogsyncer.go:164 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 <nil> 0xc00009c660 0x634d20}
[2024/03/20 16:56:28] [info] binlogsyncer.go:400 begin to sync binlog from position (binlog.000008, 4)
[2024/03/20 16:56:28] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2024/03/20 16:56:28] [info] events.go:221 start thread to write redo/rollback sql into file
[2024/03/20 16:56:28] [info] events.go:61 start thread 1 to generate redo/rollback sql
[2024/03/20 16:56:28] [info] events.go:61 start thread 2 to generate redo/rollback sql
[2024/03/20 16:56:28] [info] repl.go:16 start to get binlog from mysql
[2024/03/20 16:56:28] [info] binlogsyncer.go:816 rotate to (binlog.000008, 4)
[2024/03/20 16:56:28] [info] binlogsyncer.go:816 rotate to (binlog.000009, 4)
[2024/03/20 16:56:28] [info] binlogsyncer.go:816 rotate to (binlog.000009, 4)
[2024/03/20 16:56:28] [info] com.go:58 stop to get event. StopFilePos set. currentBinlog (binlog.000009, 125) StopFilePos (binlog.000009, 4)
[2024/03/20 16:56:28] [info] repl.go:18 finish getting binlog from mysql
[2024/03/20 16:56:28] [info] events.go:196 exit thread 1 to generate redo/rollback sql
[2024/03/20 16:56:28] [info] events.go:196 exit thread 2 to generate redo/rollback sql
[2024/03/20 16:56:28] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2024/03/20 16:56:28] [info] events.go:285 finish writing redo/forward sql into file
[2024/03/20 16:56:28] [info] events.go:288 exit thread to write redo/rollback sql into file

4. DML统计与长事务分析

之前造的数据量太少，我们再插入一些数据

       最重要的参数为 -work-type stats，-big-trx-row-limit 和 -long-trx-seconds 分别指定影响多少行、多长时间的算长事务。

# 变化500行以上、执行3秒以上算长事务
./my2sql  -user root -password  xxx -port 3306 -host 127.0.0.1 -databases sbtest  -tables t1 -big-trx-row-limit 500 -long-trx-seconds 3 -work-type stats -start-file binlog.000008 -start-datetime "2024-03-20 16:00:00" -output-dir /tmp

./my2sql  -user root -password  xxx -port 3306 -host 127.0.0.1 -databases sbtest  -tables t1 -big-trx-row-limit 500 -long-trx-seconds 3 -work-type stats -start-file binlog.000008 -start-datetime "2024-03-20 16:00:00" -output-dir /tmp

[2024/03/20 17:26:44] [info] binlogsyncer.go:164 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 <nil> 0xc000086120 0x634d20}
[2024/03/20 17:26:44] [info] binlogsyncer.go:400 begin to sync binlog from position (binlog.000008, 4)
[2024/03/20 17:26:44] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2024/03/20 17:26:44] [info] repl.go:16 start to get binlog from mysql
[2024/03/20 17:26:44] [info] binlogsyncer.go:816 rotate to (binlog.000008, 4)
[2024/03/20 17:26:44] [info] binlogsyncer.go:816 rotate to (binlog.000009, 4)
[2024/03/20 17:26:44] [info] binlogsyncer.go:816 rotate to (binlog.000009, 4)
[2024/03/20 17:26:50] [info] repl.go:84 deadline exceeded.
[2024/03/20 17:26:50] [info] repl.go:18 finish getting binlog from mysql
[2024/03/20 17:26:50] [info] stats_process.go:266 exit thread to analyze statistics from binlog

参考

GitHub - liuhr/my2sql: 解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息以及大事务分析信息。

MySQL闪回工具之my2sql_共 my2sql 下载-CSDN博客

MySQL 解析binlog生成标准SQL工具之my2sql_binlog转sql工具-CSDN博客

MySQL 解析binlog 统计DML、长事务与大事务分析工具之my2sql_my2sql 解析大事物-CSDN博客