MySQL/MariaDB binlog/relaylog 回滚/闪回,前滚,DML分析报告,DDL信息

Posted on | Edited on | In | Views: ℃
alt

喜歡就去追阿～ 晚下手，你就是孩子她舅！早下手，就是喜当爹．．．

前言

flashback闪回原理上一篇

flashback的概念最早出现于Oracle数据库，用于快速恢复用户的误操作。

flashback for MySQL用于恢复由DML语句引起的误操作，目前不支持DDL语句。例如下面的语句：

1
mysql> DELETE FROM XXX; UPDATE XXX SET YYY=ZZZ;

若没有flashback功能，那么当发生误操作时，用户只能通过全备+二进制日志前滚的方式进行恢复。通常来说，这样所需的恢复时间会非常长。为了缩短误操作恢复的时间，通常可以在slave上搭建LVM，通过定期快照的方式来缩短误操作的恢复时间。但是LVM快照的缺点是会对slave的性能产生一定的影响。

官方mysqlbinlog命令为解析MySQL的二进制日志。当二进制日志的格式为ROW格式时，可以输出每个操作的每条记录的前项与后项。那么通过逆操作即可进行回滚操作，例如：

1
2
3
4
5
6
7
8
原始操作：INSERT INTO ...
flashback操作：DELETE ...
  
原始操作：DELETE FROM ...
flashback操作：INSERT INTO ...
  
原始操作：UPDATE XXX SET OLD_VALUES ...
flashback操作：UPDATE XXX SET NEW_VALUES ...

目前flashback功能还没有集成于官方mysqlbinlogg命令。于是自己开发了一套 MySQL Flashback 工具 my2fback

介绍

my2fback 实现了基于row格式binlog的回滚闪回功能，让误删除或者误更新数据，可以不停机不使用备份而快速回滚误操作。也可以解释binlog（支持非row格式binlog）生成易读的SQL。可以按配置输出各个表的update/insert/delete统计报表， 也会输出大事务与长事务的分析， 应用是否干了坏事一目了然， 也会输出所有DDL。

1
2
3
4
5
cat my.cnf
[mysqld]
##### 添加:
binlog_format            = ROW
binlog_row_image         = FULL

my2fback 连接数据库帐号的权限:

  • MySQL5.6/MariaDB10.1/MariaDB10.2版本
1
mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, PROCESS ON *.* TO 'user'@'localhost' IDENTIFIED BY 'xxxxxx';
  • MySQL5.7版本
    1
    2
    mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'xxxxxx';
    mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, PROCESS ON *.* TO 'user'@'localhost';

my2fback 通过解释mysql/mariadb binlog/relaylog实现以下三大功能:

  1. flashback/闪回/回滚， DML回滚到任意时间或者位置。

    • 生成的文件名为rollback.xxx.sql或者db.tb.rollback.xxx.sql
    • 生成的SQL形式如下
      1
      2
      3
      4
      begin
      DELETE FROM `test`.`t1` WHERE `id`=1
      # datetime=2019-06-15_16:23:58 database=test table=t1 binlog=mysql-bin.000012 startpos=417 stoppos=575
      commit

  2. 前滚，把binlog/relaylog的DML解释成易读的SQL语句。

    • 支持非row格式的binlog， 默认不解释非row格式的DML， 需要指定参数 -stsql
    • 生成的文件名为forward.xxx.sql或者db.tb.forward.xxx.sql
    • 生成的SQL形式如下
      1
      2
      3
      4
      begin
      # datetime=2019-06-15_16:23:58 database=test table=t1 binlog=mysql-bin.000012 startpos=417 stoppos=575
      INSERT INTO `test`.`t1` (`id`,`name`,`sr`,`icon`,`points`,`sa`,`sex`) VALUES (1,'张三1','华南理工大学&SCUT',X'89504e47',1.1,1.1,1)
      commit

  3. 输出row格式下的原始SQL（5.7）

    • 结果文件名为original_sql.binlogxxx.sql

  4. 统计分析， 输出各个表的DML统计， 输出大事务与长事务， 输出所有的DDL

    • DML统计结果文件：binlog_status.txt
    • 大事务与长事务结果文件：binlog_biglong_trx.txt
    • DDL结果文件：ddl_info.txt

