mysql（mariadb） binlog 分析加强版脚本

首先感谢mysql的运维内参一书的大神

在原有功能的基础上增加2个功能

1.打印每个事务的大小，单位byte

2.如果遇到load data操作，醒目提示

#!/bin/bash
cat $1|awk \
'BEGIN {xid="null";s_type=""; stm="";endtm="";intsta=0;inttal=0;s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;bf=0;period=0;sz=0;start_byte_flag=0;start_byte=0;stop_byte=0;size=0} \
{
if (match($0, /^(#.*load data.*)/)) {print "!!!found load data at:" NR "\n\n"} \
if (match($0, /^(BEGIN)/)) {bg=1;sz=1} \
if (match($0, /^(\/\*\!\*\/\;)/) && sz==1) {start_byte_flag=1} \
if (match($0, /^(# at) /) && start_byte_flag==1 && sz==1) {start_byte=$3;sz=0;start_byte_flag=0} \
if (match($0, /#.*server id/)) {if(bg==1){statm=substr($1,2,6)" "$2;cmd=sprintf("date -d \"%s\" +%%s", statm);cmd|getline intsta;close(cmd);bg=0;bf=1;}else if(bf==1){endtm=substr($1,2,6)" "$2;cmd=sprintf("date -d \"%s\" +%%s", endtm);cmd|getline inttal;close(cmd);}} \
if(match($0, /#.*Table_map:.*mapped to number/)) {printf "Timestamp : " $1 " " $2 " Table : " $(NF-4); flag=1} \
else if (match($0, /#.*Xid =.*/)) {xid=$(NF);stop_byte=$(NF-3)} \
else if (match($0, /(### INSERT INTO .*..*)/)) {count=count+1;insert_count=insert_count+1;s_type="INSERT"; s_count=s_count+1;}  \
else if (match($0, /(### UPDATE .*..*)/)) {count=count+1;update_count=update_count+1;s_type="UPDATE"; s_count=s_count+1;} \
else if (match($0, /(### DELETE FROM .*..*)/)) {count=count+1;delete_count=delete_count+1;s_type="DELETE"; s_count=s_count+1;}  \
else if (match($0, /^(# at) /) && flag==1 && s_count>0) {print " Query Type : "s_type " " s_count " row(s) affected" ;s_type=""; s_count=0; }  \
else if (match($0, /^(COMMIT)/)) {period=inttal-intsta;size=stop_byte-start_byte;if(inttal==0){period=0};print "[Transaction total : " count " Insert(s) : " insert_count " Update(s) : " update_count " Delete(s) : " \
delete_count " Xid : "xid" period : "period"  size : "size" ] \n\n"; \
count=0;insert_count=0;update_count=0; delete_count=0;s_type=""; s_count=0; flag=0;bf=0;bg=0; sz=0;start_byte_flag=0;start_byte=0;stop_byte=0;size=0} } '

使用方法:report文件为decode之后的纯文本文件

./mysql_binlog binlog.report2