binlog 实用解析工具 my2sql

my2sql

go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等，本工具基于my2fback、binlog_rollback工具二次开发而来。

用途

数据快速回滚(闪回)
主从切换后新master丢数据的修复
从binlog生成标准SQL，带来的衍生功能
生成DML统计信息，可以找到哪些表更新的比较频繁
IO高TPS高，查出哪些表在频繁更新
找出某个时间点数据库是否有大事务或者长事务
主从延迟，分析主库执行的SQL语句
除了支持常规数据类型，对大部分工具不支持的数据类型做了支持，比如json、blob、text、emoji等数据类型sql生成

产品性能对比

binlog2sql当前是业界使用最广泛的MySQL回滚工具，下面对my2sql和binlog2sql做个性能对比。

	my2sql	binlog2sql
1.1G binlog生成回滚SQL	1分40秒	65分钟
1.1G binlog生成原始SQL	1分30秒	50分钟
1.1G binlog生成表DML统计信息、以及事务统计信息	40秒	不支持

快速开始

执行闪回操作具体操作流程

安装
编译

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

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

应用案例
误删整张表数据，需要紧急回滚

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> #数据
mysql> select * from student;
+------+--------+---------+---------------------+---------------------------------+
| id | number | name | add_time | content |
+------+--------+---------+---------------------+---------------------------------+
| 12 | 12 | che | 2020-07-06 19:39:17 | NULL |
| 1217 | 12 | hanraan | 2020-07-06 19:39:17 | NULL |
| 1218 | 12 | NULL | 2020-07-06 19:39:17 | NULL |
| 1219 | 12 | hanran | 2020-07-06 19:39:17 | NULL |
| 1221 | 13 | hanran | 2020-07-06 19:40:10 | NULL |
| 1222 | 14 | hanran | 2020-07-06 19:42:17 | NULL |
| 1223 | 15 | hanran | 2020-07-06 21:55:48 | {"author": "liuhan"} |
| 1224 | 16 | hanran | 2020-07-06 21:55:48 | {"author": "liuhan"} |
| 1226 | 17 | hanran | 2020-07-06 21:55:48 | {"age": 13, "author": "liuhan"} |
| 1227 | 18 | hanran | 2020-07-06 21:55:48 | {"age": 13, "author": "liuhan"} |
| 1229 | 20 | chenxi | 2020-07-11 16:20:50 | NULL |
| 1231 | 21 | chenxi | 2020-07-12 10:12:45 | NULL |
| 1232 | 134 | asdf | 2020-07-12 11:08:41 | NULL |
| 1233 | 26 | ranran | 2020-07-15 19:06:03 | NULL |
+------+--------+---------+---------------------+---------------------------------+

mysql> #为了方便演示，重新生成一个binlog
mysql> flush logs;

mysql> #删除表数据
mysql> delete from student;
Query OK, 14 rows affected (0.05 sec)

mysql> select * from student;
Empty set (0.00 sec)

查看binlog文件

./my2sql -user root -password 123456 -port 3306 \
-host 127.0.0.1 -databases testdb -tables student \
-work-type rollback -start-file mysql-bin.000045 \
-start-datetime "2020-07-18 11:40:00" --stop-datetime "2020-07-18 12:00:00" \
-output-dir tmpdir/

查看生成的回滚SQL

cd tmpdir/
ls
biglong_trx.txt binlog_status.txt rollback.45.sql

#查看DML信息
cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000045 2020-07-18_11:49:53 2020-07-18_11:49:53 293 806 0 0 14 testdb student
#上面信息可以看到解析的binlog DML情况

