一、简介

MyFlash是由美团点评公司技术工程部开发维护的一个回滚DML操作的工具。该工具通过解析v4版本的binlog，完成回滚操作。相对已有的回滚工具，其增加了更多的过滤选项，让回滚更加容易。该工具已经在美团点评内部使用

二、限制

binlog格式必须为row,且binlog_row_image=full

仅支持5.6与5.7

只能回滚DML（增、删、改）

三、安装测试

3.1 安装

下载：git clone https://github.com/Meituan-Dianping/MyFlash.gitcd MyFlash/编译：1）动态编译gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback2）静态编译gcc -w -g `pkg-config --cflags  glib-2.0` source/binlogParseGlib.c   -o binary/flashback /usr/lib64/libglib-2.0.a -lrt为了保证在一台机器上编译后，可以在其它机器上使用，需要满足以下两个条件 a) 将glib做成静态链接 b）在编译的那台机器的glibc版本（查看方法为ldd --version）要小于等于要运行该软件的那台机器glibc版本 因此需要你在一台glibc版本较低的机器上运行如下命令即可。使用：[root@offline02 MyFlash]# cd binary/[root@offline02 binary]# lsflashback  mysqlbinlog20160408[root@offline02 binary]# ./flashback --helpUsage:  flashback [OPTION...]Help Options:  -h, --help                  Show help optionsApplication 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

3.2 用法

1）回滚整个文件

./flashback --binlogFileNames=haha.000041mysqlbinlog binlog_output_base.flashback | mysql -h
    
      -u
     
       -p

2）回滚该文件中的所有insert语句

./flashback  --sqlTypes='INSERT' --binlogFileNames=haha.000041mysqlbinlog binlog_output_base.flashback | mysql -h
    
      -u
     
       -p

3）回滚大文件

回滚./flashback --binlogFileNames=haha.000042切割大文件./flashback --maxSplitSize=1 --binlogFileNames=binlog_output_base.flashback应用mysqlbinlog binlog_output_base.flashback.000001 | mysql -h
    
      -u
     
       -p...mysqlbinlog binlog_output_base.flashback.
      
        | mysql -h
       
         -u
        
          -p

3.3 测试

创建表结构

CREATE TABLE `testFlashback2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `nameShort` varchar(20) DEFAULT NULL,  `nameLong` varchar(260) DEFAULT NULL,  `amount` decimal(19,9) DEFAULT NULL,  `amountFloat` float DEFAULT NULL,  `amountDouble` double DEFAULT NULL,  `createDatetime6` datetime(6) DEFAULT NULL,  `createDatetime` datetime DEFAULT NULL,  `createTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `nameText` text,  `nameBlob` blob,  `nameMedium` mediumtext,  PRIMARY KEY (`id`)) ENGINE=InnoDB

3.3.1 插入回滚

flush logsinsert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');flush logs;./binary/flashback --binlogFileNames=/var/lib/mysql/haha.000048  在当前运行目录下产生binlog_output_base.flashback文件mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql --socket=/var/lib/mysql/mysql.sock test  执行结果mysql> select * from testFlashback2;Empty set (0.00 sec)

3.3.2 删除回滚

delete from testFlashback2;insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');flush logs;delete from testFlashback2;./binary/flashback --binlogFileNames=/var/lib/mysql/haha.000050  在当前运行目录下产生binlog_output_base.flashback文件mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql --socket=/var/lib/mysql/mysql.sock test  执行结果+----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+| id | nameShort | nameLong                                                                                                                                                                                                      | amount       | amountFloat | amountDouble | createDatetime6            | createDatetime      | createTimestamp     | nameText | nameBlob | nameMedium |+----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+|  4 | aaa       | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb | 10.500000000 |        10.6 |         10.7 | 2017-10-26 10:00:00.000000 | 2017-10-26 10:00:00 | 2017-10-26 10:00:00 | cccc     | dddd     | eee        |+----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+

3.3.3 更新回滚

delete from testFlashback2;insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.111,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');flush logs;mysql> checksum table testFlashback2;+---------------------+-----------+| Table               | Checksum  |+---------------------+-----------+| test.testFlashback2 | 717087411 |+---------------------+-----------+update testFlashback2 set amount=10.222;mysql> checksum table testFlashback2;+---------------------+------------+| Table               | Checksum   |+---------------------+------------+| test.testFlashback2 | 3797190846 |+---------------------+------------+./binary/flashback --binlogFileNames=/var/lib/mysql/haha.000052mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql --socket=/var/lib/mysql/mysql.sock testmysql> checksum table testFlashback2;+---------------------+-----------+| Table               | Checksum  |+---------------------+-----------+| test.testFlashback2 | 717087411 |+---------------------+-----------+

参考：

https://github.com/Meituan-Dianping/MyFlash

为了方便大家交流，本人开通了微信公众号和QQ群，QQ群：291519319，喜欢技术的一起来交流吧

转载于:https://my.oschina.net/u/3023401/blog/1793437

你可能感兴趣的文章

6天面试、斩获6家硅谷巨头Offer，我是如何做到的？