使用my2sql解析mysql binlog日志

原创飞天 2024-09-23

453

前言

今天同事提了个需求，最近mysql库的binlog涨的非常快，怎么能定位到哪些sql语句导致了binlog的暴涨？话不多说，用my2sql工具吧！

my2sql介绍

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

my2sql用途及使用场景

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

my2sql使用限制

使用回滚/闪回功能时，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才能解析

下载my2sql工具

wget https://github.com/liuhr/my2sql/raw/master/releases/centOS_release_7.x/my2sql
本文中my2sql工具下载到了/soft目录下面。

my2sql和binlog2sql性能对比

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

my2sql工具使用

[root@node2 soft]# ls -l my2sql 
-rw-r--r-- 1 root root 7919430  Sep 23 11:32 my2sql
# 授予执行权限
[root@node2 soft]# chmod +x my2sql
[root@node2 soft]# ls -l my2sql 
-rwxr-xr-x 1 root root 7919430  Sep 23 11:32 my2sql
···		
可以看出，my2sql工具非常小巧，大小不到8MB。

[root@node2 soft]# ./my2sql --help
my2sql V2.0
  -U    prefer to use unique key instead of primary key to build where condition for delete/update sql
  -add-extraInfo
        Works with -work-type=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
  -big-trx-row-limit int
        transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 1 to 30000, default 10 (default 10)
  -databases string
        only parse these databases, comma seperated, default all.
  -do-not-add-prifixDb
        Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1). 
  -file-per-table
        One file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
  -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
  -host string
        mysql host, default 127.0.0.1 . (default "127.0.0.1")
  -ignore-databases string
        ignore parse these databases, comma seperated, default null
  -ignore-primaryKey-forInsert
        for insert statement when -workType=2sql, ignore primary key
  -ignore-tables string
        ignore parse these tables, comma seperated, default null
  -local-binlog-file string
        local binlog files to process, It works with -mode=file 
  -long-trx-seconds int
        transaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 0 to 3600, default 1 (default 1)
  -mode string
        valid options are:  repl,file. repl: as a slave to get binlogs from master. file: get binlogs from local filesystem. default repl (default "repl")
  -mysql-type string
        valid options are:  mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")
  -output-dir string
        result output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space
  -output-toScreen
        Just output to screen,do not write to file
  -password string
        mysql user password.
  -port uint
        mysql port, default 3306. (default 3306)
  -print-interval int
        works with -w='stats', print stats info each PrintInterval. Valid values range from 1 to 600, default 30 (default 30)
  -server-id uint
        this program replicates from mysql as slave to read binlogs. Must set this server id unique from other slaves, default 1113306 (default 1113306)
  -sql string
        valid options are:  insert,update,delete. only parse these types of sql, comma seperated, valid types are: insert, update, delete; default is all(insert,update,delete)
  -start-datetime string
        Start reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2020-01-01 01:00:00"
  -start-file string
        binlog file to start reading
  -start-pos uint
        start reading the binlog at position (default 4)
  -stop-datetime string
        Stop reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2020-12-30 01:00:00"
  -stop-file string
        binlog file to stop reading
  -stop-pos uint
        Stop reading the binlog at position (default 4)
  -tables string
        only parse these tables, comma seperated, DONOT prefix with schema, default all.
  -threads uint
        Works with -workType=2sql|rollback. threads to run (default 2)
  -tl string
        time location to parse timestamp/datetime column in binlog, such as Asia/Shanghai. default Local (default "Local")
  -user string
        mysql user. 
  -v    print version
  -work-type string
        valid options are:  2sql,rollback,stats. 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: 2sql (default "2sql")
[root@node2 soft]#

示例1、生成dml统计&大/长事务统计数据

功能：生成指定binlog范围内dml统计&大/长事务统计数据，也可以指定日期时间范围。

./my2sql -user root -password 'Wel%1234'  -port 3306 -host 192.*.*.20 -work-type stats -start-file binlog.000011 stop-file binlog.000012

