社区首页 >专栏 >监控因为带宽打满造成的 MySQL 复制延迟

监控因为带宽打满造成的 MySQL 复制延迟

用户1148526

发布于 2024-04-29 00:13:13

14800

代码可运行

文章被收录于专栏：Hadoop数据仓库Hadoop数据仓库

运行总次数：0

代码可运行

1. 写表版本

2. 写文件版本

1. 写表版本

监控脚本文件 get_Gtid_totable.sh 内容如下：

#!/bin/bash

source ~/.bash_profile

# 获取主库 binlog 位点
a=`mysql -uroot -p123456 -h172.30.4.6 -P3306 -e "show master status\G" 2>/dev/null | egrep 'f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7' | sed 's/,//' | awk -F: '{print $2}' | awk -F"-" '{print $2}'`

# 获取从库接收和执行 binlog 位点
b=`mysql -uroot -p123456 -S /data3/3306/mysqldata/mysql.sock -e "show slave status\G" 2>/dev/null | egrep 'f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7' | egrep -v "Master_UUID" | sed -e '1s/f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7:1-//g' | sed -e '2s/f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7:1-//g'`

# 获取从库延迟秒数
c=`mysql -uroot -p123456 -S /data3/3306/mysqldata/mysql.sock -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | sed 's/Seconds_Behind_Master: //g'`
Seconds_Behind_Master=`echo $c`

master_Executed_Gtid=`echo $a`
slave_Retrieved_Gtid=`echo $b | awk '{print $1}' | sed 's/.$//'`
slave_Executed_Gtid=`echo $b | awk '{print $2}' | sed 's/.$//'`

# 入库
mysql -h172.18.16.156 -P3306 -uroot -p123456 -e "
insert into test.t_lag_172_18_27_238 (master_Executed_Gtid, slave_Retrieved_Gtid, slave_Executed_Gtid,Seconds_Behind_Master)
values ("$master_Executed_Gtid","$slave_Retrieved_Gtid","$slave_Executed_Gtid","$Seconds_Behind_Master");"

只监控业务高峰期，用 cron 调度执行：

* 19-23 * * * /home/mysql/get_Gtid_totable.sh

建表语句：

-- mysql 8
use test;
CREATE TABLE `t_lag_172_18_27_238` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `master_Executed_Gtid` bigint(20) DEFAULT NULL,
  `slave_Retrieved_Gtid` bigint(20) DEFAULT NULL,
  `slave_Executed_Gtid` bigint(20) DEFAULT NULL,
  `rlag` int(11) DEFAULT (greatest((`master_Executed_Gtid` - `slave_Retrieved_Gtid`),0)),
  `elag` int(11) DEFAULT (greatest((`slave_Retrieved_Gtid` - `slave_Executed_Gtid`),0)),
  `Seconds_Behind_Master` int(11) DEFAULT NULL
);

查询语句：

mysql -h172.18.16.156 -P3306 -uroot -p123456 -Dtest -e "select ts \"时间\",rlag \"接收binlog落后事务数\",elag \"执行binlog落后事务数\",Seconds_Behind_Master \"延迟秒数\"from t_lag_172_18_27_238 where date(ts)='2024-04-25' order by Seconds_Behind_Master desc limit 10;" 2>/dev/null

结果如下：

+---------------------+-----------------------------+-----------------------------+--------------+
| 时间                | 接收binlog落后事务数        | 执行binlog落后事务数        | 延迟秒数     |
+---------------------+-----------------------------+-----------------------------+--------------+
| 2024-04-25 22:43:01 |                      144230 |                         112 |          119 |
| 2024-04-25 22:46:02 |                      137649 |                         981 |          118 |
| 2024-04-25 22:25:02 |                      146937 |                         186 |          115 |
| 2024-04-25 22:44:01 |                      135479 |                         898 |          114 |
| 2024-04-25 22:24:01 |                      140064 |                         258 |          112 |
| 2024-04-25 22:49:01 |                      146843 |                         228 |          111 |
| 2024-04-25 22:53:02 |                      146033 |                        2032 |          110 |
| 2024-04-25 22:26:02 |                      140634 |                           9 |          110 |
| 2024-04-25 22:23:01 |                      137269 |                         747 |          109 |
| 2024-04-25 22:40:02 |                      127012 |                         350 |          108 |
+---------------------+-----------------------------+-----------------------------+--------------+

接收binlog落后事务数很大，执行binlog落后事务数很小，说明引起复制延迟的瓶颈是带宽不足造成主库 binlog 无法及时传输到从库。复制延迟不是做的慢，而是收不到。

2. 写文件版本

监控脚本文件 get_Gtid_totable.sh 内容如下：

#!/bin/bash

source ~/.bash_profile

time1=$(date "+%Y-%m-%d %H:%M:%S").$((`date "+%N"`/1000000))

a=`mysql -uroot -p123456 -h172.30.4.6 -P3306 -e "show master status\G" 2>/dev/null | egrep 'f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7' | sed 's/,//' | awk -F: '{print $2}' | awk -F"-" '{print $2}'`

time2=$(date "+%Y-%m-%d %H:%M:%S").$((`date "+%N"`/1000000))

b=`mysql -uroot -p123456 -S /data3/3306/mysqldata/mysql.sock -e "show slave status\G" 2>/dev/null | egrep 'f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7' | egrep -v "Master_UUID" | sed -e '1s/f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7:1-/ Retrieved_Gtid: /g' | sed -e '2s/f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7:1-/  Executed_Gtid: /g'`

echo -e " 主库Gtid："
echo $time1
echo -e "  Executed_Gtid: ""$a"
echo;
echo -e " 从库Gtid："
echo $time2
echo "$b" | sed 's/.$//'