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]
 

4 个评论

V5 ,实现的方法比较优雅。
earl86 你好,这在python 哪个版本下执行啊,我的是2.7.8.执行报错
python /download/sqlconvert.py -f /tmp/mysql-bin.000013.sql -s 1 -t update
update 大写就不报错,但没有看到生成的文件
python很强嘛

要回复文章请先登录注册