执行命令后，会在当前目录下生成文件：biglong_trx.txt、 binlog_status.txt
binlog_status.txt文件：统计在指定的binlog之间，每个时间段(POS)区间内所产生的DML次数
biglong_trx.txt文件：记录的是大/长事务

#查看binlog_status.txt文件内容
[root@node2 soft]# cat binlog_status.txt 

binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table
binlog.000012     2024-09-22_00:01:38 2024-09-22_00:01:38 3943       4140       1        0        0        ogg             ggs_checkpoint
binlog.000012     2024-09-22_10:24:29 2024-09-22_10:24:29 4530       4844       0        1        0        ogg             ggs_checkpoint
binlog.000012     2024-09-22_10:24:29 2024-09-22_10:24:29 4324       4530       2        0        0        ogg             test_ogg
binlog.000012     2024-09-22_10:26:58 2024-09-22_10:26:58 5028       5131       1        0        0        ogg             test_ogg
binlog.000012     2024-09-22_10:26:58 2024-09-22_10:26:58 5131       5503       0        1        0        ogg             ggs_checkpoint
binlog.000012     2024-09-22_13:28:46 2024-09-22_13:28:48 5704       6847       0        2        0        ogg             ggs_checkpoint
binlog.000012     2024-09-22_13:28:48 2024-09-22_13:28:48 6260       6466       2        0        0        ogg             test_ogg
binlog.000012     2024-09-22_13:29:47 2024-09-22_13:29:47 7031       7134       1        0        0        ogg             test_ogg
binlog.000012     2024-09-22_13:29:47 2024-09-22_13:29:47 7134       7523       0        1        0        ogg             ggs_checkpoint
binlog.000012     2024-09-22_13:44:39 2024-09-22_13:44:39 7707       7808       1        0        0        ogg             test_ogg
binlog.000012     2024-09-22_13:44:39 2024-09-22_13:44:39 7808       8197       0        1        0        ogg             ggs_checkpoint
binlog.000012     2024-09-22_21:52:30 2024-09-22_21:52:30 8381       8524       0        0        7        ogg             test_ogg
binlog.000012     2024-09-22_23:07:14 2024-09-22_23:07:14 8708       9633       9        0        0        ogg             test_ogg
binlog.000012     2024-09-23_14:34:08 2024-09-23_14:34:37 9817       10551      5        5        0        tt1             t1
binlog.000012     2024-09-23_14:34:28 2024-09-23_14:34:28 10108      10225      7        0        0        tt1             t2
binlog.000012     2024-09-23_14:35:02 2024-09-23_14:35:02 10735      10822      0        0        1        tt1             t2
#查看binlog_status.txt文件内容
[root@node2 soft]# cat biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
此文件为空，说明在指定的两个binlog之间没有大/长事务

示例2、生成sql语句

功能：生成指定binlog范围内执行过的sql语句，也可以指定日期时间范围。

./my2sql -user root -password 'Wel%1234'  -port 3306 -host 192.*.*.20 -work-type 2sql  -start-file binlog.000011 stop-file binlog.000012   -output-dir /soft/output/

执行命令后，会在当前目录下生成文件：forward.12.sql。

