Binlog Row Mode Delete mistakenly delete recovery

Scenario: A DBA mistakenly executes a delete from db.t1 during data manipulation, and this database is configured as row, which now needs to be db.t1 using Binlog to recover the error.

1. Get the approximate position of the DELETE statement in Binlog

#切换下二进制日志

MySQL >flush logs;

#将二进制binlog文件转换为文本文件

/usr/local/mysql/bin/mysqlbinlog-v--base64-output=decode-rows./mysql-bin.000005>temp.log

#找到该操作所对应的事务的开始和结束, and then redirect the log to another file Delete.log

/usr/local/mysql/bin/mysqlbinlog-v--base64-output=decode-rows--start-position=120--stop-position=401. Mysql-bin.000005>delete.log

#使用Python脚本提取恢复insert语句:

Python delete.py "./delete.log" 6

#提取出的结果如下:

INSERT into ' test '. ' T1 ' SELECT, 1, ' Gao ', ' Quan ', 31;

INSERT into ' test '. ' T1 ' SELECT, 2, ' Wang ', ' qua1 ', 32;

INSERT into ' test '. ' T1 ' SELECT, 3, ' Wan ', ' qua1 ', 32;

INSERT into ' test '. ' T1 ' SELECT, 4, ' qqq ', ' qua1 ', 32;

INSERT into ' test '. ' T1 ' SELECT, 5, ' qqq ', ' ssss ', 32;

#脚本如下

#-*-Coding:utf-8-*-

Import re

#列表拆分

def div_list (ls,n):

n = Int (n)

Ls_len = Len (ls)

j = ls_len/n

Ls_return = []

For I in Range (0,J):

Ls_return.append (Ls[i*n: (n (i+1))])

Return Ls_return

#col_count为表的列数 +2,binlog the text after processing for mysqlbinlog-v

def exc_binlog (Binlog,col_count):

With open (Binlog) as F:

Lines=[]

For line in F:

If Re.search (' # # # ', line):

Line=re.sub ("\ (\d+\)", "", Re.sub ("\@\d+\=", ",", Line.strip (). replace ("# # #", "" "). Replace (" DELETE from "," INSERT into " ). Replace ("WHERE", "select")))

Lines.append (line)

List=div_list (Lines,col_count)

With open (r "./copy.log", "W") as F:

For I in range (0,len (list)):

List1=list[i]

Line= "". Join (List1). Replace ("SELECT,", "select") + ";" + "\ n"

F.writelines (line)

if __name__ = = ' __main__ ':

Import Sys

Exc_binlog (Sys.argv[1],sys.argv[2])

Binlog Row Mode Delete mistakenly delete recovery