测试：python 3.5 、mysql 5.7
mysql 参数：binlog_rows_query_log_events = on ;binlog_format = row
#coding:utf-8
importos
del_sql_file= r"/export/bak/del_file.txt"binlog_scr= r"ls /export/data/mysql/data/mysql-bin.0*"binlog_list=os.popen(binlog_scr).readlines()#循环读取所有binlog
for log_line inbinlog_list:if len(log_line) >0:
binlog_file=log_line.strip()print(binlog_file)#解析binlog
comn = r"/export/servers/mysql/bin/mysqlbinlog -vvv {}".format(binlog_file)
binlog_sql=os.popen(comn).readlines()
del_sql= ""new_sql_flag=Falsefor line inbinlog_sql:
lower_line=line.lower()#记录含有delete的原生sql
if "delete" in lower_line and lower_line.count("#") == 1 and "server id" not inlower_line:
new_sql_flag=True
del_sql=lineelif "# at" inlower_line:
new_sql_flag=Falseelifnew_sql_flag:#原生sql有多行时，将多行进行合并
del_sql = "{} {}".format(del_sql, line)if line.find("Table_map:") >0:#获取时间戳
start_index = 1end_index= line.index("server id", start_index)
current_time=line[start_index:end_index].strip()#获取库名、表名
tb_start_index = line.index("Table_map", start_index) + 11tb_end_index= line.index("mapped", tb_start_index)
table_info= line[tb_start_index:tb_end_index].strip().replace("`", "")#将特定表的delete 信息记录到文件中
if "rt_out_orders_m" in table_info and len(del_sql) >0:
with open(del_sql_file,"a+", encoding="utf-8") as f:
f.write("{} {}\n".format(current_time, table_info))
f.write(del_sql)#遇到 Table_map 关键字则将del_sql清空，重新记录
del_sql = ""
测试结果：
200320 23:01:02 ob_task.rt_out_orders_m
# DELETE FROM ob_task.rt_out_orders_m WHERE RECEIPT_NO = 'EBS4418047049205' limit 10
200320 23:01:02 ob_task.rt_out_orders_m
# DELETE FROM ob_task.rt_out_orders_m WHERE RECEIPT_NO = 'EBS4418047040735' limit 10
200320 23:01:02 ob_task.rt_out_orders_m
# DELETE FROM ob_task.rt_out_orders_m WHERE RECEIPT_NO = 'EBS4418047051006' limit 10
转播
