对于mysql/ target=_blank class=infotextkey>mysql数据库主从复制延迟的监控,可以借助percona的有力武器pt-heartbeat来实现。
pt-heartbeat通过使用时间戳方式在主库上更新特定表,然后在从库上读取被更新的时间戳然后与本地系统时间对比来得出其延迟。
1、MySQL主从延迟监控脚本
[mysql@SZDB run]$ more ck_slave_lag.sh
#!/bin/bash
#set -x
if [ $# -ne 3 ];then
echo "usage:"
echo "ck_slave_lag.sh <Servier-id> <MaxLag> <LogDir>"
exit 0;
fi
ServerID=$1
MaxLag=$2
LogDir=$3
Timestamp=`date +%Y%m%d_%H%M%S`
Rentition=7
LogFile=$LogDir/slave_lag_$Timestamp.log
LagDetail=$LogDir/slave_lag_Detail_$Timestamp.log
mailadd=leshami@12306.cn
echo $ServerID
echo $MaxLag
echo $LogDir
echo $LogFile
echo $LagDetail
echo $mailadd
if [ ! -d $LogDir ];then
mkdir -p $LogDir
fi
Lag=`/usr/bin/pt-heartbeat --user=monitor --password=xxx -S /tmp/mysql.sock -D test --master-server-id=$ServerID --check`
Lag=`echo ${Lag%.*}`
#Lag=3
echo $Lag
ptStatus=`ps -ef|grep pt-heart|grep daemonize`
echo $ptStatus
if [ $Lag -gt $MaxLag ]; then
echo "The current date is `date` at `hostname`." >>$LogFile
echo "The current lag log file is $LogFile." >>$LogFile
echo "The current replication lag is $Lag." >>$LogFile
echo "The replication lag is larger than max lag $MaxLag." >>$LogFile
if [ -z "$ptStatus" ] ; then
echo "Start a monitor daemon with below command: " >>$LogFile
echo "pt-heartbeat --user=monitor --password=xxx -S /tmp/mysql.sock -D test " >>$LogFile
echo " --master-server-id=11 --monitor --print-master-server-id --daemonize --log=$LagDetail" >>$LogFile
/usr/bin/pt-heartbeat --user=monitor --password=xxx -S /tmp/mysql.sock -D test
--master-server-id=$ServerID --monitor --print-master-server-id --daemonize --log=$LagDetail
echo "More detail please check lag log from $LagDetail." >>$LogFile
cat $LogFile | mutt -s "Found slave lag on `hostname`." $mailadd
fi
fi
if [ -n "$ptStatus" ] ; then
STime=`ps -ef|grep pt-heart|grep daemonize |glinuxjishu/13830.html target=_blank class=infotextkey>awk '{print $5}'`
Pid=`ps -ef|grep pt-heart|grep daemonize |gawk '{print $2}'`
STime=`date '+%Y%m%d'`" "$STime
s_STime=`date -d "$STime" '+%s'`
s_ETime=`date +%s`
DiffSec=`expr $s_ETime - $s_STime`
echo $STime
echo $s_STime
echo $s_ETime
echo $DiffSec
if [ "$DiffSec" -gt 1800 ]; then
echo "kill -9 $Pid"
kill -9 $Pid
fi
fi
# Remove history slave lag log.
find $LogDir -name "*slave_lag*" -ctime +$Rentition -delete
exit
2、部署
加入crontab计划任务中定时执行。