A tutorial on writing a Python script to implement Mysql rollback _MYSQL

Operation of the database will inevitably because of "careless" and wrong operation, need to restore the words through backup to restore is unlikely, because the need to restore and binlog poor to recover, can not wait, very time-consuming. Here is a description of the next because the delete operation recovery method: mainly through the binlog to restore, if the Binlog_format must be in row format, otherwise only through backup to recover the data.
Method:

Condition: Open Binlog,format for Row.

Steps:

1. Mysqlbinlog specifies the record of the export operation through the MySQL self-with tool:

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 removed, the data needs to be parsed and reversed, and the original data:

### DELETE from Test.me_info ### WHERE ### @1=2165974/* INT meta=0 nullable=0 is_null=0/### @2= ' 1984:03:17 ' * D ATE 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/* TINYI NT 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 m Eta=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 Llable=1 is_null=0 * * ### @15=0/mediumint meta=0 nullable=1 is_null=0/### @16=320/* INT meta=0 nullable=1 Ll=0 ... .......... ....... ...... ......... ............ ........................ 
 

The format of the Binlog record in row format as shown above, the work that needs to be done is to convert the operation of Delete to insert, with a certain regularity on the top, and to note that:

1, field type datetime date. The format saved in the log is @4=2012-10-25 00:00:00, and you need to enclose the 2012-10-25 00:00:00 in quotation marks.

2, negative. The format saved in the log is @1=-1 (4294967295),-2 (4294967294),-3 (4294967293), which needs to be removed from the () data, leaving only the @1=-1.

3, escape character set. such as: ' S,\, and so on.

After 3 clear, you can write a script (the level is limited, in the Ascension, write a bad look):

#!/bin/env python #-*-encoding:utf-8-*-#------------------------------------------------------------------------    -------# Name:restore.py # purpose: Recover delete misoperation data via Binlog # author:zhoujy # created:2012-12-25 # Update: 2012-12-25 # Copyright: (c) Mablevi # licence:zjy #-------------------------------------------------------- 
    -----------------------def 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 ': #该部分替换Delete为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为列数, if less than the maximum number of columns, add, 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 (' \ ', ' \\\ ') #这里过滤掉转义的字符串 lines[3] = ' + lines[3] + ' \ ', ' E Lif lines[3].find (' INT meta ') <>-1: #过滤Int类型的字段为负数后带的 (), positive not affected 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].s Plit ('/* ') [0].strip (' \ '). Strip (). Strip (' \ '). Replace (' \ ', ', '). Replace (' \ ', ' \\\ ') #这里过滤掉转义的字符串 lines[3] = '     ' + lines[3].strip (' \ ' ') + ', ' if '. Join (lines). Find (' @ ') <>-1 and lines[3].split (' = ', 1) [0] = num: 
        #num为列数, if less than the maximum number of columns, followed by the addition); LINES[3] = lines[3].split (' = ', 1) [ -1].strip () if Lines[3].find (' \ ') <> -1:lines[3]= Lines[3].split ('/* ') [0].strip (' \ '). Strip (). Strip ('). Replace (' \ ', ', '). Replace (' \ ', ' \\\ ') #同上 lines[3] = 
        ' + ' + lines[3] + '; ' Elif lines[3].find (' INT meta ') <>-1: #同上 lines[3] = lines[3].split ('/* ') [0].strip () Li 
        NES[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 (' \ ', ' \\\ ') 
      #同上 lines[3] = ' \ ' + lines[3].strip (' \ ' ') + ' \ '); ' print '. Join (lines[1:]) if lines = = ': break if __name__ = = ' __main__ ': Import sys read_binlog (SYS.A 

 RGV[1],SYS.ARGV[2])

Execute script:

Python restore.py binlog.txt > Binlog.sql

36 on the command line indicates that there are 36 fields in the table that need to be restored, and the effect:

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

Final restore:

MySQL Test < Binlog.sql