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

Installation:

Environmental requirements:

1. Binlog format must be row and binlog_row_image=full

2. Only 5.6 and 5.7 are supported

3. Only DML can be rolled back (add, delete, change)

Download address: https://github.com/Meituan-Dianping/MyFlash

Installation:

Unzip MyFlash-master.zip

Mv MyFlash-master / usr/local/MyFlash/

Gcc-w pkg-config-- cflags-- libs glib-2.0 source/binlogParseGlib.c-o binary/flashback

Flashback:

Update, scenario description, program 1 mistakenly changed the T2 table under the xtr library to 0, which is a wrong operation, while the latter program 2 changed 13 and 14 to 0, which is a correct operation and needs to flash back the misoperation of program 1.

Similar to the above scenario:

Original table:

Mysql > select * from xtr.t2

+-+

| | id |

+-+

| | 12 |

| | 11 |

| | 4 |

| | 3 |

| | 13 |

| | 14 |

+-+

6 rows in set (0.00 sec)

Update operation:

Update xtr.t2 set id = 0 where id = 12

Update xtr.t2 set id = 0 where id = 11

Update xtr.t2 set id = 0 where id = 13

Update xtr.t2 set id = 0 where id = 14

After DBA received the alarm from the developer:

1 、 flush logs

The binary file where the flashback action is required, which must be stable and require flush logs

2. Record the current binlog file

3. Parsing binlog

Mysqlbinlog-vv bin.000038 | less

At 838

# 180326 21:41:59 server id 11 end_log_pos 882CRC32 0x304d887b Table_map: xtr.t2 mapped to number 268 # this position begins to enter the xtr.t2 library

At 882

# 180326 21:41:59 server id 11 end_log_pos 928 CRC32 0x8fac8e57 Update_rows: table id 268 flags: STMT_END_F

BINLOG'

Twa5WhMLAAAALAAAAHIDAAAAAAwBAAAAAAEAA3h0cgACdDIAAQMAAXuITTA=

Twa5Wh8LAAAALgAAAKADAAAAAAwBAAAAAAEAAgAB///+CwAAAP4AAAAAV46sjw==

'/! /

The first statement changed by 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 id 11 end_log_pos 959 CRC32 0xca19e065 Xid = 290

COMMIT/!/

At 959

# 180326 21:42:04 server id 11 end_log_pos 1024 CRC32 0xe7e195c8 GTID last_committed=3 sequence_number=4

SET @ @ SESSION.GTID_NEXT= '148e1f5e-befd-11e7-ac58-08002738f0adpur207march /

At 1024

# 180326 21:42:04 server id 11 end_log_pos 1095 CRC32 0x0c3e3fdc Query thread_id=3 exec_time=0 error_code=0

SET timestamp 1522075324Universe /

BEGIN

/! /

At 1095

# 180326 21:42:04 server id 11 end_log_pos 1139 CRC32 0xd6cdd96b Table_map: xtr.t2 mapped to number 268

At 1139

# 180326 21:42:04 server id 11 end_log_pos 1185 CRC32 0xbd63762d Update_rows: table id 268 flags: STMT_END_F

BINLOG'

VAa5WhMLAAAALAAAAHMEAAAAAAwBAAAAAAEAA3h0cgACdDIAAQMAAWvZzdY=

VAa5Wh8LAAAALgAAAKEEAAAAAAwBAAAAAAEAAgAB///+DAAAAP4AAAAALXZjvQ==

'/! /

UPDATE xtr.t2 # changed second statement 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 id 11 end_log_pos 1216 CRC32 0xb62d1a59 Xid = 231

COMMIT/!/

At 1216 # position point location for the end of two transactions

4. / flashback-sqlTypes='UPDATE'-binlogFileNames=/data/mysql/bin.000038-start-position=838-stop-position=1216-outBinlogFileNameBase=update

Reverse parsing the UPDATE operation to generate a file that starts with update

Generated file name: binlog_output_base.flashback

