统计binlog日志中INSERT/UPDATE/DELETE 事件摘要

工作中经常会遇到此类需求，如统计binlog中是否存大事物、对事物事件摘要进行统计（事物涉及了那些表表、事物执行时间是什么时候、是更新操作多还是删除操作多），以下awk 脚本，它将使用 mysqlbinlog 输出并从基于行的二进制日志中打印出一个漂亮的 INSERT/UPDATE/DELETE 事件摘要信息摘要脚本。

summarize_binlogs.sh脚本内容：

#!/bin/bash
BINLOG_FILE="/data/mysql-bin.000473"
START_TIME="2021-01-16 13:30:00"
STOP_TIME="2021-01-16 14:00:00"
mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="${START_TIME}"  --stop-datetime="${STOP_TIME}" ${BINLOG_FILE} | awk \
'BEGIN {s_type=""; s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;} \
{if(match($0, /#21.*Table_map:.*mapped to number/)) {printf "Timestamp : " $1 " " $2 " Table : " $(NF-4); flag=1} \
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)/)) {print "[Transaction total : " count " Insert(s) : " insert_count " Update(s) : " update_count " Delete(s) : " \
delete_count "] \n+----------------------+----------------------+----------------------+----------------------+"; \
count=0;insert_count=0;update_count=0; delete_count=0;s_type=""; s_count=0; flag=0} } '

注意:
脚本中第10行中的字符串“#21”是 2021 年。如果正在解码 2020 年的二进制日志文件，只需将其更改为“#20”

使用方法

1 给脚本添加执行权限

chmod u+x summarize_binlogs.sh

2 执行脚本对binglog事件进行解析

./summarize_binlogs.sh
Timestamp : #150116 13:42:13 Table : `sakila`.`payment_tmp` Query Type : INSERT 16049 row(s) affected
[Transaction total : 16049 Insert(s) : 16049 Update(s) : 0 Delete(s) : 0] 
+----------------------+----------------------+----------------------+----------------------+
Timestamp : #150116 13:42:20 Table : `sakila`.`payment_tmp` Query Type : DELETE 5001 row(s) affected
[Transaction total : 5001 Insert(s) : 0 Update(s) : 0 Delete(s) : 5001] 
+----------------------+----------------------+----------------------+----------------------+
Timestamp : #150116 13:42:28 Table : `sakila`.`payment_tmp` Query Type : UPDATE 6890 row(s) affected
[Transaction total : 6890 Insert(s) : 0 Update(s) : 6890 Delete(s) : 0] 
+----------------------+----------------------+----------------------+----------------------+
Timestamp : #150116 13:42:42 Table : `sakila`.`country` Query Type : DELETE 1 row(s) affected
Timestamp : #150116 13:42:48 Table : `sakila`.`city` Query Type : DELETE 1 row(s) affected
Timestamp : #150116 13:42:53 Table : `sakila`.`address` Query Type : DELETE 2 row(s) affected
[Transaction total : 4 Insert(s) : 0 Update(s) : 0 Delete(s) : 4] 
+----------------------+----------------------+----------------------+----------------------+

输出说明
时间戳：记录在二进制日志中的事件时间戳
表：database.tablename
查询类型：对表执行的查询类型和受查询影响的行数。
[Transaction total : 6890 Insert(s) : 0 Update(s) : 6890 Delete(s) : 0]
上面一行打印事务摘要，显示受事务影响的总行数，受每个事务影响的总行数事务中的查询类型。
对于最后一个事务，它显示了对应于事务中执行的每个语句的 3 行，然后是事务的最终摘要，即事务总共影响了 4 行，并且它们都被删除了。

3 用途
哪些表收到最多的插入/更新/删除语句？

./summarize_binlogs.sh | grep Table |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr

哪个表收到的 DELETE 查询次数最多？

./summarize_binlogs.sh | grep -E 'DELETE' |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr

针对 sakila.country 表执行了多少插入/更新/删除查询？

./summarize_binlogs.sh | grep -i '`sakila`.`country`' | awk '{print $7 " " $11}' | sort -k1,2 | uniq -c

请给出影响最大行数的前 3 条语句

./summarize_binlogs.sh | grep Table | sort -nr -k 12 | head -n 3

查找影响超过 1000 行的 DELETE 查询

./summarize_binlogs.sh | grep -E 'DELETE' | awk '{if($12>1000) print $0}'

获取影响超过 1000 行的所有查询

./summarize_binlogs.sh | grep -E 'Table' | awk '{if($12>1000) print $0}'