项目地址： https://github.com/liuhr/my2sql
参数和说明
代码语言：javascript复制# ./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")
说明：
-sql string 提取的变更类型，默认是all，也可以 insert,update,delete 用逗号分隔开
-add-extraInfo 是否把database/table/datetime/binlogposition...信息以注释的方式加入生成的每条sql前，默认false
-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 【测试发现，如果加了这个参数，但是解析的表里面有json列，最终生成的rollback.sql里面的where条件的json字段内容会出现解析不正确的情况】 已知的问题
代码语言：javascript复制1、对于bit类型，my2sql生成的是不带b标识的，例如 b'0' 会被转成0 b'1' 会被转成 1 。这个是有点问题的，需要人工修复。
2、对于json列，如果解析的时候加了-full-columns参数，生成的sql的where条件里面的json列的内容拼装会有问题，因此一般不加这个参数。使用示例
1、分析本地离线binlog文件模式
代码语言：javascript复制 1 账号 需要有查看表结构的权限
2 密码策略必须是 mysql_native_password 模式
# 提取当时的操作记录
mkdir -pv tmpdir_offline_forward_0316
./my2sql -user dts -password 'dts' -host 192.168.1.11 -port 3306 -mode file -work-type 2sql -start-file ./mysql-bin.001916 -local-binlog-file ./mysql-bin.001916 -databases db1 -tables tb1 -output-dir ./tmpdir_offline_forward_0316
-rw-r--r-- 1 root root 284 2023-03-08 17:27 biglong_trx.txt
-rw-r--r-- 1 root root 292 2023-03-08 17:27 binlog_status.txt
-rw-r--r-- 1 root root 28K 2023-03-08 17:27 forward.1916.sql # 提取到的sql明细
# 反向生成回滚记录
mkdir -pv tmpdir_offline_rollback
./my2sql -user dts -password 'dts' -host 192.168.1.11 -port 3306 -mode file -work-type rollback -start-file ./mysql-bin.001916 -local-binlog-file ./mysql-bin.001916 -databases db1 -tables tb1 -output-dir ./tmpdir_offline_rollback
-rw-r--r-- 1 root root 284 2023-03-08 17:27 biglong_trx.txt
-rw-r--r-- 1 root root 292 2023-03-08 17:27 binlog_status.txt
-rw-r--r-- 1 root root 28K 2023-03-08 17:27 rollback.1916.sql # 提取到的回滚sql明细
2、伪装成从库，直接分析远程mysql指定的binlog文件模式
代码语言：javascript复制 1 需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限
2 使用rollback功能时，要解析的binlog段，表结构要保持一致
3 密码策略必须是 mysql_native_password 模式
# 提取当时的操作记录
mkdir -pv tmpdir_online_forward_0316
./my2sql -user dts -password 'dts' -host 192.168.1.11 -port 3306 -mode repl -work-type 2sql -start-file binlog.000014 -start-datetime "2023-03-16 20:00:00" -stop-datetime "2023-03-16 22:00:00" -databases db1 -tables tb1 -output-dir ./tmpdir_online_forward_0316
# 反向生成回滚记录
mkdir -pv tmpdir_online_rollback_0316
./my2sql -user dts -password 'dts' -host 192.168.1.11 -port 3306 -mode repl -work-type rollback -start-file binlog.000014 -start-datetime "2023-03-16 20:00:00" -stop-datetime "2023-03-16 22:00:00" -databases db1 -tables tb1 -add-extraInfo -output-dir ./tmpdir_online_rollback_0316