#查看forward.12.sql 文件内容
[root@node2 soft]# cat forward.12.sql 
INSERT INTO `ogg`.`ggs_checkpoint` (`group_name`,`group_key`,`seqno`,`rba`,`audit_ts`,`create_ts`,`last_update_ts`,`current_dir`,`log_bsn`,`log_csn`,`log_xid`,`log_cmplt_csn`,`log_cmplt_xids`,`version`) VALUES ('R_TAB1','3149183463','0','0',null,'2024-09-22 00:00:13','2024-09-22 00:01:38','/goldengate',null,null,null,null,null,'1');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (3,'c');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (4,'d');
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='2302', `audit_ts`='2024-09-22 10:12:22.147836', `last_update_ts`='2024-09-22 10:24:29', `log_csn`='2487709', `log_cmplt_csn`='2487709', `log_cmplt_xids`='0.7.33.1041' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (5,'e');
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='2536', `audit_ts`='2024-09-22 10:26:52.146338', `last_update_ts`='2024-09-22 10:26:58', `log_csn`='2489738', `log_cmplt_csn`='2489738', `log_cmplt_xids`='0.2.29.1171' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
UPDATE `ogg`.`ggs_checkpoint` SET `seqno`='1', `rba`='1524', `audit_ts`='2024-09-22 13:28:40.472852', `last_update_ts`='2024-09-22 13:28:46' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (6,'f');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (7,'a');
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='2419', `audit_ts`='2024-09-22 13:27:07.258467', `last_update_ts`='2024-09-22 13:28:48', `log_bsn`='2497639', `log_csn`='2497762', `log_cmplt_csn`='2497762', `log_cmplt_xids`='0.4.12.1091' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (8,'b');
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='2648', `audit_ts`='2024-09-22 13:29:39.258317', `last_update_ts`='2024-09-22 13:29:47', `log_csn`='2498339', `log_cmplt_csn`='2498339', `log_cmplt_xids`='0.7.2.1045' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (9,null);
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='2877', `audit_ts`='2024-09-22 13:38:34.253013', `last_update_ts`='2024-09-22 13:44:39', `log_csn`='2499441', `log_cmplt_csn`='2499441', `log_cmplt_xids`='0.7.33.1042' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
DELETE FROM `ogg`.`test_ogg` WHERE `id`=3;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=4;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=5;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=6;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=7;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=8;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=9;
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (4,'d');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (5,'e');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (6,'f');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (7,'a');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (8,'b');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (9,null);
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (1,'a');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (2,'b');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (3,'c');
INSERT INTO `tt1`.`t1` (`id`) VALUES (1);
INSERT INTO `tt1`.`t1` (`id`) VALUES (2);
INSERT INTO `tt1`.`t1` (`id`) VALUES (3);
INSERT INTO `tt1`.`t1` (`id`) VALUES (4);
INSERT INTO `tt1`.`t1` (`id`) VALUES (5);
INSERT INTO `tt1`.`t2` (`id`) VALUES (1);
INSERT INTO `tt1`.`t2` (`id`) VALUES (2);
INSERT INTO `tt1`.`t2` (`id`) VALUES (3);
INSERT INTO `tt1`.`t2` (`id`) VALUES (4);
INSERT INTO `tt1`.`t2` (`id`) VALUES (5);
INSERT INTO `tt1`.`t2` (`id`) VALUES (6);
INSERT INTO `tt1`.`t2` (`id`) VALUES (7);
UPDATE `tt1`.`t1` SET `id`=10 WHERE `id`=1;
UPDATE `tt1`.`t1` SET `id`=10 WHERE `id`=2;
UPDATE `tt1`.`t1` SET `id`=10 WHERE `id`=3;
UPDATE `tt1`.`t1` SET `id`=10 WHERE `id`=4;
UPDATE `tt1`.`t1` SET `id`=10 WHERE `id`=5;
DELETE FROM `tt1`.`t2` WHERE `id`=7;

示例3、生成回滚sql

功能：生成指定binlog范围内的回滚sql语句，也可以指定日期时间范围。当我们做了一些误操作需要闪回数据时，可以使用此功能。

./my2sql -user root -password 'Wel%1234'  -port 3306 -host 192.*.*.20 -work-type rollback  -start-file binlog.000011 stop-file binlog.000012   -output-dir /soft/output/

执行命令后，会在当前目录下生成文件：rollback.12.sql

