How to compile the Python script for MySQL rollback, mysqlpython

How to compile the Python script for MySQL rollback, mysqlpython

During database operations, it is inevitable that the database will be mistakenly operated due to the "carelessness". If you need to recover quickly, it is unlikely that the database will be restored through backup, because the restoration and binlog must be different to restore the database, which is time-consuming. Here, we will first describe the restoration method for the Delete operation: binlog is used for recovery, provided that binlog_format must be in Row format; otherwise, data can only be restored through backup.
Method:

Condition: Binlog is enabled, and Format is Row.

Steps:

1. Use the MySQL built-in tool mysqlbinlog to specify the export operation record:

mysqlbinlog --no-defaults --start-datetime='2012-12-25 14:56:00' --stop-datetime='2012-12-25 14:57:00' -vv mysql-bin.000001 > /home/zhoujy/restore/binlog.txt 

2. After the data is retrieved, You need to reverse the data parsing. The original data:

### DELETE FROM test.me_info ### WHERE ###  @1=2165974 /* INT meta=0 nullable=0 is_null=0 */ ###  @2='1984:03:17' /* DATE meta=0 nullable=1 is_null=0 */ ###  @3=NULL /* DATE meta=765 nullable=1 is_null=1 */ ###  @4=2012-10-25 00:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */ ###  @5='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */ ###  @6=0 /* TINYINT meta=0 nullable=1 is_null=0 */ ###  @7='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */ ###  @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */ ###  @9=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ ###  @10=NULL /* MEDIUMINT meta=0 nullable=1 is_null=1 */ ###  @11=2 /* TINYINT meta=0 nullable=1 is_null=0 */ ###  @12=0 /* TINYINT meta=0 nullable=1 is_null=0 */ ###  @13='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */ ###  @14='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */ ###  @15=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ ###  @16=320 /* INT meta=0 nullable=1 is_null=0 */ …………………… …………………… …………………… 

The format of the binlog record in the Row format is shown above. What you need to do is convert the Delete operation into the Insert operation. The above statements are regular, and note that:

1. DATETIME Date of field type. The log is saved in the format of @ 4 = 00:00:00. You need to quote 00:00:00 with quotation marks.

2. negative number. The log is saved in the format of @ 1 =-1 (4294967295),-2 (4294967294),-3 (4294967293), and the data in () needs to be removed, retain only @ 1 =-1.

3. Escape Character Set. Such as:'s, \, and so on.

After the above three points are clear, you can write a script (the level is limited, but it is not easy to write during the upgrade ):

#! /Bin/env python #-*-encoding: UTF-8-*-# ----------------------------------------------------------------------------- # Name: restore. py # Purpose: Restore deleted misoperation data through Binlog # Author: zhoujy # Created: 2012-12-25 # update: 2012-12-25 # Copyright: (c) Mablevi 2012 # Licence: zjy # javasdef read_binlog (file, column_num): f = open (file) num = '@' + str (column_num) while True: lines = f. readline () if lines. strip () [0: 3] = '###': lines = lines. split ('', 3) if lines [1] = 'delete' and lines [2] = 'from ': # Replace "Delete" with "Insert lines [1] =" INSERT "lines [2] =" INTO "lines [-1] = lines [-1]. strip () if lines [1]. strip () = 'where': lines [1] = 'values ('if ''. join (lines ). find ('@') <>-1 and lines [3]. split ('=', 1) [0] <> num: # num indicates the number of columns. If the number is smaller than the maximum number of columns, the column is added. lines [3] = lines [3]. split ('=', 1) [-1]. strip () if lines [3]. strip ('\''). strip (). find ('\ '') <>-1: lines [3] = lines [3]. split ('/*') [0]. strip ('\''). strip (). strip ('\''). replace ('\\',''). replace ('\ '',' \'') # Here, filter the lines [3] = '\ ''+ lines [3] + '\', 'elif lines [3]. find ('int meta') <>-1: # filter the () value after the INT type field is negative. The positive number is not affected by lines [3] = lines [3]. split ('/*') [0]. strip () lines [3] = lines [3]. split () [0] + ', 'elif lines [3]. find ('null') <>-1: lines [3] = lines [3]. split ('/*') [0]. strip () lines [3] = lines [3] + ', 'else: lines [3] = lines [3]. split ('/*') [0]. strip ('\''). strip (). strip ('\''). replace ('\\',''). replace ('\ '',' \'') # Here, filter the lines [3] = '\ ''+ lines [3]. strip ('\ ''') +' \ ', 'if ''. join (lines ). find ('@') <>-1 and lines [3]. split ('=', 1) [0] = num: # num is the number of columns. If it is smaller than the maximum number of columns, it is added later ); lines [3] = lines [3]. split ('=', 1) [-1]. strip () if lines [3]. find ('\ '') <>-1: lines [3] = lines [3]. split ('/*') [0]. strip ('\''). strip (). strip ('\''). replace ('\\',''). replace ('\ '',' \'') # Same as lines [3] = '\ ''+ lines [3] + '\'); 'elif lines [3]. find ('int meta') <>-1: # Same as lines [3] = lines [3]. split ('/*') [0]. strip () lines [3] = lines [3]. split ('') [0] + '); 'elif lines [3]. find ('null') <>-1: lines [3] = lines [3]. split ('/*') [0]. strip () lines [3] = lines [3] + '); 'else: lines [3] = lines [3]. split ('/*') [0]. strip ('\''). strip (). strip ('\''). replace ('\\',''). replace ('\ '',' \'') # Same as lines [3] = '\ ''+ lines [3]. strip ('\ ''') +' \ '); 'print ''. join (lines [1:]) if lines = '': break if _ name _ = '_ main _': import sys read_binlog (sys. argv [1], sys. argv [2])

Run the script:

python restore.py binlog.txt 36 > binlog.sql

36 in the command line indicates 36 fields in the table to be restored. effect:

INSERT INTO test.me_info VALUES (  2123269,  '1990:11:12',  NULL,  2,  '',  0,  '',  -1,  0,  340800,  1,  0,  '', …… ……  1,  NULL ); 

Last restored:

mysql test < binlog.sql