特点

  1. 支持V4版本的binlog， 支持传统与GTID的binlog， 支持mysql5.1与mairiadb5.5及以上版本的binlog， 也同样支持relaylog(结果中注释的信息binlog=xxx startpos=xxx stoppos=xx是对应的主库的binlog信息)
    • –mtype=mariadb
  2. 支持以时间及位置条件过滤， 并且支持单个以及多个连续binlog的解释。
    • 区间范围为左闭右开， [-sxx， -exxx)
    • 解释binlog的开始位置：
      • -sbin mysql-bin.000101
      • -spos 4
    • 解释binlog的结束位置：
      • -ebin mysql-bin.000105
      • -epos 4
    • 解释binlog的开始时间
      • -sdt “2019-06-15 00:00:00”
    • 解释binlog的结束时间
    • -edt “2019-06-15 11:00:00”
  3. 支持以库及表条件过滤, 以逗号分隔
    • 支持正则表达式，如-dbs “db\d+,db_sh\d+”。正则表达式中请使用小写字母，因为数据库名与表名会先转成小写再与正则表达式进行匹配
  • -dbs db1,db2
  • -tbs tb1,tb2
  1. 支持以DML类型(update,delete,insert)条件过滤
    • -sql delete,update
  2. 支持分析本地binlog，也支持复制协议， my2fback作为一个从库从主库拉binlog来本地解释
    • -m file //解释本地binlog
    • -m repl //my2fback作为slave连接到主库拉binlog来解释
  3. 输出的结果支持一个binlog一个文件， 也可以一个表一个文件
    • -f
      • 例如对于binlog mysql-bin.000101, 如果一个表一个文件， 则生成的文件形式为
    • db.tb.rollback.101.sql(回滚)
    • db.tb.forward.101.sql(前滚)，
      • 否则是rollback.101.sql(回滚),forward.101.sql(前滚)
  4. 输出的结果是大家常见的易读形式的SQL，支持表名前是否加数据库名
    • -d
      1
      2
      3
      4
      5
      6
      begin
      # datetime=2019-06-15_00:14:34 database=test table=t1 binlog=mysql-bin.000012 startpos=21615 stoppos=22822
      UPDATE `test`.`t1` SET `sa`=1001 WHERE `id`=5;
      # datetime=2019-06-15_00:14:45 database=test table=t1 binlog=mysql-bin.000012 startpos=22822 stoppos=23930
      UPDATE `test`.`t1` SET `name`=null WHERE `id`=5;
      commit

否则为

1
2
3
4
5
6
begin
# datetime=2019-06-15_00:14:34 database=test table=t1 binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `t1` SET `sa`=1001 WHERE `id`=5;
# datetime=2019-06-15_00:14:45 database=test table=t1 binlog=mysql-bin.000012 startpos=22822 stoppos=23930
UPDATE `t1` SET `name`=null WHERE `id`=5;
commit

  1. 输出结果支持是否保留事务
    • -k
      1
      2
      3
      4
      5
      6
      begin
      # datetime=2019-06-15_00:14:34 database=test table=t1 binlog=mysql-bin.000012 startpos=21615 stoppos=22822
      UPDATE `test`.`t1` SET `sa`=1001 WHERE `id`=5;
      # datetime=2019-06-15_00:14:45 database=test table=t1 binlog=mysql-bin.000012 startpos=22822 stoppos=23930
      UPDATE `test`.`t1` SET `name`=null WHERE `id`=5;
      commit

不保留则是这样：

1
2
3
4
# datetime=2019-06-15_00:14:34 database=test table=t1 binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `test`.`t1` SET `sa`=1001 WHERE `id`=5;
# datetime=2019-06-15_00:14:45 database=test table=t1 binlog=mysql-bin.000012 startpos=22822 stoppos=23930
UPDATE `test`.`t1` SET `name`=null WHERE `id`=5;

如果复制因为特别大的事务而中断， 则可以以不保留事务的形式生成前滚的SQL, 在从库上执行， 然后跳过这个事务， 再启动复制， 免去重建从库的
麻烦， 特别是很大的库

  1. 支持输出是否包含时间与binlog位置信息
  • -e
  • 包含额外的信息则为
    1
    2
    3
    4
    # datetime=2019-06-15_00:14:34 database=test table=t1 binlog=mysql-bin.000012 startpos=21615 stoppos=22822
    UPDATE `test`.`t1` SET `sa`=1001 WHERE `id`=5;
    # datetime=2019-06-15_00:14:45 database=test table=t1 binlog=mysql-bin.000012 startpos=22822 stoppos=23930
    UPDATE `test`.`t1` SET `name`=null WHERE `id`=5;

