原理介绍请看美团官方博客：     

安装：

环境要求：

1、binlog格式必须为row，且binlog_row_image=full

2、仅支持5.6与5.7

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

4.  下载地址：https://github.com/Meituan-Dianping/MyFlash

 

安装：

unzip MyFlash-master.zipmv MyFlash-master /usr/local/MyFlash/gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback

闪回：

 

update，情景描述，程序1误将xtr库下面的t2表 12,11 更改后0了,这是一个错误的操作;而后面的程序2将13、14更改0了，这是一个正确的操作，需要闪回程序1的误操作。

模似上述场景：

原表：

mysql> select * from xtr.t2;+------+| id  |+------+|  12 ||  11 ||  4  ||  3  ||  13 ||  14 |+------+6 rows in set (0.00 sec)

update操作：

update xtr.t2 set id = 0 where id = 12;update xtr.t2 set id = 0 where id = 11;update xtr.t2 set id = 0 where id = 13;update xtr.t2 set id = 0 where id = 14;

DBA接到开发的报警后：

1、flush logs;

需要闪回的动作所在的二进制文件，这个文件必须是稳定的，需要flush logs;

2、记录当前的binlog文件

3、解析binlog

mysqlbinlog -vv bin.000038|less# at 838#180326 21:41:59 server id 11  end_log_pos 882 CRC32 0x304d887b         Table_map: `xtr`.`t2` mapped to number 268                                         #这个position开始进入xtr.t2库# at 882#180326 21:41:59 server id 11  end_log_pos 928 CRC32 0x8fac8e57         Update_rows: table id 268 flags: STMT_END_FBINLOG 'twa5WhMLAAAALAAAAHIDAAAAAAwBAAAAAAEAA3h0cgACdDIAAQMAAXuITTA=twa5Wh8LAAAALgAAAKADAAAAAAwBAAAAAAEAAgAB///+CwAAAP4AAAAAV46sjw=='/*!*/;### UPDATE `xtr`.`t2`                                                                    #更改的第一条语句### WHERE###   @1=11 /* INT meta=0 nullable=1 is_null=0 */### SET###   @1=0 /* INT meta=0 nullable=1 is_null=0 */# at 928#180326 21:41:59 server id 11  end_log_pos 959 CRC32 0xca19e065         Xid = 230COMMIT/*!*/;# at 959#180326 21:42:04 server id 11  end_log_pos 1024 CRC32 0xe7e195c8         GTID        last_committed=3        sequence_number=4SET @@SESSION.GTID_NEXT= '148e1f5e-befd-11e7-ac58-08002738f0ad:207'/*!*/;# at 1024#180326 21:42:04 server id 11  end_log_pos 1095 CRC32 0x0c3e3fdc         Query        thread_id=3        exec_time=0        error_code=0SET TIMESTAMP=1522075324/*!*/;BEGIN/*!*/;# at 1095#180326 21:42:04 server id 11  end_log_pos 1139 CRC32 0xd6cdd96b         Table_map: `xtr`.`t2` mapped to number 268# at 1139#180326 21:42:04 server id 11  end_log_pos 1185 CRC32 0xbd63762d         Update_rows: table id 268 flags: STMT_END_F BINLOG 'vAa5WhMLAAAALAAAAHMEAAAAAAwBAAAAAAEAA3h0cgACdDIAAQMAAWvZzdY=vAa5Wh8LAAAALgAAAKEEAAAAAAwBAAAAAAEAAgAB///+DAAAAP4AAAAALXZjvQ=='/*!*/;### UPDATE `xtr`.`t2`                              #更改的第二条语句### WHERE###   @1=12 /* INT meta=0 nullable=1 is_null=0 */### SET###   @1=0 /* INT meta=0 nullable=1 is_null=0 */# at 1185#180326 21:42:04 server id 11  end_log_pos 1216 CRC32 0xb62d1a59         Xid = 231COMMIT/*!*/;# at 1216                                         #两个事务结束的position点位置

4、./flashback --sqlTypes='UPDATE' --binlogFileNames=/data/mysql/bin.000038  --start-position=838 --stop-position=1216 --outBinlogFileNameBase=update

反解析UPDATE操作，生成以update开头的文件

