mysql row 格式binlog 恢复_mysql 误删，误更新数据恢复 脚本，基于row格式binlog

mysql 误删，误更新数据恢复 脚本，基于row格式binlog

恢复时需要指定-s columnsize(字段行数) -t update/delete(恢复类型)

update语句需要手动更新colunmlist的值(字段列表)

python脚本如下：

#!/usr/bin/python

#coding=utf-8

#Author：earl86

#version 1.0

#the script for 'delete from xxxtable;' and 'update xxxtable set yyycol='',xxxcol='';'

#mysqlbinlog -v --base64-output=decode-rows --start-position=6908 --stop-position=8900 mysql-bin.000001 >mysql-bin.000001.sql

#how to use：python sqlconvert.py -f mysql-bin.000001.sql -s columnsize -t update/delete

#need to modify the value of colunmlist

#update sql need customed by yourself

import os

import string

import sys

import argparse

reload(sys)

sys.setdefaultencoding('utf8')

parser = argparse.ArgumentParser()

parser.add_argument("-f", action="store", dest='sqlfile', help="input the sql file dir and file name", required=True)

parser.add_argument("-s", action="store", dest='colnum', help="input the Number of columns", required=True)

parser.add_argument("-t", action="store", dest='sqltype', help="input the convert sql type.update or delete", required=True)

args = parser.parse_args()

def updatereadlines():

result = list()

with open(args.sqlfile, 'r') as f:

f = f.readlines()

for line in f:

if line.startswith('### '):

if line.startswith('### UPDATE '):

#print result

convertupdatesql(result)

result =

result.append(line.strip('\n').replace('### ',' '))

#print result

convertupdatesql(result)

def updatereadline():

result = list()

f = open(args.sqlfile,'r')

for line in open(args.sqlfile):

line = f.readline()

if line.startswith('### '):

if line.startswith('### UPDATE '):

#print result

convertupdatesql(result)

result =

result.append(line.strip('\n').replace('### ',' '))

#print result

convertupdatesql(result)

f.close()

def convertupdatesql(result):

if (len(result) > 0):

sql = result[0] + result[int(args.colnum)+2] + result[8].replace('@7','local_backup_status') + result[1] + result[2].replace('@1','id') + ';'

#print sql

open('result-update.sql', 'a').write(sql+'\n')

def deletereadlines(colunmlist):

result = list()

with open(args.sqlfile, 'r') as f:

f = f.readlines()

for line in f:

if line.startswith('### '):

if line.startswith('### DELETE '):

#print result

convertdeletesql(result,colunmlist)

result =

result.append(line.strip('\n').replace('### ',' '))

#print result

convertdeletesql(result,colunmlist)

def deletereadline(colunmlist):

result = list()

f = open(args.sqlfile,'r')

for line in open(args.sqlfile):

line = f.readline()

if line.startswith('### '):

if line.startswith('### DELETE '):

#print result

convertdeletesql(result,colunmlist)

result =

result.append(line.strip('\n').replace('### ',' '))

#print result

convertdeletesql(result,colunmlist)

f.close()

def convertdeletesql(result,colunmlist):

if (len(result) > 0):

sql =  result[0].replace('DELETE FROM','INSERT INTO') + ' SET'

for i in range(2,int(args.colnum)+2):

if (i == int(args.colnum)+1):

sql = sql + result.replace('@' + str(i-1),colunmlist[i-2]) + ';'

else:

sql = sql + result[i].replace('@' + str(i-1),colunmlist[i-2]) + ','

#print sql

open('result-delete.sql', 'a').write(sql+'\n')

if __name__ == '__main__':

colunmlist =('id','backup_time','host_name','service_name','file_name','file_md5','local_backup_status','remote_backup_status','backup_user')

if ('delete' == args.sqltype):

try:

os.remove('result-delete.sql')

except:

pass

deletereadline(colunmlist)

elif ('update' == args.sqltype):

try:

os.remove('result-update.sql')

except:

pass

updatereadline()[/i]