安装

根据操作系统版本下载对应版本

wget https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql

用途

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

限制

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

主要参数

• -U
  优先使用unique key作为where条件，默认false
• -add-extraInfo
  当-work-type=2sql|rollback时，是否把database/table/datetime/binlogposition...信息以注释的方式加入生成的每条sql前，默认false
• -big-trx-row-limit int
  找出满足n行的sql的事务，默认10条，有效值 1-30000
• -databases string
  库条件过滤, 以逗号分隔，默认全部
• -do-not-add-prifixDb
  默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql，加上参数不生成库前缀
• -file-per-table
  若为true,为每个表生成一个sql文件，默认为false。注意：一个文件对应一个binlog
• -full-columns
  生成的sql是否带全列信息，默认false,使用更改的列生成集合部分，使用主键/唯一键生成where条件
• -host string
  host 地址，default 127.0.0.1
• -ignore-databases string
  忽略数据库，逗号分割，默认null
• -ignore-primaryKey-forInsert
  使用-workType=2sql，生成的insert语句是否去掉主键，默认false
• -ignore-tables string
  忽略数据表，逗号分割，默认null
• -local-binlog-file string
  当指定-mode=file 参数时，需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以连续解析多个binlog文件，只需要指定起始文件名，程序会自动持续解析下个文件
• -long-trx-seconds int
  持续时间超出n秒的事务，有效值 0-3600，默认1
• -mode string
  repl: 伪装成从库解析binlog文件，file: 离线解析binlog文件, 默认repl
• -mysql-type string
  mysql类型：mysql,mariadb，默认mysql
• -output-dir string
  将生成的结果存放到制定目录，默认当前目录。注意：结果集个能很大，注意空间
• -output-toScreen
  将生成的结果打印到屏幕，默认写到文件
• -password string
  mysql user password.
• -port uint
  mysql port, default 3306. (default 3306)
• -print-interval int
  与-w='stats'配合使用，打印每个PrintInterval的统计信息。有效值范围从1到600，默认值30（默认值30）
• -server-id uint
  伪装slave时使用的server-id，不能与其他从库相同，default 1113306
• -sql string
  要解析的sql类型，可选参数insert、update、delete，逗号分割，默认全部解析
• -start-datetime string
  开始时间（包含）如: "2020-01-01 01:00:00"
• -start-file string
  开始解析的binlog文件
• -start-pos uint
  开始解析的pos点，默认4
• -stop-datetime string
  结束时间（不包含）如: "2020-12-30 01:00:00"
• -stop-file string
  结束解析的binlog文件
• -stop-pos uint
  结束解析的pos点，默认4
• -tables string
  表条件过滤, 以逗号分隔，不要库前缀，默认全部
• -threads uint
  并行度，默认 2
• -tl string
  指定时区，例如 Asia/Shanghai. default Local (default "Local")
• -user string
  mysql user.
• -v
  print version
• -work-type string
  解析类型：2sql,rollback,stats，2sql：生成原始sql，rollback：生成回滚sql，stats：只统计DML、事务信息，default: 2sql

常用语句

mkdir tmpdir

• 可以解析时候添加指定的库表信息

根据时间点解析标准SQL/回滚SQL

注意：默认是解析到最后一个binlog，可指定binlog范围,如果是解析到最后一个的，不能进行指定

########################  标准 SQL  #############################
## 伪装成从库解析binlog
./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode repl \
-work-type 2sql  \
-start-file mysql-bin.000006  \
-stop-file mysql-bin.000007  \
-start-datetime "2022-10-28 11:15:00" \
-stop-datetime "2022-10-28 11:16:53" \
-output-dir ./tmpdir

#直接读取binlog文件解析
 ./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode file \
-local-binlog-file ./mysql-bin.000006 \
-work-type 2sql  \
-start-file mysql-bin.000006  \
-stop-file mysql-bin.000007  \
-start-datetime "2022-10-28 11:15:00" \
-stop-datetime "2022-10-28 11:16:53" \
-output-dir ./tmpdir

########################  回滚 SQL  #############################
## 伪装成从库解析binlog
./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode repl \
-work-type rollback  \
-start-file mysql-bin.000006  \
-stop-file mysql-bin.000007  \
-start-datetime "2022-10-28 11:15:00" \
-stop-datetime "2022-10-28 11:16:53" \
-output-dir ./tmpdir

#直接读取binlog文件解析
 ./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode file \
-local-binlog-file ./mysql-bin.000006 \
-work-type rollback  \
-start-file mysql-bin.000006  \
-stop-file mysql-bin.000007  \
-start-datetime "2022-10-28 11:15:00" \
-stop-datetime "2022-10-28 11:16:53" \
-output-dir ./tmpdir

根据pos点解析出标准SQL/回滚SQL

注意：

start-pos点要找 mapped to number xxxx 上面的pos点

pos解析的binlog 只能是单个binlog文件中

########################  标准 SQL  #############################
## 伪装成从库解析binlog
./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode repl \
-work-type 2sql  \
-start-file mysql-bin.000006  \
-stop-file mysql-bin.000006  \
-start-pos 1410 \
-stop-pos 1541 \
-output-dir ./tmpdir

#直接读取binlog文件解析
 ./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode file \
-local-binlog-file ./mysql-bin.000006 \
-work-type 2sql  \
-start-file mysql-bin.000006  \
-stop-file mysql-bin.000006  \
-start-datetime "2022-10-28 11:15:00" \
-stop-datetime "2022-10-28 11:16:53" \
-output-dir ./tmpdir


########################  回滚 SQL  #############################
## 伪装成从库解析binlog
./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode repl \
-work-type rollback  \
-start-file mysql-bin.000006  \
-stop-file mysql-bin.000006  \
-start-pos 1410 \
-stop-pos 1541 \
-output-dir ./tmpdir

#直接读取binlog文件解析
 ./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode file \
-local-binlog-file ./mysql-bin.000006 \
-work-type rollback  \
-start-file mysql-bin.000006  \
-stop-file mysql-bin.000006  \
-start-datetime "2022-10-28 11:15:00" \
-stop-datetime "2022-10-28 11:16:53" \
-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 

模拟实战

模拟误删了数据

mysql> delete from yqtest.sbtest1;
Query OK, 279552 rows affected (10.10 sec)

闪回数据

# 先查看当前的binlog地址
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                    |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000010 | 53344137 |              |                  | 645a456e-4f79-11ed-bee1-000c294448db:1-329386 |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


# 进行闪回数据
./my2sql  -user yqtest -password 123456 -host 192.168.66.167 -port 3312 \
-mode repl  \
-work-type 2sql  \
-start-file mysql-bin.000010  \
-start-datetime "2022-10-28 16:50:00" \
-stop-datetime "2022-10-28 16:56:00" \
-databases yqtest \
-tables sbtest1 \
-sql delete \
-output-dir ./tmpdir

离线恢复也可使用repl方式进行恢复（binlog下载后伪造在本地库） 需要创建好对应的库表结构