MySQL binlog回滚/闪回、前滚、DML统计、长事务与大事务分析之my2sql工具

最新推荐文章于 2023-09-14 14:26:00 发布

原创最新推荐文章于 2023-09-14 14:26:00 发布 · 2.4k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #github #运维 #数据库 #golang

mysql 同时被 3 个专栏收录

12 篇文章

订阅专栏

数据库运维平台

12 篇文章

订阅专栏

github

4 篇文章

订阅专栏

my2sql是一款用Go语言编写的MySQL binlog解析工具，能生成原始SQL、回滚SQL和DML统计信息。适用于数据回滚、主从切换修复、事务分析等场景。支持json、blob等复杂数据类型的sql生成，速度快，全量解析1.1G binlog仅需1分30秒。具备丰富的参数选项，如时间点解析、线程数调整等。

my2sql简介

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

用途

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

工具优势

功能丰富，参考上文
基于golang实现，速度快，全量解析1.1Gbinlog只需要1分30秒左右，当前其他类似开源工具一般要几十分钟

重要参数说明

-U

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

-add-extraInfo

是否把database/table/datetime/binlogposition…信息以注释的方式加入生成的每条sql前，默认false

-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


./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -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


./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos  583918266  -output-dir ./tmpdir

根据时间点解析出回滚SQL


./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -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


./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type rollback  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir

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


./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -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秒的事务


./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -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，并且持续打印到屏幕


./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  -start-file mysql-bin.011259  -start-pos 4   -output-toScreen

安装

参考：
https://github.com/liuhr/my2sql

限制

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