之前学习过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博客
扫一扫
1576
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
