mysql大事务定位，mysql从库落后大事务分析

1、问题背景

在MySQL运维过程中，大事务（即涉及大量数据更新的事务）不受欢迎，因其往往会引发诸多维护难题。

在MySQL维护工作中，我们需要重点留意是否存在大事务，并从binlog中寻找相关迹象。

下面将介绍如何确定大事务的大小。

2、模拟大事务

mysql> create table t(x int primary key auto_increment);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t() values(),(),(),();
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into t(x) select x +(select count(1) from t)from t;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into t(x) select x +(select count(1) from t)from t;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

...........

mysql> insert into t(x) select x +(select count(1) from t)from t;
Query OK, 33554432 rows affected (1 min 55.20 sec)
Records: 33554432 Duplicates: 0 Warnings: 0

 

执行上述操作后，binlog文件大小出现异常。

 

 

3、定位大事务的大小

注意：以下所有步骤，均需在开启GTID模式的前提下进行。

3.1、解析binlog文件

我们可以使用以下命令来初步查看binlog文件内容：

/data/mysql/base/5.7.25/bin/mysqlbinlog ./mysql-bin.000885 | head -n 40

 

在GTID模式下，事务起始处通常会有一个GTID_event，如：

信息 说明

at 194 pos点位置信息

set @@session.gtid_next=''; 代表一个事务的开始，包含事务的GTID信息我们还可以使用以下命令进一步筛选关键信息：

/data/mysql/base/5.7.25/bin/mysqlbinlog ./mysql-bin.000885 | grep "GTID$(printf '\t')last_committed" -B 1 | head -n 20

 

 

这里使用到grep两个技巧：

　　通过$(printf '\t')插入tab字符，用于过滤包含特定tab分隔内容的行。

　　使用-B 1参数，表示在匹配到的行之前显示1行，用于获取“at xxx”这一行，即GTID_event在binlog中的位置。

3.2、获取binlog文件的pos点位置信息

使用如下命令获取binlog文件中所有事务的pos点位置信息：

/data/mysql/base/5.7.25/bin/mysqlbinlog ./mysql-bin.000885 \
| grep "GTID$(printf '\t')last_committed" -B 1 \
|grep -E '^# at' | awk '{print $3}' \
| head -n 40

 

3.3、获取单个事务在binlog中的大小

通过以下命令计算每个事务在binlog中的大小：

/data/mysql/base/5.7.25/bin/mysqlbinlog ./mysql-bin.000885 \
| 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=$1}' \
| head -n 30

 

3.4、获取最大事务的大小

执行以下命令获取该binlog中最大的30个事务的大小：

/data/mysql/base/5.7.25/bin/mysqlbinlog ./mysql-bin.000885 \
| 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=$1}' \
| sort -n -r | head -n 30

 

假设通过上述操作，找到某个事务在binlog中的大小为80.34MB(84245335字节/1024/1204)。

 

转自：原文链接：https://blog.csdn.net/weixin_47056515/article/details/146132960