My2sql

My2sql--MySQL闪回工具--DBA必备

前提：mysql数据库binlog_format=ROW;

1.my2sql工具上传  version V2.0

rz  -e  my2sql-master.zip

mv my2sql-maste.zip /root/

unzip my2sql-master.zip

mv my2sql-master-1ff5d20e854fd7be43ff07c88358fe3754f29af0 my2sql-master

cd my2sql-master/releases/centOS_release_7.x

2.my2sql参数

[root@vm10-110-68-46 centOS_release_7.x]# ./my2sql --version

flag provided but not defined: -version

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")

                                    2sql：正向解析binlog为sql语句        rollback：反向解析binlog为sql语句即回滚语句

3.数据闪回（生成sql回滚语句）

./my2sql -user admin -password '123' -port 3306 -host xx.xx.xx.xx -databases binlog -tables message -work-type rollback -start-file binlog.002025 --stop-file binlog.002027 -output-dir /jlc/

4.正向解析binlog（常用于备份+binlog恢复数据）

./my2sql -user admin -password '123' -port 3306 -host xx.xx.xx.xx -databases binlog -tables message -work-type 2sql -start-file binlog.002025 --stop-file binlog.002027 -output-dir /jlc/

注意：-start-file参数 与-stop-file参数

-sql 参数，insert，update，delete，  只过滤源sql为此类型的sql进行重做或者闪回