看腻了文字就来听听视频演示吧：https://www.bilibili.com/video/BV1rp4y1w74B/
github项目：https://github.com/liuhr/my2sql
gitee链接：https://gitee.com/mirrors/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生成
github给出的产品性能对比
| 测试场景 | my2sql | binlog2sql |
|---|---|---|
| 1.1G binlog生成回滚SQL | 1分40秒 | 65分钟 |
| 1.1G binlog生成原始SQL | 1分30秒 | 50分钟 |
| 1.1G binlog生成表DML统计信息、以及事务统计信息 | 40秒 | 不支持 |
有闲得测试一下
使用限制
- 使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响
- 只能回滚DML， 不能回滚DDL，即drop table是恢复不了的
- 支持指定-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才能解析
下载安装
- 方式一：下载二进制版本
有编译好的linux版本(CentOS release 7.x)点击下载Linux版
- 方式二：编译安装
需要go语言环境来做编译
cd $GOPATH/src
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .
直接下载源码包，找到里面的二进制版本直接使用
[root@dba ~]# unzip my2sql-master.zip
[root@dba my2sql-master]# cd my2sql-master
[root@dba my2sql-master]# cp releases/centOS_release_7.x/my2sql /usr/bin/
[root@dba my2sql-master]# my2sql --help
my2back V1.0# 优先使用unique key作为where条件，默认false-U prefer to use unique key instead of primary key to build where condition for delete/update sql-add-extraInfoWorks with -work-type=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false# 找出满足n条sql的事务，默认500条-big-trx-row-limit inttransaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 1 to 10, default 30000 (default 30000)-databases stringonly parse these databases, comma seperated, default all.# 生成不带库名的sql-do-not-add-prifixDbPrefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1). # 为每个表生成一个sql文件-file-per-tableOne file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog# 生成的sql是否带全列信息，默认false-full-columnsFor 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 stringmysql host, default 127.0.0.1 . (default "127.0.0.1")-ignore-databases stringignore parse these databases, comma seperated, default null# 生成的insert语句是否去掉主键，默认false-ignore-primaryKey-forInsertfor insert statement when -workType=2sql, ignore primary key-ignore-tables stringignore parse these tables, comma seperated, default null# 当指定-mode=file 参数时，需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以连续解析多个binlog文件，只需要指定起始文件名，程序会自动持续解析下个文件-local-binlog-file stringlocal binlog files to process, It works with -mode=file -long-trx-seconds inttransaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 0 to 1, default 3600 (default 3600)# repl：默认，伪装成从库解析binlog；file：直接读取binlog文件解析-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 stringvalid options are: mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")# 将生成的结果存放到制定目录-output-dir stringresult output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space# 将生成的结果打印到屏幕，默认写到文件-output-toScreenJust output to screen,do not write to file-password stringmysql user password.-port uintmysql port, default 3306. (default 3306)-print-interval intworks with -w='stats', print stats info each PrintInterval. Valid values range from 1 to 600, default 30 (default 30)-server-id uintthis program replicates from mysql as slave to read binlogs. Must set this server id unique from other slaves, default 1113306 (default 1113306)# 要解析的sql类型，可选参数insert、update、delete，默认全部解析-sql stringvalid 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 stringStart 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 stringbinlog file to start reading-start-pos uintstart reading the binlog at position (default 4)-stop-datetime stringStop 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 stringbinlog file to stop reading-stop-pos uintStop reading the binlog at position (default 4)-tables stringonly parse these tables, comma seperated, DONOT prefix with schema, default all.# 线程数-threads uintWorks with -workType=2sql|rollback. threads to run (default 2)-tl stringtime location to parse timestamp/datetime column in binlog, such as Asia/Shanghai. default Local (default "Local")-user stringmysql user. -v print version# 2sql：生成原始sql，rollback：生成回滚sql，stats：只统计DML、事务信息-work-type stringvalid options are: 2sql,rollback,stats. 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: 2sql (default "2sql")
开干
# 参数要求，也是默认参数
binlog_format=ROW
binlog_row_image=FULL# 伪装成从库解析binlog
my2sql -host 127.0.0.1 -user root -password root -port 3306 -mode repl -work-type 2sql -start-file mysql-bin.000011 -output-toScreen
# 直接读取binlog文件解析
my2sql -host 127.0.0.1 -user root -password root -port 3306 -mode file -local-binlog-file /mysqldata/data/mysql-bin.000011 -work-type 2sql -start-file /mysqldata/data/mysql-bin.000011 -output-toScreen# 重新生成一个binlog日志
mysql> flush logs;# 误删表数据
delete from t_student;
# 查看当前biglog日志所在的点
show master logs;# 立即定位结束时间
mysql> select now();# 根据操作时间解析binlog（定位时间缩小范围，避免解析多余的内容），生成回滚日志
my2sql -host 127.0.0.1 -user root -password root -port 3306 -databases mdb -tables t_student -work-type rollback -start-file mysql-bin.000011 -sql delete -start-datetime "2023-02-23 02:36:17" --stop-datetime "2023-02-23 02:38:17" -output-dir /mysqldata/backup/# 将生成的DML语句在库里source恢复
source /mysqldata/backup/rollback.11.sql