生成的文件名：binlog_output_base.flashback

 

5、 mysqlbinlog binlog_output_base.flashback --skip-gtids |mysql -uroot -p'123' -S /tmp/mysql_3306.sock

导入数据库

 

6、查询数据库

mysql> select * from xtr.t2;+------+| id   |+------+|   12 ||   11 ||    4 ||    3 ||    0 ||    0 |+------+6 rows in set (0.00 sec)

闪回完成

 

insert 场景闪回

原表：

mysql> select * from xtr.t2;+------+| id   |+------+|   12 ||   11 ||    4 ||    3 ||    0 ||    0 |+------+6 rows in set (0.00 sec)

插入数据

mysql> insert into xtr.t2 values(99);Query OK, 1 row affected (0.00 sec)mysql> insert into xtr.t2 values(88);Query OK, 1 row affected (0.00 sec)mysql> insert into xtr.t2 values(77);Query OK, 1 row affected (0.00 sec)mysql> select * from xtr.t2;+------+| id   |+------+|   12 ||   11 ||    4 ||    3 ||    0 ||    0 ||   99 ||   88 ||   77 |+------+9 rows in set (0.00 sec) mysql> delete from xtr.t2 where id = 0;Query OK, 2 rows affected (0.03 sec) mysql> select * from xtr.t2;+------+| id   |+------+|   12 ||   11 ||    4 ||    3 ||   99 ||   88 ||   77 |+------+7 rows in set (0.00 sec)

现在需要闪回insert的操作，id=99,id=99,id=77的全部的闪回

 

mysql> show master logs;+------------+-----------+| Log_name   | File_size |+------------+-----------+ || bin.000036 |      1759 || bin.000037 |       749 || bin.000038 |      1771 || bin.000039 |       217 || bin.000040 |      2979 |+------------+-----------+17 rows in set (0.00 sec) mysql> flush logs;Query OK, 0 rows affected (0.08 sec) ./flashback --binlogFileNames=/data/mysql/bin.000040 --sqlTypes='INSERT' --start-position=1851 --stop-position=2462 --outBinlogFileNameBase=insert mysqlbinlog insert.flashback --skip-gtids |mysql -uroot -p'123' -S /tmp/mysql_3306.sock mysql> select * from xtr.t2;+------+| id   |+------+|   12 ||   11 ||    4 ||    3 |+------+4 rows in set (0.00 sec)

insert 闪回完成

 

可以用mysqlbinlog查看insert.flashback文件：

### DELETE FROM `xtr`.`t2`### WHERE###   @1=77 /* INT meta=0 nullable=1 is_null=0 */# at 207#180327 21:20:37 server id 11  end_log_pos 251 CRC32 0xb6c9ac45         Table_map: `xtr`.`t2` mapped to number 268# at 251#180327 21:20:37 server id 11  end_log_pos 291 CRC32 0x3efac7b3         Delete_rows: table id 268 flags: STMT_END_F BINLOG 'NVO6WhMLAAAALAAAAPsAAAAAAAwBAAAAAAEAA3h0cgACdDIAAQMAAUWsybY=NVO6WiALAAAAKAAAACMBAAAAAAwBAAAAAAEAAgAB//5YAAAAs8f6Pg=='/*!*/;### DELETE FROM `xtr`.`t2`### WHERE###   @1=88 /* INT meta=0 nullable=1 is_null=0 */# at 291#180327 21:20:34 server id 11  end_log_pos 335 CRC32 0xe10122fd         Table_map: `xtr`.`t2` mapped to number 268# at 335#180327 21:20:34 server id 11  end_log_pos 375 CRC32 0x9943c01c         Delete_rows: table id 268 flags: STMT_END_F BINLOG 'MlO6WhMLAAAALAAAAE8BAAAAAAwBAAAAAAEAA3h0cgACdDIAAQMAAf0iAeE=MlO6WiALAAAAKAAAAHcBAAAAAAwBAAAAAAEAAgAB//5jAAAAHMBDmQ=='/*!*/;### DELETE FROM `xtr`.`t2`### WHERE###   @1=99 /* INT meta=0 nullable=1 is_null=0 */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*/;

生成还是binlog文件，把二进制文件一行一行的读出来，反向解析了

问题：