mysql闪回工具下载_MyFlash MySQL闪回工具

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

安装：

环境要求：

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

2、仅支持5.6与5.7

3、只能回滚DML(增、删、改)

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

安装：

unzip MyFlash-master.zip

mv 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_F

BINLOG '

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 = 230

COMMIT/*!*/;

# at 959

#180326 21:42:04 server id 11 end_log_pos 1024 CRC32 0xe7e195c8 GTID last_committed=3 sequence_number=4

SET @@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=0

SET 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 = 231

COMMIT/*!*/;

# 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文件，把二进制文件一行一行的读出来，反向解析了

问题：

安装报错：

[root@mysql

MyFlash]# gcc -w  `pkg-config --cflags

--libs glib-2.0` source/binlogParseGlib.c

-o binary/flashback

Package glib-2.0 was

not found in the pkg-config search path.

Perhaps you should

add the directory containing `glib-2.0.pc'

to the

PKG_CONFIG_PATH environment variable

No package

'glib-2.0' found

解决：

yum

-y install  glib2-devel

导入报错

[root@mysql binary]#

mysqlbinlog binlog_output_base.flashback |mysql -uroot -p'123' -S

/tmp/mysql_3306.sock

mysql: [Warning]

Using a password on the command line interface can be insecure.

ERROR 1782 (HY000)

at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when

@@GLOBAL.GTID_MODE = ON.

根据官方建议加入--skip-gtids

[root@mysql binary]#

mysqlbinlog binlog_output_base.flashback --skip-gtids |mysql -uroot -p'123' -S

/tmp/mysql_3306.sock

mysql: [Warning]

Using a password on the command line interface can be insecure.

ERROR 1032 (HY000) at line 36: Can't find record in 't2't2是我需要回滚的表

但加上这个参数还是会报错，但在数据库里查询此时数据已经回滚进去了,第二天在测试就没有这个问题了？

问题：

这是因为找错了position点的位置