否则为

1
2
UPDATE `test`.`t1` SET `sa`=1001 WHERE `id`=5;
UPDATE `test`.`t1` SET `name`=null WHERE `id`=5;

  1. 支持生成的SQL只包含最少必须的字段, 前提下是表含有主键或者唯一索引

  • 默认为

    1
    2
    UPDATE `test`.`t1` SET `sa`=1001 WHERE `id`=5;
    DELETE FROM `test` WHERE `id`=5;

  • -a 则为

    1
    2
    UPDATE `test`.`t1` SET `id`=5, `age`=21, `sex`='M',`sa`=1001, `name`='test' WHERE `id`=5 and `age`=21 and `sex`='M' and `sa`=900 and `name`='test';
    DELETE FROM `test` WHERE `id`=5 and `age`=21 and `sex`='M' and `sa`=900 and `name`='test';
  1. 支持优先使用唯一索引而不是主键来构建where条件
    • -U
    • 有时不希望使用主健来构建wheret条件， 如发生双写时， 自增主健冲突了， 这时使用非主健的唯一索引来避免生成的SQL主健冲突
  2. 支持生成的insert语句不包含主健
    • -I
      • 发生双写时， 自增主健冲突了， 这时使用这个参数来让生成的insert语句不包括主健来避免生成的SQL主健冲突

  3. 支持大insert拆分成小insert语句。

    • -r 100
    • 对于一个insert 1000行的插入， 会生成10个insert语句，每个语句插入100行

  4. 支持非row格式binlog的解释

    • 当-w 2sql时加上参数-stsql，则会解释非row格式的DML语句。
    • 由于不是支持所有要SQL， 如create trigger就不支持， 遇到SQL无法解释时会报错退出， 如需要跳过该SQL并继续解释， 请使用参数-ies。-ies 后接正则表达式，
    • 解释错误或者无法解释的SQL如果匹配-ies指定的正则表达式， 则my2fback不会退出而是跳过该SQL继续解释后面的binlog， 否则错误退出。
    • -ies 后接的正则表达式请使用小写字母, my2fback会先把SQL转成小写再与之匹配。

安装与下载

安装

如果需要编译， 请使用GO>=1.11.x版本来编译。

  • 开启GO111MODULE参数

    • 编译linux 平台

      1
      CGO_ENABLED=0 GOOS=linux GOARCH=amd64 go build -o releases/my2fback -ldflags "-s -w" main.go

    • 编译windows 平台

      1
      CGO_ENABLED=0 GOOS=windows GOARCH=amd64 go build -o releases/my2fback -ldflags "-s -w" main.go

  • 没有开启GO111MODULE参数

    • 编译linux 平台

      1
      CGO_ENABLED=0 GOOS=linux GOARCH=amd64 go build -o releases/my2fback -ldflags "-s -w" main.go

    • 编译windows 平台

      1
      CGO_ENABLED=0 GOOS=windows GOARCH=amd64 go build -o releases/my2fback -ldflags "-s -w" main.go

下载

使用帮助

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
test_dbs2 ~ # /usr/local/bin/my2fback -h
my2fback V2.0 By WangJiemin.
	E_mail: 278667010@qq.com

