Mysql MyFlash usage
one。 Install MyFlash
1. Installation condition
Binlog_format=ROW
Binlog_row_image=FULL
Only 5.6and 5.7are supported, and only DML can be rolled back
two。 Installation
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
* No error is reported, which proves that the installation is successful
two。 Restore the scen
1. Roll back the entire file
. / flashback-- binlogFileNames=.000041
Mysqlbinlog binlog_output_base.flashback | mysql-h-u-p
two。 Roll back the DML statement of a table
-Delete data
Mysql > show tables
+-+
| | Tables_in_wwj |
+-+
| | T1 |
+-+
1 row in set (0.00 sec)
Mysql > select * from T1
+-+ +
| | id | name |
+-+ +
| | 5 | |
| | 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)
-generate recovery files (delete operation to roll back T1 table)
Cd / data/MyFlash/MyFlash-master/binary
#. / flashback-databaseNames='wwj'-tableNames='t1'-sqlTypes='DELETE'-binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001
-View Fil
# / usr/local/mysql/bin/mysqlbinlog-vv binlog_output_base.flashback
-restore
# / usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback | / usr/local/mysql/bin/mysql-S / tmp/mysql3307.sock-p
Recovery complete
Mysql > select * from T1
+-+ +
| | id | name |
+-+ +
| | 5 | |
| | 6 | mxt3 |
| | 7 | mxt4 |
+-+ +
3 rows in set (0.00 sec)
3. Restore binlog for a period of time
-generate data
[root@mysql5 mysql3307] # date
Thu Mar 29 05:41:00 CST 2018
Insert into wwj.t3 values (4 ~ mxt2')
Insert into wwj.t3 values (5meme 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
-Roll back the operation between 05:42:57 and 2018-03-29 05Flux 41purl 3782018-03-29
-View binlog
# / usr/local/mysql/bin/mysqlbinlog-- start-datetime='2018-03-29 05-41 mysql-bin.000001 37'--stop-datetime='2018-03-29 05-29 05-- 4215-57 'mysql-bin.000001-- base64-output=decode-rows-v
-generate recovery files
. / flashback-- databaseNames='wwj'-- start-datetime='2018-03-29 05 start-datetime='2018-03-29 05Partition 41 start-datetime='2018 37'-- stop-datetime='2018-03-2905Partition 42Franco 57'-- binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001
-View rollback fil
# / usr/local/mysql/bin/mysqlbinlog-vv binlog_output_base.flashback
-perform a rollback
/ usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback | / usr/local/mysql/bin/mysql-S / tmp/mysql3307.sock-p
-View recovery results
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. Restore a binlog file of position
-generate data
Insert into wwj.t3 values (4 ~ mxt2')
Insert into wwj.t3 values (5meme mxt3')
Insert into wwj.t3 values (6 ~ mxt4')
Insert into wwj.t2 values (4 ~ mxt2')
Insert into wwj.t2 values (5meme mxt3')
Insert into wwj.t2 values (6 ~ mxt4')
Insert into wwj.t1 values (4 ~ mxt2')
Insert into wwj.t1 values (5meme mxt3')
Insert into wwj.t1 values (6 ~ mxt4')
Mysql > delete from wwj.t3
Query OK, 3 rows affected (0.01sec)
Mysql > delete from wwj.t2
Query OK, 3 rows affected (0.01sec)
Mysql > delete from wwj.t1
Query OK, 3 rows affected (0.00 sec)
-View binlog
/ usr/local/mysql/bin/mysqlbinlog mysql-bin.000001-base64-output=decode-rows-v
-confirm the recovery of mysql-bin.000001 position 182302487 and generate the recovery file
#. / flashback-databaseNames='wwj'-start-position=1823-stop-position=2487-binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001
-View rollback fil
# / usr/local/mysql/bin/mysqlbinlog-vv binlog_output_base.flashback
-perform a rollback
/ usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback | / usr/local/mysql/bin/mysql-S / tmp/mysql3307.sock-p
-View data
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)
three。 Related parameters
Click (here) to collapse or open
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
