MyFlash MySQL闪回工具
安装：环境要求：
1、binlog格式必须为row，且binlog_row_image=full
2、仅支持5.6与5.7
3、只能回滚DML（增、删、改）
[*]下载地址：https://github.com/Meituan-Dianping/MyFlash
安装：
unzip MyFlash-master.zip
mv MyFlash-master /usr/local/MyFlash/
gcc -wpkg-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;
+------+
|> +------+
| 12 |
| 11 |
| 4 |
| 3 |
| 13 |
| 14 |
+------+
6 rows in set (0.00 sec)
update操作：
update xtr.t2 set>
update xtr.t2 set>
update xtr.t2 set>
update xtr.t2 set> DBA接到开发的报警后：
1、flush logs;
需要闪回的动作所在的二进制文件，这个文件必须是稳定的，需要flush logs;
2、记录当前的binlog文件
3、解析binlog
mysqlbinlog -vv bin.000038|less
at 838
#180326 21:41:59 server>xtr.t2 mapped to number 268 #这个position开始进入xtr.t2库
at 882
#180326 21:41:59 server> 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> COMMIT/!/;
at 959
#180326 21:42:04 server> SET @@SESSION.GTID_NEXT= '148e1f5e-befd-11e7-ac58-08002738f0ad:207'/!/;
at 1024
#180326 21:42:04 server> SET TIMESTAMP=1522075324/!/;
BEGIN
/!/;
at 1095
#180326 21:42:04 server>xtr.t2 mapped to number 268
at 1139
#180326 21:42:04 server> 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> 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;
+------+
|> +------+
| 12 |
| 11 |
| 4 |
| 3 |
| 0 |
| 0 |
+------+
6 rows in set (0.00 sec)
闪回完成
insert 场景闪回
原表：
mysql> select * from xtr.t2;
+------+
|> +------+
| 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;
+------+
|> +------+
| 12 |
| 11 |
| 4 |
| 3 |
| 0 |
| 0 |
| 99 |
| 88 |
| 77 |
+------+
9 rows in set (0.00 sec)
mysql> delete from xtr.t2 where> Query OK, 2 rows affected (0.03 sec)
mysql> select * from xtr.t2;
+------+
|> +------+
| 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;
+------+
|> +------+
| 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>xtr.t2 mapped to number 268
at 251
#180327 21:20:37 server> 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>xtr.t2 mapped to number 268
at 335
#180327 21:20:34 server> 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文件，把二进制文件一行一行的读出来，反向解析了
安装报错：
# gcc -wpkg-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 installglib2-devel
导入报错
# mysqlbinlog binlog_output_base.flashback |mysql -uroot -p'123' -S /tmp/mysql_3306.sock
mysql: 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
# mysqlbinlog binlog_output_base.flashback --skip-gtids |mysql -uroot -p'123' -S /tmp/mysql_3306.sock
mysql: 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点的位置
页:
[1]