MySQL 解析binlog生成标准SQL工具之my2sql

怼烎@ 2024-03-27 04:37 65阅读 0赞

my2sql  
简介

go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。https://github.com/liuhr/my2sql  
安装

yum install -y go

编译

git clone https://github.com/liuhr/my2sql.git  
cd my2sql/  
go build .

也可以直接下载Linux版编译好的可执行文件  
https://github.com/liuhr/my2sql/blob/master/releases/my2sql  
应用案例

解析mysql binlog生成标准SQL语句

mysql> \#数据库  
mysql> show create database testdb\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Database: testdb  
Create Database: CREATE DATABASE \`testdb\` /\*!40100 DEFAULT CHARACTER SET utf8mb4 \*/

mysql> \#表结构  
mysql> show create table student\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Table: student  
Create Table: CREATE TABLE \`student\` (  
\`id\` int(11) NOT NULL AUTO\_INCREMENT,  
\`number\` int(11) NOT NULL,  
\`name\` varchar(255) DEFAULT NULL,  
\`add\_time\` timestamp NULL DEFAULT CURRENT\_TIMESTAMP COMMENT '添加的时间',  
\`content\` json DEFAULT NULL,  
PRIMARY KEY (\`id\`),  
UNIQUE KEY \`idx\_name\` (\`number\`,\`name\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=1234 DEFAULT CHARSET=utf8

\#查询数据表结构  
mysql> select \* from student;  
Empty set (0.00 sec)

\#重新生成要给binlog  
mysql> flush logs;  
Query OK, 0 rows affected (0.05 sec)

\#插入一些数据  
mysql> INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);  
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);  
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);  
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);  
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','\{\\"age\\":13,\\"author\\":\\"liuhan\\"\}');

查看binlog文件

根据操作时间解析binlog，生成标准SQL

./my2sql -user root -password 123456 -port 3306 \\  
\-host 127.0.0.1 -databases testdb -tables student \\  
\-work-type 2sql -start-file mysql-bin.000047 \\  
\-start-datetime "2020-07-18 12:35:00" --stop-datetime "2020-07-18 12:43:00" \\  
\-output-dir tmpdir/

查看生成的标准SQL

cd tmpdir/  
ls  
biglong\_trx.txt binlog\_status.txt forward.47.sql

\#查看DML信息  
cat binlog\_status.txt  
binlog starttime stoptime startpos stoppos inserts updates deletes database table  
mysql-bin.000047 2020-07-18\_12:41:19 2020-07-18\_12:41:19 301 1630 5 0 0 testdb student

cat forward.47.sql  
INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);  
INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);  
INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);  
INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);  
INSERT INTO \`testdb\`.\`student\` (\`id\`,\`number\`,\`name\`,\`add\_time\`,\`content\`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','\{\\"age\\":13,\\"author\\":\\"liuhan\\"\}');

根据POS点解析binlog，生成标准SQL  
也可以根据binlog的pos点解析，这里不在展示  
执行命令：

./my2sql -user root -password 123456 -port 3306 \\  
\-host 127.0.0.1 -databases testdb -tables student \\  
\-work-type rollback -start-file mysql-bin.000047 \\  
\-start-pos 4 -stop-file mysql-bin.000047 -stop-pos 1661 \\  
\-output-dir tmpdir/

总结  
限制

使用回滚/闪回功能时，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权限

优点

功能丰富，不仅支持回滚操作，还有其他实用功能。请参考之前博文  
基于golang实现，速度快，全量解析1.1Gbinlog只需要1分30秒左右，当前其他类似开源工具一般要几十分钟  
github地址 https://github.com/liuhr/my2sql

my2sql功能介绍相关博文

MySQL binlog回滚/闪回、前滚、DML统计、长事务与大事务分析  
https://blog.csdn.net/liuhanran/article/details/107425016

MySQL闪回工具之my2sql  
https://blog.csdn.net/liuhanran/article/details/107426162

MySQL 解析binlog 统计DML、长事务与大事务分析工具之my2sql  
https://blog.csdn.net/liuhanran/article/details/107427391