mysqlbinlog过滤表_python分析mysqlbinlog并记录特定表的delete记录

测试&＃xff1a;python 3.5 、mysql 5.7

mysql 参数&＃xff1a;binlog_rows_query_log_events &＃61;  on ;binlog_format &＃61; row

#coding:utf-8

importos

del_sql_file&＃61; r"/export/bak/del_file.txt"binlog_scr&＃61; r"ls /export/data/mysql/data/mysql-bin.0*"binlog_list&＃61;os.popen(binlog_scr).readlines()#循环读取所有binlog

for log_line inbinlog_list:if len(log_line) >0:

binlog_file&＃61;log_line.strip()print(binlog_file)#解析binlog

comn &＃61; r"/export/servers/mysql/bin/mysqlbinlog -vvv {}".format(binlog_file)

binlog_sql&＃61;os.popen(comn).readlines()

del_sql&＃61; ""new_sql_flag&＃61;Falsefor line inbinlog_sql:

lower_line&＃61;line.lower()#记录含有delete的原生sql

if "delete" in lower_line and lower_line.count("#") &＃61;&＃61; 1 and "server id" not inlower_line:

new_sql_flag&＃61;True

del_sql&＃61;lineelif "# at" inlower_line:

new_sql_flag&＃61;Falseelifnew_sql_flag:#原生sql有多行时&＃xff0c;将多行进行合并

del_sql &＃61; "{} {}".format(del_sql, line)if line.find("Table_map:") >0:#获取时间戳

start_index &＃61; 1end_index&＃61; line.index("server id", start_index)

current_time&＃61;line[start_index:end_index].strip()#获取库名、表名

tb_start_index &＃61; line.index("Table_map", start_index) &＃43; 11tb_end_index&＃61; line.index("mapped", tb_start_index)

table_info&＃61; line[tb_start_index:tb_end_index].strip().replace("&＃96;", "")#将特定表的delete 信息记录到文件中

if "rt_out_orders_m" in table_info and len(del_sql) >0:

with open(del_sql_file,"a&＃43;", encoding&＃61;"utf-8") as f:

f.write("{} {}\n".format(current_time, table_info))

f.write(del_sql)#遇到 Table_map 关键字则将del_sql清空&＃xff0c;重新记录

del_sql &＃61; ""

测试结果&＃xff1a;

200320 23:01:02 ob_task.rt_out_orders_m

# DELETE FROM ob_task.rt_out_orders_m WHERE RECEIPT_NO &＃61; &＃39;EBS4418047049205&＃39; limit 10

200320 23:01:02 ob_task.rt_out_orders_m

# DELETE FROM ob_task.rt_out_orders_m WHERE RECEIPT_NO &＃61; &＃39;EBS4418047040735&＃39; limit 10

200320 23:01:02 ob_task.rt_out_orders_m

# DELETE FROM ob_task.rt_out_orders_m WHERE RECEIPT_NO &＃61; &＃39;EBS4418047051006&＃39; limit 10