一、简介
MyFlash是由美团点评公司技术工程部开发维护的一个回滚DML操作的工具。该工具通过解析v4版本的binlog,完成回滚操作。相对已有的回滚工具,其增加了更多的过滤选项,让回滚更加容易。 该工具已经在美团点评内部使用
二、限制
binlog格式必须为row,且binlog_row_image=full
仅支持5.6与5.7
只能回滚DML(增、删、改)
三、工具安装
git clone https://github.com/Meituan-Dianping/MyFlash.git
cd MyFlash/
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
yum install glib2 #没有glib-2.0,安装
cd binary
./flashback --help
Usage:
flashback [OPTION...]
Help Options:
-?, --help Show help options
Application Options:
--databaseNames databaseName to apply. if multiple, seperate by comma(,)
--tableNames tableName to apply. if multiple, seperate by comma(,)
--start-position start position
--stop-position stop position
--start-datetime start time (format %Y-%m-%d %H:%M:%S)
--stop-datetime stop time (format %Y-%m-%d %H:%M:%S)
--sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
--maxSplitSize max file size after split, the uint is M
--binlogFileNames binlog files to process. if multiple, seperate by comma(,)
--outBinlogFileNameBase output binlog file name base
--logLevel log level, available option is debug,warning,error
--include-gtids gtids to process
--exclude-gtids gtids to skip
Parameter explantion
下面的这些参数是可以任意组合的。
1.databaseNames
指定需要回滚的数据库名。多个数据库可以用“,”隔开。如果不指定该参数,相当于指定了所有数据库。
2.tableNames
指定需要回滚的表名。多个表可以用“,”隔开。如果不指定该参数,相当于指定了所有表。
3.start-position
指定回滚开始的位置。如不指定,从文件的开始处回滚。请指定正确的有效的位置,否则无法回滚
4.stop-position
指定回滚结束的位置。如不指定,回滚到文件结尾。请指定正确的有效的位置,否则无法回滚
5.start-datetime
指定回滚的开始时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定,则不限定时间
6.stop-datetime
指定回滚的结束时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定,则不限定时间
7.sqlTypes
指定需要回滚的sql类型。目前支持的过滤类型是INSERT, UPDATE ,DELETE。多个类型可以用“,”隔开。
8.maxSplitSize
一旦指定该参数,对文件进行固定尺寸的分割(单位为M),过滤条件有效,但不进行回滚操作。该参数主要用来将大的binlog文件切割,防止单次应用的binlog尺寸过大,对线上造成压力
9.binlogFileNames
指定需要回滚的binlog文件,目前只支持单个文件,后续会增加多个文件支持
10.outBinlogFileNameBase
指定输出的binlog文件前缀,如不指定,则默认为binlog_output_base.flashback
11.logLevel
仅供开发者使用,默认级别为error级别。在生产环境中不要修改这个级别,否则输出过多
12.include-gtids
指定需要回滚的gtid,支持gtid的单个和范围两种形式。
13.exclude-gtids
指定不需要回滚的gtid,用法同include-gtids
delete 恢复测试:
1.准备数据
mysql> select * from travelrecord limit 10;
id | user_id | traveldate | fee | days |
---|---|---|---|---|
10017 | xiaohong8 | NULL | 522 | 8 |
10018 | xiaohong8 | NULL | 522 | 8 |
10020 | xiaohong8 | NULL | 522 | 8 |
10021 | xiaohong8 | NULL | 522 | 8 |
10023 | xiaohong8 | NULL | 522 | 8 |
100000 | xiaohong | 2014-01-06 | 511 | 3 |
100002 | xiaohong2 | 2014-01-12 | 511 | 3 |
100003 | xiaohong3 | 2014-01-13 | 511 | 3 |
100005 | xiaohong4 | 2014-01-14 | 511 | 5 |
100006 | xiaohong6 | 2014-01-14 | 511 | 6 |
10 rows in set (0.00 sec)
2.删除前5个数据
mysql> delete from travelrecord where id=10017;
Query OK, 1 row affected (0.00 sec)
mysql> delete from travelrecord where id=10018;
Query OK, 1 row affected (0.00 sec)
mysql> delete from travelrecord where id=10019;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from travelrecord where id=10020;
Query OK, 1 row affected (0.00 sec)
mysql> delete from travelrecord where id=10021;
Query OK, 1 row affected (0.00 sec)
mysql> delete from travelrecord where id=10023;
Query OK, 1 row affected (0.00 sec)
mysql> select * from travelrecord limit 10;
id | user_id | traveldate | fee | days |
---|---|---|---|---|
100000 | xiaohong1 | 2014-01-06 | 511 | 3 |
100002 | xiaohong2 | 2014-01-12 | 511 | 3 |
100003 | xiaohong3 | 2014-01-13 | 511 | 3 |
100005 | xiaohong4 | 2014-01-14 | 511 | 5 |
100006 | xiaohong6 | 2014-01-14 | 511 | 6 |
5 rows in set (0.00 sec)
3.查看binlog日志,确定start stop position
[root@mydb1 mysql_data]# mysqlbinlog bin.000056 –base64-output=decode-rows -v
# at 12961
#171215 21:42:30 server id 11 end_log_pos 13035 CRC32 0x1198e401 Query thread_id=762 exec_time=0 error_code=0
SET TIMESTAMP=1513345350/*!*/;
BEGIN
/*!*/;
# at 13035
#171215 21:42:30 server id 11 end_log_pos 13100 CRC32 0xe30d86d6 Table_map: `testdb`.`travelrecord` mapped to number 113
# at 13100
#171215 21:42:30 server id 11 end_log_pos 13164 CRC32 0x8f6f5c98 Delete_rows: table id 113 flags: STMT_END_F
### DELETE FROM `testdb`.`travelrecord`
### WHERE
### @1=10017
### @2='xiaohong8'
### @3=NULL
### @4=522
### @5=8
# at 13164
#171215 21:42:30 server id 11 end_log_pos 13195 CRC32 0x6c37a6ba Xid = 3308
COMMIT/*!*/;
# at 13195
#171215 21:42:34 server id 11 end_log_pos 13260 CRC32 0x8d601b32 GTID last_committed=44 sequence_number=45
SET @@SESSION.GTID_NEXT= 'c07e1144-3f74-11e6-8405-000c292e6dc1:2377259'/*!*/;
# at 13260
#171215 21:42:34 server id 11 end_log_pos 13334 CRC32 0x76ed46dc Query thread_id=762 exec_time=0 error_code=0
SET TIMESTAMP=1513345354/*!*/;
BEGIN
/*!*/;
# at 13334
#171215 21:42:34 server id 11 end_log_pos 13399 CRC32 0xf874f221 Table_map: `testdb`.`travelrecord` mapped to number 113
# at 13399
#171215 21:42:34 server id 11 end_log_pos 13463 CRC32 0x273c9903 Delete_rows: table id 113 flags: STMT_END_F
### DELETE FROM `testdb`.`travelrecord`
### WHERE
### @1=10018
### @2='xiaohong8'
### @3=NULL
### @4=522
### @5=8
# at 13463
#171215 21:42:34 server id 11 end_log_pos 13494 CRC32 0x6201295c Xid = 3309
COMMIT/*!*/;
# at 13494
#171215 21:42:45 server id 11 end_log_pos 13559 CRC32 0x236b1be3 GTID last_committed=45 sequence_number=46
SET @@SESSION.GTID_NEXT= 'c07e1144-3f74-11e6-8405-000c292e6dc1:2377260'/*!*/;
# at 13559
#171215 21:42:45 server id 11 end_log_pos 13633 CRC32 0x88243fcf Query thread_id=762 exec_time=0 error_code=0
SET TIMESTAMP=1513345365/*!*/;
BEGIN
/*!*/;
# at 13633
#171215 21:42:45 server id 11 end_log_pos 13698 CRC32 0xc18a2803 Table_map: `testdb`.`travelrecord` mapped to number 113
# at 13698
#171215 21:42:45 server id 11 end_log_pos 13762 CRC32 0x0db53947 Delete_rows: table id 113 flags: STMT_END_F
### DELETE FROM `testdb`.`travelrecord`
### WHERE
### @1=10020
### @2='xiaohong8'
### @3=NULL
### @4=522
### @5=8
# at 13762
#171215 21:42:45 server id 11 end_log_pos 13793 CRC32 0xf157ada0 Xid = 3311
COMMIT/*!*/;
# at 13793
#171215 21:42:47 server id 11 end_log_pos 13858 CRC32 0x59e62800 GTID last_committed=46 sequence_number=47
SET @@SESSION.GTID_NEXT= 'c07e1144-3f74-11e6-8405-000c292e6dc1:2377261'/*!*/;
# at 13858
#171215 21:42:47 server id 11 end_log_pos 13932 CRC32 0x00b833b6 Query thread_id=762 exec_time=0 error_code=0
SET TIMESTAMP=1513345367/*!*/;
BEGIN
/*!*/;
# at 13932
#171215 21:42:47 server id 11 end_log_pos 13997 CRC32 0xdc89cbe3 Table_map: `testdb`.`travelrecord` mapped to number 113
# at 13997
#171215 21:42:47 server id 11 end_log_pos 14061 CRC32 0xb8f3404a Delete_rows: table id 113 flags: STMT_END_F
### DELETE FROM `testdb`.`travelrecord`
### WHERE
### @1=10021
### @2='xiaohong8'
### @3=NULL
### @4=522
### @5=8
# at 14061
#171215 21:42:47 server id 11 end_log_pos 14092 CRC32 0x51dc1d7e Xid = 3312
COMMIT/*!*/;
# at 14092
#171215 21:42:51 server id 11 end_log_pos 14157 CRC32 0x4bdd179f GTID last_committed=47 sequence_number=48
SET @@SESSION.GTID_NEXT= 'c07e1144-3f74-11e6-8405-000c292e6dc1:2377262'/*!*/;
# at 14157
#171215 21:42:51 server id 11 end_log_pos 14231 CRC32 0x64d80716 Query thread_id=762 exec_time=0 error_code=0
SET TIMESTAMP=1513345371/*!*/;
BEGIN
/*!*/;
# at 14231
#171215 21:42:51 server id 11 end_log_pos 14296 CRC32 0xd0cd8862 Table_map: `testdb`.`travelrecord` mapped to number 113
# at 14296
#171215 21:42:51 server id 11 end_log_pos 14360 CRC32 0x773f59f0 Delete_rows: table id 113 flags: STMT_END_F
### DELETE FROM `testdb`.`travelrecord`
### WHERE
### @1=10023
### @2='xiaohong8'
### @3=NULL
### @4=522
### @5=8
# at 14360
#171215 21:42:51 server id 11 end_log_pos 14391 CRC32 0xfd2b0a43 Xid = 3313
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
4.恢复id=10020 id=10021 id=10023 三条数据
–start-position=13633
–stop-position=14360
5.生成恢复binlog日志
./flashback –binlogFileNames=/data/mysql_data/bin.000056 –start-position=13633 –stop-position=14360
默认产生一个binlog_output_base.flashback文件
6.应用日志到数据库
mysqlbinlog –skip-gtids binlog_output_base.flashback|mysql -uroot -p111111
7.查看恢复情况
mysql> select * from travelrecord limit 10;
id | user_id | traveldate | fee | days |
---|---|---|---|---|
10020 | xiaohong8 | NULL | 522 | 8 |
10021 | xiaohong8 | NULL | 522 | 8 |
10023 | xiaohong8 | NULL | 522 | 8 |
100000 | xiaohong1 | 2014-01-06 | 511 | 3 |
100002 | xiaohong2 | 2014-01-12 | 511 | 3 |
100003 | xiaohong3 | 2014-01-13 | 511 | 3 |
100005 | xiaohong4 | 2014-01-14 | 511 | 5 |
100006 | xiaohong6 | 2014-01-14 | 511 | 6 |
8 rows in set (0.00 sec)
MyFlash开源地址:https://github.com/Meituan-Dianping/MyFlash