Flush back with a Python script for incorrect MySQL operation

1. Introduction
In the Oracle database, when a false operation is committed, we can flash the table back to the state before the error operation via the Flash back function provided by Oracle. There is no native flush back feature in MySQL, and when the DBA is operating in error, the traditional recovery method is to use the full-standby + binary log roll forward to recover.
Today to introduce a Python script in MySQL similar to the implementation of the Flush back table in Oracle, compared to the traditional full-standby + add-on, the method is faster and simpler.

2. Flash Back principle
Principle: Call mysql_rollback.py (the script in my other blog Flashback script: mysql_rollback.py) Reverses the rows format of binlog, and delete reverse generates INSERT, Update generates a reverse update, insert reverse generates delete.

3. Description
0. Need to install Python and MySQLdb module
1. The format of the Binlog must be row
2. The table structure is not changed before and after the table operation to be resumed, otherwise the script cannot parse
3. Only DML (INSERT/UPDATE/DELETE) Rollback statements are generated, DDL statements cannot be rolled back
4, the resulting SQL is reversed, so the latest DML will be generated at the top of the input file, and with a timestamp and offset points, easy to find the target
5, need to provide a read-only user to connect to MySQL, mainly to get the table structure
6, if the binlog is too large, it is recommended to take the time range, you can also specify that only restore a library of SQL
7, after SQL generation, be sure to test the recovery on the test environment before applying to the online

4. Actual combat
Step1. Log in to MySQL view table information

mysql> use db1Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from test;+------+-------+------+---------+----------+| id   | name  | age  | country | city     |+------+-------+------+---------+----------+|    1 | alex  |   26 | china   | shanghai ||    2 | bob   |   25 | britain | london   ||    3 | simon |   24 | france  | paris    |+------+-------+------+---------+----------+3 rows in set (0.00 sec)

Step2. Analog error operation (update)

mysql> update test set country=‘europe‘ where name=‘bob‘;   --bob的国家被改为europeQuery OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from test;+------+-------+------+---------+----------+| id   | name  | age  | country | city     |+------+-------+------+---------+----------+|    1 | alex  |   26 | china   | shanghai ||    2 | bob   |   25 | europe  | london   ||    3 | simon |   24 | france  | paris    |+------+-------+------+---------+----------+3 rows in set (0.00 sec)mysql> exit;Bye

Step3. Parsing Binlog and generating a reverse statement
Find the latest Binlog

SZD-L0087668:gzz3306:Master > ll-rw-rw---- 1 mysql mysql      167 May  2 14:30 mysql-bin.000001-rw-rw---- 1 mysql mysql 11400402 May  2 19:28 mysql-bin.000002-rw-rw---- 1 mysql mysql     1807 May  2 19:49 mysql-bin.000003-rw-rw---- 1 mysql mysql      660 May  2 20:10 mysql-bin.000004-rw-rw---- 1 mysql mysql      403 May  2 20:10 mysql-bin.000005-rw-rw---- 1 mysql mysql      584 May  3 10:45 mysql-bin.000006-rw-rw---- 1 mysql mysql      417 May  3 10:53 mysql-bin.000007-rw-rw---- 1 mysql mysql     1973 May  3 13:28 mysql-bin.000008-rw-rw---- 1 mysql mysql     2604 May  3 14:13 **mysql-bin.000009**-rw-rw---- 1 mysql mysql      369 May  3 13:28 mysql-bin.index-rw-r--r-- 1 root  root     12222 Apr 13  2017 mysql_rollback.py

According to the keyword Europe find the Binlog in the error operation of SQL, and output Europe before and after 30 rows (the number of rows depending on the situation, be sure to output the statement corresponding to the begin and commit part)

Szd-l0087668:gzz3306:master > Mysqlbinlog--no-defaults-v-v--base64-output=decode-rows--set-charset=utf8 mysql-bin.000009 | Grep-c ' Europe ' ...  begin/*!*/;# at 2426#180503 14:13:36 server ID 1 end_log_pos 2482 CRC32 0xe79b9612 table_map: ' db1 '. ' Test ' mapped To number 76# at 2482#180503 14:13:36 server ID 1 end_log_pos 2573 CRC32 0XACD94A0B update_rows:table ID FLA gs:stmt_end_f### UPDATE ' db1 '. ' Test ' # # # where### @1=2 */INT meta=0 nullable=1 is_null=0 */### @2= ' Bob '/* varstring ( meta=30 nullable=1 is_null=0 */### @3=25/INT meta=0 nullable=1 is_null=0 */### @4= ' Britain '/* varstring (Me) ta=30 nullable=1 is_null=0 */### @5= ' London '/* varstring (+) meta=30 nullable=1 is_null=0 */### set### @1=2/* INT me Ta=0 nullable=1 is_null=0 */### @2= ' Bob '/* varstring (+) meta=30 nullable=1 is_null=0 */### @3=25/* INT meta=0 Nulla ble=1 is_null=0 */### @4= ' Europe '/* varstring (+) meta=30 nullable=1 is_null=0 */### @5= ' London '/* varstring (meta=30 nullable=1 is_null=0 */# at 2573#180503 14:13:36 server ID 1 end_log_pos 2604 CRC32 0x63b3d5fa Xid = 118commit/*!*/, .....

Choose 2426 and 2604 as the starting position for the analysis Binlog

SZD-L0087668:gzz3306:Master > python2.7 mysql_rollback.py -f mysql-bin.000009 -o rollback.sql -h127.0.0.1 -P3306 -uroot -p123456 --start-position=‘2426‘ --stop-position=‘2604‘ -d db1正在获取参数.....正在解析binlog.....正在初始化列名.....正在开始拼凑sql.....done!

View reverse statements for misoperation in Rollback.sql

SZD-L0087668:gzz3306:Master > cat rollback.sql ## at 2482##180503 14:13:36 server id 1  end_log_pos 2573 CRC32 0xacd94a0b        Update_rows: table id 76 flags: STMT_END_FUPDATE `db1`.`test`SET  id=2  ,name=‘bob‘  ,age=25  ,country=‘britain‘  ,city=‘london‘WHERE  id=2  AND name=‘bob‘  AND age=25  AND country=‘europe‘  AND city=‘london‘;

Step4. Rolling back

SZD-L0087668:gzz3306:Master > mysql -uroot -p <rollback.sql Enter password: SZD-L0087668:gzz3306:Master > mysql -uroot -p -e ‘select * from db1.test‘;Enter password: +------+-------+------+---------+----------+| id   | name  | age  | country | city     |+------+-------+------+---------+----------+|    1 | alex  |   26 | china   | shanghai ||    2 | bob   |   25 | britain | london   ||    3 | simon |   24 | france  | paris    |+------+-------+------+---------+----------+

The test table has been rolled back.

Flush back with a Python script for incorrect MySQL operation