Rollback in case of a DELETE misoperation in MySQL

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-12-25 14:56:00'
-- Stop-datetime = '2017-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 = '2014: 03: 17'/* DATE meta = 0 nullable = 1 is_null = 0 */
###@ 3 = NULL/* DATE meta = 765 nullable = 1 is_null = 1 */
###@ 4 = 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
#-------------------------------------------------------------------------------
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': # 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 characters that are not converted to meanings.
Lines [3] = ''' + lines [3] + '','
Elif lines [3]. find ('int meta') <>-1: # filter fields of the INT type with negative values (). Positive values are 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]. split ('/*') [0]. strip ('''). strip (). strip ('''). replace ('',''). replace (''', ''') # Here, filter the characters that are not converted to meanings.
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 above
Lines [3] = ''' + lines [3] + '');'
Elif lines [3]. find ('int meta') <>-1: # Same as above
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 above
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,
'2017: 11: 12 ',
NULL,
2,
'',
0,
'',
-1,
0,
340800,
1,
0,
'',
......
......
1,
NULL
);

Last restored:

Mysql test <binlog. SQL

Summary:

Next, sort out the advantages and disadvantages of Row and STATEMENT.