mysql MyFlash使用

一.安裝MyFlash
1.安裝條件
binlog_format=ROW
binlog_row_image=FULL
僅支持5.6與5.7，并且只能回滾DML

2.安裝
unzip MyFlash.zip
yum -y install glib2*
cd /data/MyFlash/MyFlash-master
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
*沒有報錯，證明安裝成功

二.恢復場景
1.回滾整個文件
./flashback --binlogFileNames=haha.000041
mysqlbinlog binlog_output_base.flashback | mysql -h -u -p

2.回滾某個表的DML語句
-刪除數據
mysql> show tables;
+---------------+
| Tables_in_wwj |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  5 | haha |
|  6 | mxt3 |
|  7 | mxt4 |
+----+------+
3 rows in set (0.00 sec)

mysql> delete from t1;
Query OK, 3 rows affected (0.02 sec)

mysql> select * from t1;
Empty set (0.00 sec)

-生成恢復文件(回滾t1表的delete操作)
cd /data/MyFlash/MyFlash-master/binary
# ./flashback --databaseNames='wwj' --tableNames='t1' --sqlTypes='DELETE' --binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001

-查看文件
# /usr/local/mysql/bin/mysqlbinlog -vv binlog_output_base.flashback

-恢復
# /usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback|/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -p
恢復完成
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  5 | haha |
|  6 | mxt3 |
|  7 | mxt4 |
+----+------+
3 rows in set (0.00 sec)
3.恢復一段時間的binlog

-生成數據
[root@mysql5 mysql3307]# date
Thu Mar 29 05:41:00 CST 2018
insert into wwj.t3 values(4,'mxt2');
insert into wwj.t3 values(5,'mxt3');
insert into wwj.t3 values(6,'mxt4');
[root@mysql5 mysql3307]# date
Thu Mar 29 05:41:37 CST 2018
- 回滾2018-03-29 05:41:37~2018-03-29 05:42:57 之間的操作
- 查看binlog
# /usr/local/mysql/bin/mysqlbinlog --start-datetime='2018-03-29 05:41:37' --stop-datetime='2018-03-29 05:42:57' mysql-bin.000001 --base64-output=decode-rows -v
- 生成恢復文件
./flashback --databaseNames='wwj' --start-datetime='2018-03-29 05:41:37' --stop-datetime='2018-03-29 05:42:57' --binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001
- 查看回滾文件
# /usr/local/mysql/bin/mysqlbinlog -vv binlog_output_base.flashback
- 執行回滾
/usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback|/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -p
- 查看恢復結果
mysql> select * from wwj.t3;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***
+----+------+
| id | name |
+----+------+
|  4 | mxt2 |
|  5 | mxt3 |
|  6 | mxt4 |
+----+------+

3.恢復一段position的binlog文件
- 生成數據
insert into wwj.t3 values(4,'mxt2');
insert into wwj.t3 values(5,'mxt3');
insert into wwj.t3 values(6,'mxt4');
insert into wwj.t2 values(4,'mxt2');
insert into wwj.t2 values(5,'mxt3');
insert into wwj.t2 values(6,'mxt4');
insert into wwj.t1 values(4,'mxt2');
insert into wwj.t1 values(5,'mxt3');
insert into wwj.t1 values(6,'mxt4');

mysql> delete from wwj.t3;
Query OK, 3 rows affected (0.01 sec)
mysql> delete from wwj.t2;
Query OK, 3 rows affected (0.01 sec)
mysql> delete from wwj.t1;
Query OK, 3 rows affected (0.00 sec)

-查看binlog
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 --base64-output=decode-rows -v

-確認恢復 mysql-bin.000001 position 1823~2487，生成恢復文件
# ./flashback --databaseNames='wwj' --start-position=1823 --stop-position=2487 --binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001

- 查看回滾文件
# /usr/local/mysql/bin/mysqlbinlog -vv binlog_output_base.flashback

- 執行回滾
/usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback|/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -p

-查看數據
mysql> select * from wwj.t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    17
Current database: *** NONE ***
+----+------+
| id | name |
+----+------+
|  4 | mxt2 |
|  7 | mxt2 |
|  5 | mxt3 |
|  8 | mxt3 |
|  6 | mxt4 |
|  9 | mxt4 |
+----+------+
6 rows in set (0.00 sec)

mysql> select * from wwj.t2;
+----+------+
| id | name |
+----+------+
|  4 | mxt2 |
|  5 | mxt3 |
|  6 | mxt4 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from wwj.t3;
+----+------+
| id | name |
+----+------+
|  4 | mxt2 |
|  5 | mxt3 |
|  6 | mxt4 |
+----+------+
3 rows in set (0.00 sec)