MySQL [Delete misoperation] data recovery, mysqldelete

MySQL [Delete misoperation] data recovery [convert], mysqldelete

Preface:
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='2017-03-16 14:56:00' --stop-datetime='2017-03-16 14:57:00' -vv mysql-bin.000001 > /tmp/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=2017-03-16 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). You need to remove the data in the brackets, 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-*-# handler # Name: restore_insert.py # Purpose: recover data with Delete misoperations through Binlog # 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 = '': breakif _ name _ = '_ main _': import sys read_binlog (sys. argv [1], sys. argv [2])

View Code

Run the Script: Method: number of fields in the binlog file of the python script name

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_infoVALUES (  2123269,  '1990:11:12',  NULL,  2,  '',  0,  '',  -1,  0,  340800,  1,  0,  '',…………  1,  NULL);

Last restored:

mysql test < binlog.sql