binlog分析脚本

binlog_analyze.sh

#!/bin/bash
## 给定binlog目录及文件名称
BINLOG_FILE=$*
# 确保mysqlbinlog可用
BINLOG_CLI=${BINLOG_CLI:-mysqlbinlog}
which "$BINLOG_CLI" >/dev/null 2>&1
if test $? -ne 0; then
  echo -e "  \033[1;33m Example1:bash $(basename $0)  mysql_bin.000750 \033[m"
  echo -e "  \033[1;33m Example2:bash $(basename $0)  /data/dblogs/mysql3366/binlogs/mysql_bin.000749 \033[m"
  exit 1
fi

if test $# -ne 1; then
  echo -e "  \033[1;33m Example:bash $(basename $0)  /data/dblogs/mysql3366/binlogs/mysql_bin.000750 \033[m"
  exit 1
fi
## 具体解析命令
$BINLOG_CLI --base64-output=decode-rows -vv ${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, /#.*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} } ' >/tmp/analyze_binlog.log

echo "该binlog中 所有类型操作超过1000行的操作"
cat /tmp/analyze_binlog.log |grep -E 'Table' | awk '{if($12>1000) print $0}'

echo "该binlog中 单个操作影响行数的top 5 "
cat analyze_binlog.log | grep Table | sort -nr -k 12 |head -5

echo "该binlog中 操作最多的表 top 5"
cat /tmp/analyze_binlog.log | grep Table |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr|head -5

echo "该binlog中 删除操作最多的表top 5"
cat /tmp/analyze_binlog.log |grep -E 'DELETE' |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr|head -5

echo "该binlog中 UPDATE操作最多的表top 5"
cat /tmp/analyze_binlog.log |grep -E 'UPDATE' |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr|head -5

echo "该binlog中 INSERT操作最多的表top 5"
cat /tmp/analyze_binlog.log |grep -E 'INSERT' |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr|head -5

cat << "EOF"
1.查看该binlog中 操作最多的表
cat analyze_binlog.log | grep Table |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr
2.查看该binlog中 删除操作最多的表
cat analyze_binlog.log |grep -E 'DELETE' |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr
3.查看该binlog中 指定库表的操作统计
cat analyze_binlog.log |grep -i '`demo`.`t1`' | awk '{print $7 " " $11}' | sort -k1,2 | uniq -c
4.查看该binlog中 单个操作影响行数的top 3
cat analyze_binlog.log | grep Table | sort -nr -k 12 | head -n 3
5.查看该binlog中 删除超过1000行的操作
cat analyze_binlog.log |grep -E 'DELETE' | awk '{if($12>1000) print $0}'
6.查看该binlog中 所有类型操作超过1000行的操作
cat analyze_binlog.log |grep -E 'Table' | awk '{if($12>1000) print $0}'
EOF