使用my2sql提取binlog里的数据

# ./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字段内容会出现解析不正确的情况】		

1、对于bit类型，my2sql生成的是不带b标识的，例如 b'0' 会被转成0 b'1' 会被转成 1 。这个是有点问题的，需要人工修复。
2、对于json列，如果解析的时候加了-full-columns参数，生成的sql的where条件里面的json列的内容拼装会有问题，因此一般不加这个参数。

 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明细

 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