*****************************************************************************************************
*	system_command: /usr/local/bin/my2fback																		*
*	system_goos: linux																			*
*	system_arch: amd64																			*
*	hostname: test_dbs2.yz.babytree-ops.org																			*
*	hostaddress: 10.10.1.221																			*
*	blog: https://jiemin.wang																					*
*		read binlog from master, work as a fake slave: ./my2fback -m repl opts...					*
*		read binlog from local filesystem: ./my2fback -m file opts... mysql-bin.000010				*
*****************************************************************************************************

  -C	works with -w='stats', keep analyzing transations to last binlog for -m=file, and keep analyzing for -m=repl
  -H string
	master host, DONOT need to specify when -w=stats. if mode is file, it can be slave or other mysql contains same schema and table structure, not only master. default 127.0.0.1 (default "127.0.0.1")
  -I	for insert statement when -wtype=2sql, ignore primary key
  -M string
	valid options are:  mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")
  -P uint
	master port, default 3306. DONOT need to specify when -w=stats (default 3306)
  -S string
	mysql socket file
  -U	prefer to use unique key instead of primary key to build where condition for delete/update sql
  -a	Works with -w=2sql|rollback. 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
  -b int
	transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 10 to 30000, default 500 (default 500)
  -d	Works with -w=2sql|rollback. Prefix table name with database name in sql, ex: insert into db1.tb1 (x1, x1) values (y1, y1). Default true (default true)
  -dbs string
	only parse database which match any of these regular expressions. The regular expression should be in lower case because database name is translated into lower case and then matched against it.
		Multi regular expressions is seperated by comma, default parse all databases. Useless when -w=stats
  -dj string
	dump table structure to this file. default tabSchame.json (default "tabSchame.json")
  -e	Works with -w=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
  -ebin string
	binlog file to stop reading
  -edt string
	Stop reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2004-12-25 11:25:56"
  -epos uint
	Stop reading the binlog at position
  -f	Works with -w=2sql|rollback. one file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
  -i int
	works with -w='stats', print stats info each PrintInterval. Valid values range from 1 to 600, default 30 (default 30)
  -ies string
	for sql which is error to parsed and matched by this regular expression, just print error info, skip it and continue parsing, otherwise stop parsing and exit.
		The regular expression should be in lower case, because sql is translated into lower case and then matched against it. (default "^create definer.+trigger")
  -k	Works with -w=2sql|rollback. wrap result statements with 'begin...commit|rollback'
  -l int
	transaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 1 to 3600, default 300 (default 300)
  -m string
	valid options are:  repl,file. repl: as a slave to get binlogs from master. file: get binlogs from local filesystem. default file (default "file")
  -mid uint
	works with -m=repl, this program replicates from master as slave to read binlogs. Must set this server id unique from other slaves, default 1113306 (default 1113306)
  -o string
	result output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space
  -oj
	Only use table structure from -rj, do not get or merge table struct from mysql
  -ors
	for mysql>=5.6.2 and binlog_rows_query_log_events=on, if set, output original sql. default false
  -p string
	mysql user password. DONOT need to specify when -w=stats
  -r int
	Works with -w=2sql|rollback. rows for each insert sql. Valid values range from 1 to 500, default 30 (default 30)
  -rj string
	Works with -w=2sql|rollback, read table structure from this file and merge from mysql
  -sbin string
	binlog file to start reading
  -sdt string
	Start reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2004-12-25 11:25:56"
  -spos uint
	start reading the binlog at position
  -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)
  -stsql
	when -w=2sql, also parse plain sql and write into result file even if binlog_format is not row. default false
  -t uint
	Works with -w=2sql|rollback. threads to run, default 4 (default 2)
  -tbs string
	only parse table which match any of these regular expressions.The regular expression should be in lower case because database name is translated into lower case and then matched against it.
		 Multi regular expressions is seperated by comma, default parse all tables. Useless when -w=stats
  -tl string
	time location to parse timestamp/datetime column in binlog, such as Asia/Shanghai. default Local (default "Local")
  -u string
	mysql user. DONOT need to specify when -w=stats
  -v	print version
  -w string
	valid options are:  tbldef,stats,2sql,rollback. tbldef: only get table definition structure; 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: stats (default "stats")
test_dbs2 ~ #

常用参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-m string
valid options are:  repl,file. repl: as a slave to get binlogs from master. file: get binlogs from local filesystem. default file (default "file")
		relp: 模仿 SLAVE 的IO_THREAD连接到MASTER获取BINLOG EVENT
		file: 解析本地的BINLOG(default: file)
-w string
valid options are:  tbldef,stats,2sql,rollback. tbldef: only get table definition structure; 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: stats (default "stats")
		2sql: 解析成SQL语句
		rollback: 解析为回滚语句
-M string
valid options are:  mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")
		选择是MySQL还是Mariadb, 不选择默认为MySQL
-e  Works with -w=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
 在sql之前的行上打印database/table/datetime/binlogposition...info，默认为false
