Myflash MySQL Flash back tool

Installation:
Environmental requirements:
1, the Binlog format must be row, and Binlog_row_image=full
2, only support 5.6 and 5.7
3. You can only roll back DML (add, delete, change)

4. : 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

Flash Back:

Update, scenario description, Program 1 mistakenly the XTR library below the T2 table 12,11 changed after 0, this is a wrong operation, and the subsequent program 2 will be 13, 14 change 0, this is a correct operation, the need to flash back Program 1 error operation.
The mold resembles the above scene:
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 the DBA receives the development alarm:
1, flush logs;
The binary file where the action needs to be flashed, this file must be stable and requires flush logs;
2. Record the current Binlog file
3, Analytic binlog
MYSQLBINLOG-VV bin.000038|less

At 838

#180326 21:41:59 Server ID end_log_pos 882 CRC32 0x304d887b table_map: xtr . Mapped to number t2 268 #这个position开始进入 XTR.T2 Library

At 882

#180326 21:41:59 Server ID end_log_pos 928 CRC32 0x8fac8e57 update_rows:table ID 268 flags:stmt_end_f

BINLOG '
Twa5whmlaaaalaaaahidaaaaaawbaaaaaaeaa3h0cgacddiaaqmaaxuitta=
twa5wh8laaaalgaaakadaaaaaawbaaaaaaeaagab///+cwaaap4aaaaav46sjw==
'/! /;

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 end_log_pos 959 CRC32 0xca19e065 Xid = 230
commit/! /;

At 959

#180326 21:42:04 server ID end_log_pos CRC32 0xe7e195c8 GTID last_committed=3 sequence_number=4
SET @ @SESSION. gtid_next= ' 148e1f5e-befd-11e7-ac58-08002738f0ad:207 '/! /;

At 1024

#180326 21:42:04 Server ID end_log_pos 1095 CRC32 0x0c3e3fdc Query thread_id=3 exec_time=0 error_code=0
SET timestamp=1522075324/! /;
BEGIN
/! /;

At 1095

#180326 21:42:04 Server ID end_log_pos 1139 CRC32 0xd6cdd96b table_map: xtr . Mapped to number t2 268

At 1139

#180326 21:42:04 Server ID end_log_pos 1185 CRC32 0xbd63762d update_rows:table ID 268 flags:stmt_end_f

BINLOG '
vaa5whmlaaaalaaaahmeaaaaaawbaaaaaaeaa3h0cgacddiaaqmaawvzzdy=
vaa5wh8laaaalgaaakeeaaaaaawbaaaaaaeaagab///+daaaap4aaaaalxzjvq==
'/! /;

UPDATE xtr. t2#更改的第二条语句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 end_log_pos 1216 CRC32 0xb62d1a59 Xid = 231
commit/! /;

At 1216 #两个事务结束的position点位置

4./flashback--sqltypes= ' UPDATE '--binlogfilenames=/data/mysql/bin.000038--start-position=838--stop-position= 1216--outbinlogfilenamebase=update
Anti-parsing update operation, generating a file starting with update
Generated file name: Binlog_output_base.flashback

5, Mysqlbinlog binlog_output_base.flashback--skip-gtids |mysql-uroot-p ' 123 '-s/tmp/mysql_3306.sock
Import Database

6. Querying the Database
Mysql> select * from Xtr.t2;
+------+
| ID |
+------+
| 12 |
| 11 |
| 4 |
| 3 |
| 0 |
| 0 |
+------+
6 rows in Set (0.00 sec)
Flash back complete

Insert Scene Flash Back
Original table:
Mysql> select * from Xtr.t2;
+------+
| ID |
+------+
| 12 |
| 11 |
| 4 |
| 3 |
| 0 |
| 0 |
+------+
6 rows in Set (0.00 sec)

Inserting 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 we need to flash back the insert operation, id=99,id=99,id=77 all the flashback

Mysql> show master logs;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+ |
| bin.000036 | 1759 |
| bin.000037 | 749 |
| bin.000038 | 1771 |
| bin.000039 | 217 |
| bin.000040 | 2979 |
+------------+-----------+
$ 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-p ' 123 '-s/tmp/mysql_3306.sock

Mysql> select * from Xtr.t2;
+------+
| ID |
+------+
| 12 |
| 11 |
| 4 |
| 3 |
+------+
4 rows in Set (0.00 sec)
Insert Flash-back complete

You can view Insert.flashback files with Mysqlbinlog:

DELETE from xtr. t2where@1=77/ INT meta=0 nullable=1 is_null=0/at 207

#180327 21:20:37 Server ID end_log_pos 251 CRC32 0xb6c9ac45 table_map: xtr . Mapped to number t2 268

At 251

#180327 21:20:37 Server ID 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 end_log_pos 335 CRC32 0xe10122fd table_map: xtr . Mapped to number t2 268

At 335

#180327 21:20:34 Server ID 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 [email protected]_completion_type/;
/!50530 SET @ @SESSION. Pseudo_slave_mode=0/;
Generate or Binlog file, the binary file one line of reading out, reverse parse the

Installation Error:
[Email protected] myflash]# gcc-w pkg-config --cflags --libs glib-2.0 source/binlogparseglib.c-o binary/flashback
Package glib-2.0 is 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

Solve:
Yum-y Install Glib2-devel

Import Error
[Email protected] binary]# mysqlbinlog binlog_output_base.flashback |mysql-uroot-p ' 123 '-s/tmp/mysql_3306.sock
MySQL: [Warning] Using a password on the command line interface can is insecure.
ERROR 1782 (HY000) at line: @ @SESSION. Gtid_next cannot is set to ANONYMOUS when @ @GLOBAL. Gtid_mode = on.

Join--skip-gtids According to the official proposal
[Email protected] binary]# mysqlbinlog binlog_output_base.flashback--skip-gtids |mysql-uroot-p ' 123 '-s/tmp/mysql_ 3306.sock
MySQL: [Warning] Using a password on the command line interface can is insecure.
ERROR 1032 (HY000) at line 36:can ' t find record in ' T2 ' T2 is the table I need to roll back
But with this parameter will still error, but in the database query at this time the data has been rolled back in, the next day in the test there is no this problem?

Problem:

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

Myflash MySQL Flash back tool