5. Mysqlbinlog binlog_output_base.flashback-- skip-gtids | mysql-uroot-pendant 123'-S / tmp/mysql_3306.sock

Import database

6. Query the database

Mysql > select * from xtr.t2

+-+

| | id |

+-+

| | 12 |

| | 11 |

| | 4 |

| | 3 |

| | 0 |

| | 0 |

+-+

6 rows in set (0.00 sec)

Flashback complete

Insert scene flashback

Original table:

Mysql > select * from xtr.t2

+-+

| | id |

+-+

| | 12 |

| | 11 |

| | 4 |

| | 3 |

| | 0 |

| | 0 |

+-+

6 rows in set (0.00 sec)

Insert data

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

+-+

| | id |

+-+

| | 12 |

| | 11 |

| | 4 |

| | 3 |

| | 0 |

| | 0 |

| | 99 |

| | 88 |

| | 77 |

+-+

9 rows in set (0.00 sec)

Mysql > delete from xtr.t2 where id = 0

Query OK, 2 rows affected (0.03 sec)

Mysql > select * from xtr.t2

+-+

| | id |

+-+

| | 12 |

| | 11 |

| | 4 |

| | 3 |

| | 99 |

| | 88 |

| | 77 |

+-+

7 rows in set (0.00 sec)

Now you need to flashback the operation of insert, all the flashbacks of 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-pendant 123'-S / tmp/mysql_3306.sock

Mysql > select * from xtr.t2

+-+

| | id |

+-+

| | 12 |

| | 11 |

| | 4 |

| | 3 |

+-+

4 rows in set (0.00 sec)

Insert flashback complete

You can view the insert.flashback file with mysqlbinlog:

DELETE FROM xtr.t2WHERE@1=77 / INT meta=0 nullable=1 is_null=0 / at 207

# 180327 21:20:37 server id 11 end_log_pos 251 CRC32 0xb6c9ac45 Table_map: xtr.t2 mapped to number 268

At 251

# 180327 21:20:37 server id 11 end_log_pos 291 CRC32 0x3efac7b3 Delete_rows: table id 268 flags: STMT_END_F

BINLOG'

NVO6WhMLAAAALAAAAPsAAAAAAAwBAAAAAAEAA3h0cgACdDIAAQMAAUWsybY=

NVO6WiALAAAAKAAAACMBAAAAAAwBAAAAAAEAAgAB//5YAAAAs8f6Pg==

'/! /

DELETE FROM xtr.t2WHERE@1=88 / INT meta=0 nullable=1 is_null=0 / at 291

# 180327 21:20:34 server id 11 end_log_pos 335 CRC32 0xe10122fd Table_map: xtr.t2 mapped to number 268

At 335

# 180327 21:20:34 server id 11 end_log_pos 375 CRC32 0x9943c01c Delete_rows: table id 268 flags: STMT_END_F

BINLOG'

MlO6WhMLAAAALAAAAE8BAAAAAAwBAAAAAAEAA3h0cgACdDIAAQMAAf0iAeE=

MlO6WiALAAAAKAAAAHcBAAAAAAwBAAAAAAEAAgAB//5jAAAAHMBDmQ==

'/! /

DELETE FROM xtr.t2WHERE@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/

Generate or binlog file, read the binary file line by line, and reverse parse

Installation error:

[root@mysql MyFlash] # gcc-w pkg-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

Resolve:

Yum-y install glib2-devel

Import error report

[root@mysql binary] # mysqlbinlog binlog_output_base.flashback | mysql-uroot-pendant 123'-S / tmp/mysql_3306.sock

Mysql: [Warning] 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.

Join skip-gtids according to official suggestion

[root@mysql binary] # mysqlbinlog binlog_output_base.flashback-- skip-gtids | mysql-uroot-pendant 123'-S / tmp/mysql_3306.sock

Mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1032 (HY000) at line 36: Can't find record in 't2t2 is the table I need to roll back

However, adding this parameter will still report an error, but when querying in the database, the data has already been rolled back, and there is no such problem in the test the next day?

Question:

This is because the location of the position point is wrong.

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.