mysqlbinlog闪回数据
mysql利用binlog恢复误操作数据
在人工手动进行一些数据库写操作的时候（比方说数据订正），尤其是一些不可控的批量更新或删除，通常都建议备份后操作。不过不怕万一，就怕一万，有备无患总是好的。在线上或者测试环境误操作导致数据被删除或者更新后，想要恢复，一般有两种方法。
方法一、利用最近的全量备份+增量binlog备份，恢复到误操作之前的状态，但是随着数据量的增大，binlog的增多，恢复起来很费时。
方法二、如果binlog的格式为row，那么就可以将binlog解析出来生成反向的原始sql
以下是利用方法二写的一个python脚本binlog_rollback.py，可利用此脚本生成反向的原始sql。
说明：
0、前提是binlog的格式为row
1、要恢复的表操作前后表结构没有发生变更，否则脚本无法解析
2、只生成dml（insert/update/delete）的rollback语句
3、最终生成的sql是逆序的，所以最新的dml会生成在输入文件的最前面，并且带上了时间戳和偏移点，方便查找目标
4、需要提供一个连接mysql的只读用户，主要是为了获取表结构
5、如果binlog过大，建议带上时间范围，也可以指定只恢复某个库的sql
6、sql生成后，请务必在测试环境上测试恢复后再应用到线上
脚本代码
#！/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（‘--\n’）
#逆序读取分块
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+‘\n’
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\n’， ‘where\n’， 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 + ‘\n’
sub_where = re.sub（‘^\s*and’， ‘’， sub_where， 1）
rollback_sql = rollback_sql.split（‘where’， 1）［0］ + ‘where\n’ + sub_where
if sql.split（）［0］ == ‘update’：
rollback_sql = re.sub（‘set\n’， ‘#set#\n’， sql， 1）
rollback_sql = re.sub（‘where\n’， ‘set\n’， rollback_sql， 1）
rollback_sql = re.sub（‘#set#\n’， ‘where\n’， 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 + ‘\n’
sub_where = re.sub（‘^\s*and’， ‘’， sub_where， 1）
rollback_sql = rollback_sql.split（‘where’， 1）［0］ + ‘where\n’ + sub_where
if sql.split（）［0］ == ‘delete’：
rollback_sql = re.sub（‘^delete from’， ‘insert into’， sql， 1）
rollback_sql = re.sub（‘where\n’， ‘set\n’， 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（‘\n$’，‘;\n’，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（）
演示
#首先创建一个只读账号
root:test》 grant select on *.* to ‘query’@‘127.0.0.1’ identified by ‘123456’;
query ok， 0 rows affected， 1 warning （0.01 sec）
#测试表结构如下
root:test》 create table `table1` （
-》 `id` int（11） not null auto_increment，
-》 `c1` int（11） default null，
-》 `c2` varchar（20） default null，
-》 `c3` int（11） default null，
-》 primary key （`id`）
-》 ）;
query ok， 0 rows affected （0.09 sec）
#插入三条数据
root:test》 insert into table1（c1，c2，c3） values （1，‘a’，1），（2，‘b’，2），（3，‘c’，3）;
query ok， 3 rows affected （0.01 sec）
records： 3 duplicates： 0 warnings： 0
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | b | 2 |
| 3 | 3 | c | 3 |
+----+------+------+------+
3 rows in set （0.00 sec）
#更新一条数据
root:test》 update table1 set c3=10 where id=3;
query ok， 1 row affected （0.01 sec）
rows matched： 1 changed： 1 warnings： 0
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | b | 2 |
| 3 | 3 | c | 10 |
+----+------+------+------+
3 rows in set （0.00 sec）
#删除一条数据
root:test》 delete from table1 where id=1;
query ok， 1 row affected （0.01 sec）
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 2 | 2 | b | 2 |
| 3 | 3 | c | 10 |
+----+------+------+------+
2 rows in set （0.00 sec）
接下来利用脚本来生成反向sql
［root@diandi ~］# python binlog_rollback.py -f /log/mysql/bin/mysql-bin.000002 -o rollback.sql -u query -p 123456 --start-datetime=‘2016-10-28 00:00:00’ -d test
正在获取参数。..。.
正在解析binlog.。..。
正在初始化列名。..。.
正在开始拼凑sql.。..。
done！
查看反向sql，最新的dml会生成在输入文件的最前面
［root@diandi ~］# cat rollback.sql
## at 155848
##161028 17:07:10 server id 22100 end_log_pos 155898 crc32 0x5000bca7 delete_rows： table id 351 flags： stmt_end_f
insert into `test`。`table1`
set
id=1
，c1=1
，c2=‘a’
，c3=1;
## at 155560
##161028 17:04:56 server id 22100 end_log_pos 155626 crc32 0x11d91e2d update_rows： table id 351 flags： stmt_end_f
update `test`。`table1`
set
id=3
，c1=3
，c2=‘c’
，c3=3
where
id=3
and c1=3
and c2=‘c’
and c3=10;
## at 155258
##161028 16:59:31 server id 22100 end_log_pos 155338 crc32 0x3978c1c1 write_rows： table id 351 flags： stmt_end_f
delete from `test`。`table1`
where
id=3
and c1=3
and c2=‘c’
and c3=3;
delete from `test`。`table1`
where
id=2
and c1=2
and c2=‘b’
and c3=2;
delete from `test`。`table1`
where
id=1
and c1=1
and c2=‘a’
and c3=1;
执行回滚操作
#直接source整个文件，table1将恢复到原来的空表状态（实际情况，在测试环境上按需索取，然后再恢复线上）
root:test》 source /root/rollback.sql
query ok， 1 row affected （0.01 sec）
query ok， 1 row affected （0.01 sec）
rows matched： 1 changed： 1 warnings： 0
query ok， 1 row affected （0.01 sec）
query ok， 1 row affected （0.01 sec）
query ok， 1 row affected （0.01 sec）
root:test》 select * from table1;
empty set （0.00 sec）
具体的参数使用方法如下：
［root@diandi ~］# python binlog_rollback.py
==========================================================================================
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
铜箔软连接常用铜箔0.1定制规格
《节奏空间》携手网易正式入华，现已开放预约！
一季度报出炉，蜂窝模组各大厂都在往哪“卷”？
高速电机驱动技术及其发展趋势
血管芯片可协助验证抗发炎化合物作用与成效
mysqlbinlog闪回数据
柔性智造属于那个行业类别
以太网与工业以太网的区别
一文详解600V稳压电路模块
通过CANopen转Modbus TCP网关的伺服系统
钽电容优点
激光雷达业务发展顺利 Waymo在2020年的估值将会大幅提升
对于未来市场上的三大趋势“模拟”“共享”“自由”分析
意法半导体携手微电子研究所将在碳化硅领域展开合作
谐振型开关电容变换器的工作原理和参数设计方法
留住你从前的开发环境的方法
手机RF抗干扰能力的测试
西门子洗碗机在中国市场占据绝对优势,呈现出碾压之势
单片机入门之数码管时钟
TI 通用电量计选型指南
在人工手动进行一些数据库写操作的时候（比方说数据订正），尤其是一些不可控的批量更新或删除，通常都建议备份后操作。不过不怕万一，就怕一万，有备无患总是好的。在线上或者测试环境误操作导致数据被删除或者更新后，想要恢复，一般有两种方法。
方法一、利用最近的全量备份+增量binlog备份，恢复到误操作之前的状态，但是随着数据量的增大，binlog的增多，恢复起来很费时。
方法二、如果binlog的格式为row，那么就可以将binlog解析出来生成反向的原始sql
以下是利用方法二写的一个python脚本binlog_rollback.py，可利用此脚本生成反向的原始sql。
说明：
0、前提是binlog的格式为row
1、要恢复的表操作前后表结构没有发生变更，否则脚本无法解析
2、只生成dml（insert/update/delete）的rollback语句
3、最终生成的sql是逆序的，所以最新的dml会生成在输入文件的最前面，并且带上了时间戳和偏移点，方便查找目标
4、需要提供一个连接mysql的只读用户，主要是为了获取表结构
5、如果binlog过大，建议带上时间范围，也可以指定只恢复某个库的sql
6、sql生成后，请务必在测试环境上测试恢复后再应用到线上
脚本代码
#！/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（‘--\n’）
#逆序读取分块
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+‘\n’
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\n’， ‘where\n’， 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 + ‘\n’
sub_where = re.sub（‘^\s*and’， ‘’， sub_where， 1）
rollback_sql = rollback_sql.split（‘where’， 1）［0］ + ‘where\n’ + sub_where
if sql.split（）［0］ == ‘update’：
rollback_sql = re.sub（‘set\n’， ‘#set#\n’， sql， 1）
rollback_sql = re.sub（‘where\n’， ‘set\n’， rollback_sql， 1）
rollback_sql = re.sub（‘#set#\n’， ‘where\n’， 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 + ‘\n’
sub_where = re.sub（‘^\s*and’， ‘’， sub_where， 1）
rollback_sql = rollback_sql.split（‘where’， 1）［0］ + ‘where\n’ + sub_where
if sql.split（）［0］ == ‘delete’：
rollback_sql = re.sub（‘^delete from’， ‘insert into’， sql， 1）
rollback_sql = re.sub（‘where\n’， ‘set\n’， 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（‘\n$’，‘;\n’，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（）
演示
#首先创建一个只读账号
root:test》 grant select on *.* to ‘query’@‘127.0.0.1’ identified by ‘123456’;
query ok， 0 rows affected， 1 warning （0.01 sec）
#测试表结构如下
root:test》 create table `table1` （
-》 `id` int（11） not null auto_increment，
-》 `c1` int（11） default null，
-》 `c2` varchar（20） default null，
-》 `c3` int（11） default null，
-》 primary key （`id`）
-》 ）;
query ok， 0 rows affected （0.09 sec）
#插入三条数据
root:test》 insert into table1（c1，c2，c3） values （1，‘a’，1），（2，‘b’，2），（3，‘c’，3）;
query ok， 3 rows affected （0.01 sec）
records： 3 duplicates： 0 warnings： 0
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | b | 2 |
| 3 | 3 | c | 3 |
+----+------+------+------+
3 rows in set （0.00 sec）
#更新一条数据
root:test》 update table1 set c3=10 where id=3;
query ok， 1 row affected （0.01 sec）
rows matched： 1 changed： 1 warnings： 0
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | b | 2 |
| 3 | 3 | c | 10 |
+----+------+------+------+
3 rows in set （0.00 sec）
#删除一条数据
root:test》 delete from table1 where id=1;
query ok， 1 row affected （0.01 sec）
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 2 | 2 | b | 2 |
| 3 | 3 | c | 10 |
+----+------+------+------+
2 rows in set （0.00 sec）
接下来利用脚本来生成反向sql
［root@diandi ~］# python binlog_rollback.py -f /log/mysql/bin/mysql-bin.000002 -o rollback.sql -u query -p 123456 --start-datetime=‘2016-10-28 00:00:00’ -d test
正在获取参数。..。.
正在解析binlog.。..。
正在初始化列名。..。.
正在开始拼凑sql.。..。
done！
查看反向sql，最新的dml会生成在输入文件的最前面
［root@diandi ~］# cat rollback.sql
## at 155848
##161028 17:07:10 server id 22100 end_log_pos 155898 crc32 0x5000bca7 delete_rows： table id 351 flags： stmt_end_f
insert into `test`。`table1`
set
id=1
，c1=1
，c2=‘a’
，c3=1;
## at 155560
##161028 17:04:56 server id 22100 end_log_pos 155626 crc32 0x11d91e2d update_rows： table id 351 flags： stmt_end_f
update `test`。`table1`
set
id=3
，c1=3
，c2=‘c’
，c3=3
where
id=3
and c1=3
and c2=‘c’
and c3=10;
## at 155258
##161028 16:59:31 server id 22100 end_log_pos 155338 crc32 0x3978c1c1 write_rows： table id 351 flags： stmt_end_f
delete from `test`。`table1`
where
id=3
and c1=3
and c2=‘c’
and c3=3;
delete from `test`。`table1`
where
id=2
and c1=2
and c2=‘b’
and c3=2;
delete from `test`。`table1`
where
id=1
and c1=1
and c2=‘a’
and c3=1;
执行回滚操作
#直接source整个文件，table1将恢复到原来的空表状态（实际情况，在测试环境上按需索取，然后再恢复线上）
root:test》 source /root/rollback.sql
query ok， 1 row affected （0.01 sec）
query ok， 1 row affected （0.01 sec）
rows matched： 1 changed： 1 warnings： 0
query ok， 1 row affected （0.01 sec）
query ok， 1 row affected （0.01 sec）
query ok， 1 row affected （0.01 sec）
root:test》 select * from table1;
empty set （0.00 sec）
具体的参数使用方法如下：
［root@diandi ~］# python binlog_rollback.py
==========================================================================================
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
铜箔软连接常用铜箔0.1定制规格
《节奏空间》携手网易正式入华，现已开放预约！
一季度报出炉，蜂窝模组各大厂都在往哪“卷”？
高速电机驱动技术及其发展趋势
血管芯片可协助验证抗发炎化合物作用与成效
mysqlbinlog闪回数据
柔性智造属于那个行业类别
以太网与工业以太网的区别
一文详解600V稳压电路模块
通过CANopen转Modbus TCP网关的伺服系统
钽电容优点
激光雷达业务发展顺利 Waymo在2020年的估值将会大幅提升
对于未来市场上的三大趋势“模拟”“共享”“自由”分析
意法半导体携手微电子研究所将在碳化硅领域展开合作
谐振型开关电容变换器的工作原理和参数设计方法
留住你从前的开发环境的方法
手机RF抗干扰能力的测试
西门子洗碗机在中国市场占据绝对优势,呈现出碾压之势
单片机入门之数码管时钟
TI 通用电量计选型指南