-f  Works with -w=2sql|rollback. one file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
 如果为true，则为一个表的一个文件，否则为所有表的一个文件。默认为false。注意，一个binlog总是一个文件
-r int
Works with -w=2sql|rollback. rows for each insert sql. Valid values range from 1 to 500, default 30 (default 30)
		INSERT SQL 语句每一行包含的values的行数
-t uint
Works with -w=2sql|rollback. threads to run, default 4 (default 2)
		开启几个thread进行来执行解析2sql|rollback
-o string
result output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space
		输入的目录
-k  Works with -w=2sql|rollback. wrap result statements with 'begin...commit|rollback'
 使用-w = 2sql | rollback。使用'begin ... commit | rollback'包装结果语句
-l int
transaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 1 to 3600, default 300 (default 300)
 
-b int
transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 10 to 30000, default 500 (default 500)
		
-dWorks with -w=2sql|rollback. Prefix table name with database name in sql, ex: insert into db1.tb1 (x1, x1) values (y1, y1). Default true (default true)
 使用-w=2sql|rollback。在sql中具有数据库名称的前缀表名

例子

  • 测试机器

    • CPU 2c
    • 内存 4G
    • 磁盘 400G

  • file本地方式解析binlog

    1
    test_dbs2 ~ # time /usr/local/bin/my2fback -m file -w 2sql -M mysql -t 6 -H ***.***.***.*** -u test -p test -dbs babytree -tbs userbaby -e -f -d -r 20 -k -b 100 -l 10  -o /data/bak/20190626/tosql /data/bak/20190626/mysql-bin.002938
1
test_dbs2 ~ # ls /data/bak/20190626/tosql/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
test_dbs2 ~ # cat /data/bak/20190626/tosql/babytree.UserBaby.forward.2938.sql |more
commit;
begin;
# datetime=2019-06-26_09:35:18 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=79686 stoppos=80034
UPDATE `babytree`.`UserBaby` SET `name`='双双', `update_ts`=1561512918, `extra`='{\"born_preg_week\":30,\"born_preg_day\":5,\"is_premature\":0,\"born_height\":0,\"born_weight\":0,\"is_only_child\":0}' WHE
RE `id`=83758158;
commit;
begin;
# datetime=2019-06-26_09:35:18 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=121354 stoppos=121552
UPDATE `babytree`.`UserBaby` SET `birthday`=1582905600, `gender`=3, `update_ts`=1561512918, `extra`='{\"is_premature\":0}' WHERE `id`=87162805;
commit;
begin;
# datetime=2019-06-26_09:35:18 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=128088 stoppos=128234
INSERT INTO `babytree`.`UserBaby` (`id`,`user_id`,`name`,`birthday`,`gender`,`is_default`,`state`,`photo_url`,`text_info`,`create_ts`,`update_ts`,`baby_status`,`extra`) VALUES (87163008,87041217,'',0,1,1,
1,'','',1561512918,1561512918,1,'');
commit;
begin;
# datetime=2019-06-26_09:35:19 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=159954 stoppos=160134
UPDATE `babytree`.`UserBaby` SET `gender`=0, `update_ts`=1561512919 WHERE `id`=87163008;
commit;
begin;
# datetime=2019-06-26_09:35:19 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=251027 stoppos=251207
UPDATE `babytree`.`UserBaby` SET `update_ts`=1561512919 WHERE `id`=87163007;
commit;
begin;
# datetime=2019-06-26_09:35:20 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=342791 stoppos=342971
UPDATE `babytree`.`UserBaby` SET `update_ts`=1561512920 WHERE `id`=87163008;
commit;
begin;
# datetime=2019-06-26_09:35:22 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=607938 stoppos=608256
UPDATE `babytree`.`UserBaby` SET `birthday`=1560268800, `gender`=1, `update_ts`=1561512922, `baby_status`=3, `extra`='{\"born_height\":0,\"born_weight\":0,\"is_premature\":0,\"born_preg_week\":39,\"born_p
reg_day\":0}' WHERE `id`=81204050;
commit;
begin;
# datetime=2019-06-26_09:35:22 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=628699 stoppos=628897
UPDATE `babytree`.`UserBaby` SET `birthday`=-192551296, `gender`=3, `update_ts`=1561512922, `extra`='{\"is_premature\":0}' WHERE `id`=87162871;
commit;
begin;
# datetime=2019-06-26_09:35:22 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=631950 stoppos=632130
UPDATE `babytree`.`UserBaby` SET `update_ts`=1561512922 WHERE `id`=84567514;
commit;
  • file本地方式回滚binlog
    1
    est_dbs2 ~ # time /usr/local/bin/my2fback -m file -w rollback -M mysql -t 6 -H ***.***.***.*** -u test -p test -dbs babytree -tbs userbaby -e -f -d -r 20 -k -b 100 -l 10  -o /data/bak/20190626/tosql /data/bak/20190626/mysql-bin.002938