#查看rollback.12.sql文件内容
[root@node2 soft]# cat rollback.12.sql 
INSERT INTO `tt1`.`t2` (`id`) VALUES (7);
UPDATE `tt1`.`t1` SET `id`=5 WHERE `id`=10;
UPDATE `tt1`.`t1` SET `id`=4 WHERE `id`=10;
UPDATE `tt1`.`t1` SET `id`=3 WHERE `id`=10;
UPDATE `tt1`.`t1` SET `id`=2 WHERE `id`=10;
UPDATE `tt1`.`t1` SET `id`=1 WHERE `id`=10;
DELETE FROM `tt1`.`t2` WHERE `id`=7;
DELETE FROM `tt1`.`t2` WHERE `id`=6;
DELETE FROM `tt1`.`t2` WHERE `id`=5;
DELETE FROM `tt1`.`t2` WHERE `id`=4;
DELETE FROM `tt1`.`t2` WHERE `id`=3;
DELETE FROM `tt1`.`t2` WHERE `id`=2;
DELETE FROM `tt1`.`t2` WHERE `id`=1;
DELETE FROM `tt1`.`t1` WHERE `id`=5;
DELETE FROM `tt1`.`t1` WHERE `id`=4;
DELETE FROM `tt1`.`t1` WHERE `id`=3;
DELETE FROM `tt1`.`t1` WHERE `id`=2;
DELETE FROM `tt1`.`t1` WHERE `id`=1;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=3;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=2;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=1;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=9;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=8;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=7;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=6;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=5;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=4;
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (9,null);
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (8,'b');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (7,'a');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (6,'f');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (5,'e');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (4,'d');
INSERT INTO `ogg`.`test_ogg` (`id`,`name`) VALUES (3,'c');
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='2648', `audit_ts`='2024-09-22 13:29:39.258317', `last_update_ts`='2024-09-22 13:29:47', `log_csn`='2498339', `log_cmplt_csn`='2498339', `log_cmplt_xids`='0.7.2.1045' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
DELETE FROM `ogg`.`test_ogg` WHERE `id`=9;
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='2419', `audit_ts`='2024-09-22 13:27:07.258467', `last_update_ts`='2024-09-22 13:28:48', `log_csn`='2497762', `log_cmplt_csn`='2497762', `log_cmplt_xids`='0.4.12.1091' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
DELETE FROM `ogg`.`test_ogg` WHERE `id`=8;
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='1524', `audit_ts`='2024-09-22 13:28:40.472852', `last_update_ts`='2024-09-22 13:28:46', `log_bsn`=null, `log_csn`='2489738', `log_cmplt_csn`='2489738', `log_cmplt_xids`='0.2.29.1171' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
DELETE FROM `ogg`.`test_ogg` WHERE `id`=7;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=6;
UPDATE `ogg`.`ggs_checkpoint` SET `seqno`='0', `rba`='2536', `audit_ts`='2024-09-22 10:26:52.146338', `last_update_ts`='2024-09-22 10:26:58' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='2302', `audit_ts`='2024-09-22 10:12:22.147836', `last_update_ts`='2024-09-22 10:24:29', `log_csn`='2487709', `log_cmplt_csn`='2487709', `log_cmplt_xids`='0.7.33.1041' WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
DELETE FROM `ogg`.`test_ogg` WHERE `id`=5;
UPDATE `ogg`.`ggs_checkpoint` SET `rba`='0', `audit_ts`=null, `last_update_ts`='2024-09-22 00:01:38', `log_csn`=null, `log_cmplt_csn`=null, `log_cmplt_xids`=null WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
DELETE FROM `ogg`.`test_ogg` WHERE `id`=4;
DELETE FROM `ogg`.`test_ogg` WHERE `id`=3;
DELETE FROM `ogg`.`ggs_checkpoint` WHERE (`group_name`='R_TAB1' AND `group_key`='3149183463');
[root@node2 soft]#

总结

my2sql工具虽小，但非常实用，是DBA工作中必不可少的好帮手。把学到的东西写出来，希望大家能共同进步！

关于作者：
网名：飞天，墨天轮2024年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证以及OBCA、KCP、ACP、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgresSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同探讨~~~

墨力计划 mysql

最后修改时间：2025-01-17 14:50:36

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

MySql数据库（共27篇）

MySql数据库