#查看回滚SQL
cat rollback.45.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\"}');
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1226,17,'hanran','2020-07-06 21:55:48','{\"age\":13,\"author\":\"liuhan\"}');
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1224,16,'hanran','2020-07-06 21:55:48','{\"author\":\"liuhan\"}');
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1223,15,'hanran','2020-07-06 21:55:48','{\"author\":\"liuhan\"}');
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1222,14,'hanran','2020-07-06 19:42:17',null);
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1221,13,'hanran','2020-07-06 19:40:10',null);
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1219,12,'hanran','2020-07-06 19:39:17',null);
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1218,12,null,'2020-07-06 19:39:17',null);
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1217,12,'hanraan','2020-07-06 19:39:17',null);
INSERT INTO `testdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (12,12,'che','2020-07-06 19:39:17',null);

#应用回滚SQL恢复数据
mysql -u root -p123456 -P3306 -h127.0.0.1 testdb < tmpdir/rollback.45.sql
mysql> select * from student;
+------+--------+---------+---------------------+---------------------------------+
| id | number | name | add_time | content |
+------+--------+---------+---------------------+---------------------------------+
| 12 | 12 | che | 2020-07-06 19:39:17 | NULL |
| 1217 | 12 | hanraan | 2020-07-06 19:39:17 | NULL |
| 1218 | 12 | NULL | 2020-07-06 19:39:17 | NULL |
| 1219 | 12 | hanran | 2020-07-06 19:39:17 | NULL |
| 1221 | 13 | hanran | 2020-07-06 19:40:10 | NULL |
| 1222 | 14 | hanran | 2020-07-06 19:42:17 | NULL |
| 1223 | 15 | hanran | 2020-07-06 21:55:48 | {"author": "liuhan"} |
| 1224 | 16 | hanran | 2020-07-06 21:55:48 | {"author": "liuhan"} |
| 1226 | 17 | hanran | 2020-07-06 21:55:48 | {"age": 13, "author": "liuhan"} |
| 1227 | 18 | hanran | 2020-07-06 21:55:48 | {"age": 13, "author": "liuhan"} |
| 1229 | 20 | chenxi | 2020-07-11 16:20:50 | NULL |
| 1231 | 21 | chenxi | 2020-07-12 10:12:45 | NULL |
| 1232 | 134 | asdf | 2020-07-12 11:08:41 | NULL |
| 1233 | 26 | ranran | 2020-07-15 19:06:03 | NULL |
+------+--------+---------+---------------------+---------------------------------+
14 rows in set (0.00 sec)

根据POS点解析binlog，生成回滚日志
也可以根据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.000045 \
-start-pos 4 -stop-file mysql-bin.000045 -stop-pos 837 \
-output-dir tmpdir/

解析binlog生成标准SQL

#查询数据表结构
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文件

./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 统计DML、长事务与大事务分析

zabbix是常用的监控系统，其底层使用的是mysql做为数据存储，这里以zabbix库为例，查看一段时间DML信息，以及事务信息

./my2sql -user root -password 123456 -port 3306 \
-databases testdb -tables student \
-big-trx-row-limit 500 -long-trx-seconds 300 \
-work-type stats -start-file mysql-bin.000045 \
-start-datetime "2020-07-18 11:40:00" --stop-datetime "2020-07-18 12:00:00" \
-output-dir tmpdir/

查看生成的分析结果

cd tmpdir
ls
biglong_trx.txt binlog_status.txt

#DML统计信息
head -n 10 binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.025924 2020-07-16_13:44:49 2020-07-16_13:45:18 373 30418263 192777 0 0 zabbix history
mysql-bin.025924 2020-07-16_13:44:49 2020-07-16_13:45:18 6312 30431731 0 80986 0 zabbix item_discovery
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:45:18 378419 30321953 0 122 0 zabbix hosts
mysql-bin.025924 2020-07-16_13:44:49 2020-07-16_13:45:18 539 30428268 437159 0 0 zabbix history_uint
mysql-bin.025924 2020-07-16_13:44:49 2020-07-16_13:45:18 254109 30431069 69 0 0 zabbix events
mysql-bin.025924 2020-07-16_13:44:49 2020-07-16_13:45:18 254563 30431311 45 22 0 zabbix problem
mysql-bin.025924 2020-07-16_13:44:49 2020-07-16_13:45:18 255024 30430836 0 62 0 zabbix item_rtdata
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:45:18 729854 30003688 22 0 0 zabbix event_recovery
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:45:18 763767 30161178 0 20 0 zabbix triggers

#事务统计信息
head -n 10 biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 297896 322782 981 0 [zabbix.history(inserts=206, updates=0, deletes=0) zabbix.history_uint(inserts=775, updates=0, deletes=0)]
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 347990 372526 967 0 [zabbix.history(inserts=206, updates=0, deletes=0) zabbix.history_uint(inserts=761, updates=0, deletes=0)]
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 379255 403816 968 0 [zabbix.history(inserts=139, updates=0, deletes=0) zabbix.history_uint(inserts=829, updates=0, deletes=0)]
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 432562 457923 1000 0 [zabbix.history_uint(inserts=842, updates=0, deletes=0) zabbix.history(inserts=158, updates=0, deletes=0)]
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 554079 579440 1000 0 [zabbix.history(inserts=119, updates=0, deletes=0) zabbix.history_uint(inserts=881, updates=0, deletes=0)]
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 579505 604851 998 0 [zabbix.history(inserts=334, updates=0, deletes=0) zabbix.history_uint(inserts=664, updates=0, deletes=0)]
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 604916 630277 1000 0 [zabbix.history(inserts=269, updates=0, deletes=0) zabbix.history_uint(inserts=731, updates=0, deletes=0)]
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 647063 672424 1000 0 [zabbix.history(inserts=184, updates=0, deletes=0) zabbix.history_uint(inserts=816, updates=0, deletes=0)]
mysql-bin.025924 2020-07-16_13:44:50 2020-07-16_13:44:50 672489 697850 1000 0 [zabbix.history_uint(inserts=734, updates=0, deletes=0) zabbix.history(inserts=266, updates=0, deletes=0)]

重要参数说明

-U

优先使用unique key作为where条件，默认false

-mode

repl: 伪装成从库解析binlog文件，file: 离线解析binlog文件, 默认repl

-local-binlog-file

当指定-mode=file 参数时，需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以连续解析多个binlog文件，只需要指定起始文件名，程序会自动持续解析下个文件

-add-extraInfo

是否把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

transaction with affected rows greater or equal to this value is considerated as big transaction 
找出满足n条sql的事务，默认500条

-databases 、 -tables

库及表条件过滤, 以逗号分隔

-sql

要解析的sql类型，可选参数insert、update、delete，默认全部解析

-doNotAddPrifixDb

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

为每个表生成一个sql文件

-full-columns

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

生成的insert语句是否去掉主键，默认false

-output-dir

将生成的结果存放到制定目录

-output-toScreen

将生成的结果打印到屏幕，默认写到文件

-threads

线程数，默认8个

-work-type

2sql：生成原始sql，rollback：生成回滚sql，stats：只统计DML、事务信息

使用案例

解析出标准SQL

根据时间点解析出标准SQL

#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl -work-type 2sql  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode file -local-binlog-file ./mysql-bin.011259  -work-type 2sql  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir

根据pos点解析出标准SQL

#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl  -work-type 2sql  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -mode file -local-binlog-file ./mysql-bin.011259  -work-type 2sql  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir

解析出回滚SQL

根据时间点解析出回滚SQL

#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl -work-type rollback  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -mode file -local-binlog-file ./mysql-bin.011259 -work-type rollback  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir

根据pos点解析出回滚SQL

#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl -work-type rollback  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306   -mode file -local-binlog-file ./mysql-bin.011259  -work-type rollback  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir

统计DML以及大事务

统计时间范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务

#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -mode repl -work-type stats  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00"  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode file -local-binlog-file ./mysql-bin.011259   -work-type stats  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00"  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir

统计一段pos点范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务

#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -mode repl -work-type stats  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode file -local-binlog-file ./mysql-bin.011259  -work-type stats  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir

从某一个pos点解析出标准SQL，并且持续打印到屏幕

#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl  -work-type 2sql  -start-file mysql-bin.011259  -start-pos 4   -output-toScreen

安装

有编译好的linux版本(CentOS release 7.x) 点击下载Linux版

限制

使用回滚/闪回功能时，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权限
MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析

posted @ 2021-12-11 23:45 屠魔的少年阅读(22) 评论(0) 收藏举报

刷新页面返回顶部