#!/bin/env python
# -*- coding:utf-8 -*-
import os,sys,re,getopt
import MySQLdb
host = '127.0.0.1'
user = ''
password = ''
port = 3306
start_datetime = '1971-01-01 00:00:00'
stop_datetime = '2037-01-01 00:00:00'
start_position = '4'
stop_position = '18446744073709551615'
database = ''
mysqlbinlog_bin = 'mysqlbinlog -v'
binlog = ''
fileContent = ''
output='rollback.sql'
only_primary = 0
# ----------------------------------------------------------------------------------------
# : , binlog
# ----------------------------------------------------------------------------------------
def getopts_parse_binlog():
global host
global user
global password
global port
global fileContent
global output
global binlog
global start_datetime
global stop_datetime
global start_position
global stop_position
global database
global only_primary
try:
options, args = getopt.getopt(sys.argv[1:], "f:o:h:u:p:P:d:", ["help","binlog=","output=","host=","user=","password=","port=","start-datetime=", \
"stop-datetime=","start-position=","stop-position=","database=","only-primary="])
except getopt.GetoptError:
print " !!!!!"
options = []
if options == [] or options[0][0] in ("--help"):
usage()
sys.exit()
print " ....."
for name, value in options:
if name == "-f" or name == "--binlog":
binlog = value
if name == "-o" or name == "--output":
output = value
if name == "-h" or name == "--host":
host = value
if name == "-u" or name == "--user":
user = value
if name == "-p" or name == "--password":
password = value
if name == "-P" or name == "--port":
port = value
if name == "--start-datetime":
start_datetime = value
if name == "--stop-datetime":
stop_datetime = value
if name == "--start-position":
start_position = value
if name == "--stop-position":
stop_position = value
if name == "-d" or name == "--database":
database = value
if name == "--only-primary" :
only_primary = value
if binlog == '' :
print " : binlog !"
usage()
if user == '' :
print " : !"
usage()
if password == '' :
print " : !"
usage()
if database <> '' :
condition_database = "--database=" + "'" + database + "'"
else:
condition_database = ''
print " binlog....."
fileContent=os.popen("%s %s --base64-output=DECODE-ROWS --start-datetime='%s' --stop-datetime='%s' --start-position='%s' --stop-position='%s' %s\
|grep '###' -B 2|sed -e 's/### //g' -e 's/^INSERT/##INSERT/g' -e 's/^UPDATE/##UPDATE/g' -e 's/^DELETE/##DELETE/g' " \
%(mysqlbinlog_bin,binlog,start_datetime,stop_datetime,start_position,stop_position,condition_database)).read()
#print fileContent
# ----------------------------------------------------------------------------------------
# : binlog , result_dict
# ----------------------------------------------------------------------------------------
def init_col_name():
global result_dict
global pri_dict
global fileContent
result_dict = {}
pri_dict = {}
table_list = re.findall('`.*`\\.`.*`',fileContent)
table_list = list(set(table_list))
#table_list binlog
print " ....."
for table in table_list:
sname = table.split('.')[0].replace('`','')
tname = table.split('.')[1].replace('`','')
# id
try:
conn = MySQLdb.connect(host=host,user=user,passwd=password,port=int(port))
cursor = conn.cursor()
cursor.execute("select ordinal_position,column_name \
from information_schema.columns \
where table_schema='%s' and table_name='%s' " %(sname,tname))
result=cursor.fetchall()
if result == () :
print 'Warning:'+sname+'.'+tname+' '
#sys.exit()
result_dict[sname+'.'+tname]=result
cursor.execute("select ordinal_position,column_name \
from information_schema.columns \
where table_schema='%s' and table_name='%s' and column_key='PRI' " %(sname,tname))
pri=cursor.fetchall()
#print pri
pri_dict[sname+'.'+tname]=pri
cursor.close()
conn.close()
except MySQLdb.Error, e:
try:
print "Error %d:%s" % (e.args[0], e.args[1])
except IndexError:
print "MySQL Error:%s" % str(e)
sys.exit()
#print result_dict
#print pri_dict
# ----------------------------------------------------------------------------------------
# : sql,
# ----------------------------------------------------------------------------------------
def gen_rollback_sql():
global only_primary
fileOutput = open(output, 'w')
# '--' , sql
area_list=fileContent.split('--
')
#
print " sql....."
for area in area_list[::-1]:
# sql , sql sql
sql_list = area.split('##')
# pos timestamp
for sql_head in sql_list[0].splitlines():
sql_head = '#'+sql_head+'
'
fileOutput.write(sql_head)
# sql ,
for sql in sql_list[::-1][0:-1]:
try:
if sql.split()[0] == 'INSERT':
rollback_sql = re.sub('^INSERT INTO', 'DELETE FROM', sql, 1)
rollback_sql = re.sub('SET
', 'WHERE
', rollback_sql, 1)
tablename_pos = 2
table_name = rollback_sql.split()[tablename_pos].replace('`', '')
# sql
col_list = sorted(list(set(re.findall('@\d+', rollback_sql))))
# and,
rollback_sql = rollback_sql.replace('@1=', result_dict[table_name][0][1]+'=')
for col in col_list[1:]:
i = int(col[1:]) - 1
rollback_sql = rollback_sql.replace(col+'=', 'AND ' + result_dict[table_name][i][1]+'=',1)
# only_primary ,where
if int(only_primary) == 1 and pri_dict[table_name] <> ():
sub_where = ''
for primary in pri_dict[table_name]:
primary_name = primary[1]
for condition in rollback_sql.split('WHERE', 1)[1].splitlines():
if re.compile('^\s*'+primary_name).match(condition) or re.compile('^\s*AND\s*'+primary_name).match(condition):
sub_where = sub_where + condition + '
'
sub_where = re.sub('^\s*AND', '', sub_where, 1)
rollback_sql = rollback_sql.split('WHERE', 1)[0] + 'WHERE
' + sub_where
if sql.split()[0] == 'UPDATE':
rollback_sql = re.sub('SET
', '#SET#
', sql, 1)
rollback_sql = re.sub('WHERE
', 'SET
', rollback_sql, 1)
rollback_sql = re.sub('#SET#
', 'WHERE
', rollback_sql, 1)
tablename_pos = 1
table_name = rollback_sql.split()[tablename_pos].replace('`', '')
# sql
col_list = sorted(list(set(re.findall('@\d+', rollback_sql))))
# and,
rollback_sql = rollback_sql.replace('@1=', result_dict[table_name][0][1] + '=')
for col in col_list[1:]:
i = int(col[1:]) - 1
rollback_sql = rollback_sql.replace(col+'=', ',' + result_dict[table_name][i][1]+'=', 1).replace(col+'=','AND ' +result_dict[table_name][i][1]+'=')
# only_primary ,where
if int(only_primary) == 1 and pri_dict[table_name] <> ():
sub_where = ''
for primary in pri_dict[table_name]:
primary_name = primary[1]
for condition in rollback_sql.split('WHERE', 1)[1].splitlines():
if re.compile('^\s*' + primary_name).match(condition) or re.compile('^\s*AND\s*'+primary_name).match(condition):
sub_where = sub_where + condition + '
'
sub_where = re.sub('^\s*AND', '', sub_where, 1)
rollback_sql = rollback_sql.split('WHERE', 1)[0] + 'WHERE
' + sub_where
if sql.split()[0] == 'DELETE':
rollback_sql = re.sub('^DELETE FROM', 'INSERT INTO', sql, 1)
rollback_sql = re.sub('WHERE
', 'SET
', rollback_sql, 1)
tablename_pos = 2
table_name = rollback_sql.split()[tablename_pos].replace('`', '')
# sql
col_list = sorted(list(set(re.findall('@\d+', rollback_sql))))
# and,
rollback_sql = rollback_sql.replace('@1=', result_dict[table_name][0][1] + '=')
for col in col_list[1:]:
i = int(col[1:]) - 1
rollback_sql = rollback_sql.replace(col+'=', ',' + result_dict[table_name][i][1]+'=',1)
rollback_sql = re.sub('
$',';
',rollback_sql)
#print rollback_sql
fileOutput.write(rollback_sql)
except IndexError,e:
print "Error:%s" % str(e)
sys.exit()
print "done!"
def usage():
help_info="""==========================================================================================
Command line options :
--help # OUT : print help info
-f, --binlog # IN : binlog file. (required)
-o, --outfile # OUT : output rollback sql file. (default 'rollback.sql')
-h, --host # IN : host. (default '127.0.0.1')
-u, --user # IN : user. (required)
-p, --password # IN : password. (required)
-P, --port # IN : port. (default 3306)
--start-datetime # IN : start datetime. (default '1970-01-01 00:00:00')
--stop-datetime # IN : stop datetime. default '2070-01-01 00:00:00'
--start-position # IN : start position. (default '4')
--stop-position # IN : stop position. (default '18446744073709551615')
-d, --database # IN : List entries for just this database (No default value).
--only-primary # IN : Only list primary key in where condition (default 0)
Sample :
shell> python binlog_rollback.py -f 'mysql-bin.000001' -o '/tmp/rollback.sql' -h 192.168.0.1 -u 'user' -p 'pwd' -P 3307 -d dbname
=========================================================================================="""
print help_info
sys.exit()
if __name__ == '__main__':
getopts_parse_binlog()
init_col_name()
gen_rollback_sql()