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

1. profile

In oracle database, when a misoperation is committed, we can flashback the table to the state before the misoperation through the flashback function provided by oracle. MySQL does not have a native flashback function. In case of DBA misoperation, the traditional recovery method is to use full backup + binary log roll-forward for recovery.

Today, I will introduce you to a Python script in MySQL to achieve flashback functions similar to Oracle flashback table, compared to the traditional full backup + backup, this method is faster and simpler.

2. flashback principle

Principle: Call mysql_rollback.py (script in my other blog flashback script: mysql_rollback.py) to reverse the binlog in rows format, delete generates insert in reverse, update generates update in reverse, insert generates delete in reverse.

3. description

0, Python and MySQLdb modules need to be installed

Binlog must be in row format.

2. The table structure to be restored has not changed before and after the operation, otherwise the script cannot be parsed.

3. Only rollback statements of DML(insert/update/delete) are generated. DDL statements cannot be rolled back.

4. The SQL finally generated is in reverse order, so the latest DML will be generated at the front of the input file, and with a timestamp and offset point to facilitate finding the target.

5, need to provide a connection MySQL read-only user, mainly to obtain the table structure

6. If the binlog is too large, it is recommended to bring a time range, or you can specify that only SQL of a certain library is restored.

7. After SQL is generated, be sure to test the recovery in the test environment before applying it to the line.

4. actual combat

step1. Log in to mysql to 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)

Step 2. Simulate misoperation (update)

mysql> update test set country='europe' where name='bob'; --bob's country was changed to 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

Step 3. Parse binlog and generate inverted statements

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

Find the misoperation sql in binlog according to the keyword europe, and output 30 lines before and after europe (the number of lines depends on the specific situation, be sure to output the BEGIN and COMMIT parts corresponding to the statement)

SZD-L0087668:gzz3306:Master > mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS --set-charset=utf8 mysql-bin.000009 | grep -C 30 '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 76 flags: STMT_END_F### UPDATE `db1`.` test`### WHERE### @1=2 /* INT meta=0 nullable=1 is_null=0 */### @2='bob' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */### @3=25 /* INT meta=0 nullable=1 is_null=0 */### @4='britain' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */### @5='london' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */### SET### @1=2 /* INT meta=0 nullable=1 is_null=0 */### @2='bob' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */### @3=25 /* INT meta=0 nullable=1 is_null=0 */### @4='europe' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */### @5='london' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */# at 2573#180503 14:13:36 server id 1 end_log_pos 2604 CRC32 0x63b3d5fa Xid = 118COMMIT/*!*/;...

Select 2426 and 2604 as starting positions for analysis binlog

SZD-L0087668:gzz3306:Master > python2.7 mysql_rollback.py -f mysql-bin. 00009-o rollback.sql -h227.0.0.1 -P3306 -uroot -p123456 --start-position='2426' --stop-position='2604' -d db1 Getting parameters... Parsing binlog... Initializing column names... Starting to piece together sql... done!

Look at the reverse of the 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';

Step 4. Rollback

SZD-L0087668:gzz3306:Master > mysql -uroot -p 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 |+------+-------+------+---------+----------+

Test table rolled back.

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.