如何通过binlog定位大事务？

如何通过binlog定位大事务

今天为大家介绍如何通过binlog定位大事务方面的讲解，一起来看看吧！

1、序

大事务想必大家都遇到过，既然要对大事务进行拆分，第一步就是要找到它。那么如何通过binlog来定位到大事务呢？

首先，可通过binlog文件的大小来判断是否存在大事务，当一个binlog文件快被写完时，突然出现大事务，会突破 max_binlog_size 的大小继续写入。

官方文档中是这样描述的：

A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

根据这个特点，只要进入 binlog 的存放目录，观察到文件大小异常的 binlog，那么你就可以去解析这个 binlog 获取大事务了。

当然，需要注意的是，这只是一部分，文件大小正常的 binlog 中也藏着大事务。

2、实践

既然要定位大事务的SQL，针对已开启 GTID 的实例，只要定位到对应的GTID即可，下面我们开始对一个binlog进行解析:

首先，我们解析出一个binlog中按照事务大小排名前 N 的事务。

# 为了方便保存为脚本，这里定义几个基本的变量
BINLOG_FILE_NAME=$1                       # binlog文件名
TRANS_NUM=$2                              # 想要获取的事务数量
MYSQL_BIN_DIR='/data/mysql/3306/base/bin' # basedir
# 获取前TRANS_NUM个大事务
${MYSQL_BIN_DIR}/mysqlbinlog ${BINLOG_FILE_NAME} | grep "GTID$(printf '\t')last_committed" -B 1  | grep -E '^# at' | awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp,tmp);tmp=$1}' | sort -n -r -k 1 | head -n ${TRANS_NUM} > binlog_init.tmp

经过第一步对 binlog 的基本解析后，我们已经拿到了对应事务的大小和可供定位 GTID 的 POS 信息，接下来对上述输出的临时文件进行逐行解析，针对每一个事务获取到相应的信息。

while read line
do
    # 事务大小这里取近似值，因为不是通过(TRANS_END_POS-TRANS_START_POS)计算出的
    TRANS_SIZE=$(echo ${line} | awk '{print $1}')
    logWriteWarning "TRANS_SIZE: $(echo | awk -v TRANS_SIZE=${TRANS_SIZE} '{ print (TRANS_SIZE/1024/1024) }')MB"
    FLAG_POS=$(echo ${line} | awk '{print $2}')
    # 获取GTID
    ${MYSQL_BIN_DIR}/mysqlbinlog -vvv --base64-output=decode-rows ${BINLOG_FILE_NAME} | grep -m 1 -A3 -Ei "^# at ${FLAG_POS}" > binlog_parse.tmp
    GTID=$(cat binlog_parse.tmp | grep -i 'SESSION.GTID_NEXT' | awk -F "'" '{print $2}')
    # 通过GTID解析出事务的详细信息
    ${MYSQL_BIN_DIR}/mysqlbinlog --base64-output=decode-rows -vvv --include-gtids="${GTID}" ${BINLOG_FILE_NAME} > binlog_gtid.tmp
    START_TIME=$(grep -Ei '^BEGIN' -m 1 -A 3 binlog_gtid.tmp | grep -i 'server id' | awk '{print $1,$2}' | sed 's/#//g')
    END_TIME=$(grep -Ei '^COMMIT' -m 1 -B 1 binlog_gtid.tmp | head -1 | awk '{print $1,$2}' | sed 's/#//g')
    TRANS_START_POS=$(grep -Ei 'SESSION.GTID_NEXT' -m 1 -A 1 binlog_gtid.tmp | tail -1 | awk '{print $3}')
    TRANS_END_POS=$(grep -Ei '^COMMIT' -m 1 -B 1 binlog_gtid.tmp | head -1 | awk '{print $7}')
    # 输出
    logWrite "GTID: ${GTID}"
    logWrite "START_TIME: $(date -d "${START_TIME}" '+%F %T')"
    logWrite "END_TIME: $(date -d "${END_TIME}" '+%F %T')"
    logWrite "TRANS_START_POS: ${TRANS_START_POS}"
    logWrite "TRANS_END_POS: ${TRANS_END_POS}"
    # 统计对应的DML语句数量
    logWrite "该事务的DML语句及相关表统计:"
    grep -Ei '^### insert' binlog_gtid.tmp | sort | uniq -c
    grep -Ei '^### delete' binlog_gtid.tmp | sort | uniq -c
    grep -Ei '^### update' binlog_gtid.tmp | sort | uniq -c
done < binlog_init.tmp