一.安装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
delete from wwj.t3;
[root@mysql5 mysql3307]# date
Thu Mar 29 05:42:57 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)
三.相关参数
点击(此处)折叠或打开
- Application 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