-w 修改为 rollback


看上面解析的SQl,用grep 进行搜索下，是不是回滚对了。还有一定要和业务方确认，业务方确认，业务方确认。重要的事情说三遍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
test_dbs2 ~ # cat /data/bak/20190626/tosql/babytree.UserBaby.rollback.2938.sql|grep -C 2 83758158
commit;
begin;
UPDATE `babytree`.`UserBaby` SET `name`='', `update_ts`=1548426675, `extra`='{\"born_preg_week\":30,\"born_preg_day\":5,\"is_premature\":0}' WHERE `id`=83758158;
# datetime=2019-06-26_09:35:18 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=79686 stoppos=80034
commit;
test_dbs2 ~ # cat /data/bak/20190626/tosql/babytree.UserBaby.rollback.2938.sql|grep -C 2 87163008
commit;
begin;
UPDATE `babytree`.`UserBaby` SET `birthday`=0, `gender`=0, `update_ts`=1561512920, `baby_status`=1, `extra`='' WHERE `id`=87163008;
# datetime=2019-06-26_09:36:41 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=9292334 stoppos=9292532
commit;
--
commit;
begin;
UPDATE `babytree`.`UserBaby` SET `update_ts`=1561512919 WHERE `id`=87163008;
# datetime=2019-06-26_09:35:20 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=342791 stoppos=342971
commit;
--
commit;
begin;
UPDATE `babytree`.`UserBaby` SET `gender`=1, `update_ts`=1561512918 WHERE `id`=87163008;
# datetime=2019-06-26_09:35:19 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=159954 stoppos=160134
commit;
begin;
DELETE FROM `babytree`.`UserBaby` WHERE `id`=87163008;
# datetime=2019-06-26_09:35:18 database=babytree table=UserBaby binlog=mysql-bin.002938 startpos=128088 stoppos=128234
commit;

  • relp 方式解析binlog
  • 数据库上的binlog文件起始文件和结束文件 
1
test_dbs2 ~ # time /usr/local/bin/my2fback -m repl -w 2sql -M mysql -t 6 -H ***.***.***.*** -u test -p test -dbs babytree -tbs userbaby -e -f -d -r 20 -k -b 100 -l 10 -sbin mysql-bin.003045 -spos 4 -ebin mysql-bin.003079 -epos 4 -o /data/bak/20190626/tosql/

系统资源

执行截图

binlog文件共计有36个。文件大小为36G。解析时间:57m24.978s

  • repl 方式回滚binlog
    1
    test_dbs2 ~ # time /usr/local/bin/my2fback -m repl -w rollback -M mysql -t 6 -H ***.***.***.*** -u test -p test -dbs babytree -tbs userbaby -e -f -d -r 20 -k -b 100 -l 10 -sbin mysql-bin.003045 -spos 4 -ebin mysql-bin.003045 -epos 888888 -o /data/bak/20190626/tosql/

就不贴图了。和file方式一致。

解析文件可以是一个时间范围，也可以是一个positiions范围。

最后

一个小菜鸟撸的菜鸟工具，如果有BUG。请大家多多包容。

测试通过，已经在宝宝树线上使用。累计恢复数据120G。

如果不放心。请使用binlog2sql。

-------------本文结束感谢您的阅读-------------

本文标题:MySQL/MariaDB binlog/relaylog 回滚/闪回,前滚,DML分析报告,DDL信息

文章作者:Wang Jiemin

发布时间:2019年07月17日 - 16:07

最后更新:2019年08月15日 - 11:08

原始链接:https://jiemin.wang/2019/07/17/my2fback/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

0%