首页 › 智明杂谈 › mysql查看执行失败的sql (mysql数据库闪回)

mysql查看执行失败的sql (mysql数据库闪回)

🏷️ 智明杂谈 ✍️ 学的越多，知道的越少 📅 2026-03-14T03:21:33+00:00

背景

my2sql是一个使用go语言开发的数据闪回工具.对比目前市面上存在的binlog2sql和myflash

my2sql具有处理速度快.使用简单的优点.

mysql数据库的闪回,mysql数据库回滚工具

my2sql用途

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

my2sql安装方式

GitHub地址: https://github.com/liuhr/my2sql

直接可执行文件*载下**

链接：https://pan.baidu.com/s/1fjbONHwPwCthNeTKLkJSUA 
提取码：9se3 

安装方法. *载下**下来加个执行权限就可以

my2sql主要参数

  -U	prefer to use unique key instead of primary key to build where condition for delete/update sql
  -add-extraInfo --  是否把database/table/datetime/binlogposition...信息以注释的方式加入生成的每条sql前，默认false
    	Works with -work-type=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
  -big-trx-row-limit int  -- 找出影响了n行数据的事务，默认500条
    	transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 10 to 30000, default 500 (default 500)
  -databases string  -- 库及表条件过滤, 以逗号分隔
    	only parse these databases, comma seperated, default all.
  -do-not-add-prifixDb  -- 默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql，也可以生成不带库名的sql
    	Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1). 
  -file-per-table  -- 为每个表生成一个sql文件
    	One file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
  -full-columns   -- 生成的sql是否带全列信息，默认false
    	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   -- 生成的insert语句是否去掉主键，默认false
    	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 1 to 3600, default 300 (default 300)
  -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   -- 要解析的sql类型，可选参数insert、update、delete，默认全部解析
    	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: "2026-03-14T03:21:33+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: "2026-03-14T03:21:33+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  线程数，默认2个
    	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   -- 2sql表示生成原始sql，rollback表示生成回滚sql，stats表示只统计DML、事务信息
    	valid options are:  2sql,rollback,stats. 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: 2sql (default "2sql")

简单使用

根据时间点解析出标准SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  \
-start-file mysql-bin.011259  -start-datetime "2026-03-14T03:21:33+00:00" -stop-datetime "2026-03-14T03:21:33+00:00" \
-output-dir ./tmpdir

根据position点解析出标准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

可以的是不支持GTID模式.目前还是position模式

根据时间点解析出回滚SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type rollback  \
-start-file mysql-bin.011259  -start-datetime "2026-03-14T03:21:33+00:00" -stop-datetime "2026-03-14T03:21:33+00:00" \
-output-dir ./tmpdir

根据position点生成回滚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

使用my2SQL统计大事务

统计时间范围各个表的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 "2026-03-14T03:21:33+00:00" -stop-datetime "2026-03-14T03:21:33+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

使用限制

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

总结

这篇文章主要分享了mysql 回滚工具my2sql的使用