目录
1. 写表版本
2. 写文件版本
1. 写表版本
监控脚本文件 get_Gtid_totable.sh 内容如下：
代码语言：javascript复制#!/bin/bash
source ~/.bash_profile
# 获取主库 binlog 位点
a=`mysql -uroot -p123456 -h172.30.4.6 -P3306 -e "show master statusG" 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 statusG" 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 statusG" 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 调度执行：
代码语言：javascript复制* 19-23 * * * /home/mysql/get_Gtid_totable.sh建表语句：
代码语言：javascript复制-- 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
);查询语句：
代码语言：javascript复制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结果如下：
代码语言：javascript复制 --------------------- ----------------------------- ----------------------------- --------------
| 时间 | 接收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 内容如下：
代码语言：javascript复制#!/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 statusG" 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 statusG" 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/.$//'
