Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.