MySQL Master-Slave delay Monitoring script (pt-heartbeat)

For MySQL database master-slave replication delay monitoring, we can rely on Percona's powerful weapon pt-heartbeat to achieve. Pt-heartbeat updates a specific table on the main library by using timestamp, and then reads the updated timestamp from the library and then compares it with the local system time to get its delay. This article is mainly through the script regularly check the delay from the library and the main library replication and send mail for your reference.

For the installation of the Pt-heartbeat tool, refer to: Percona-toolkit Installation and introduction
For an introduction to the Pt-heartbeat tool, refer to: Monitor master-slave replication latency with Pt-heartbeat

1. Script Overview
A, the script periodically uses the--check method to check the current delay one time (regular way can use cron job such as every 1 minutes or 5 minutes)
b, by setting the specified delay threshold value to determine whether the delay is in the controllable range
C, once the current delay is greater than the specified threshold, immediately use the--monitor method to continuously monitor its delay and write to the log file
D, for--monitor mode, its process runs for more than 30 minutes, since kill its process, to avoid running indefinitely resulting in log too large, space is not enough

2. Script Content

[[email protected] run]$ more ck_slave_lag.sh #!/bin/bash#set-xif [$#-ne 3];then echo "Usage:" echo "CK _slave_lag.sh <Servier-id> <MaxLag> <LogDir> "Exit 0;fi# author:leshami# BLOG:HTTP://BLOG.CSD n.net/leshamiserverid=$1maxlag=$2logdir=$3timestamp= ' Date +%y%m%d_%h%m%s ' rentition=7logfile= $LogDir/slave_lag_$ Timestamp.loglagdetail= $LogDir/slave_lag_detail_$timestamp.log[email protected]echo $ServerIDecho $ Maxlagecho $LogDirecho $LogFileecho $LagDetailecho $mailaddif [!-D $LogDir];then mkdir-p $LogDirfiLag = '/usr/bin/pt- Heartbeat--user=monitor--password=xxx-s/tmp/mysql.sock-d test--master-server-id= $ServerID--check ' lag= ' echo ${Lag %.*} ' #Lag =3echo $LagptStatus = ' ps-ef|grep pt-heart|grep daemonize ' echo $ptStatusif [$Lag-gt $MaxLag];          Then echo, "The current date was ' date ' at ' hostname '."              >> $LogFile echo "The current lag log file is $LogFile." >> $LogFile echo "The current replicationLag 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=m Onitor--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= $Lag Detail echo "More Detail-check lag log from $LagDetail." >> $LogFile cat $LogFile | Mutt-s "Found slave lag on ' hostname '." $mailadd Fifiif [-N "$ptStatus"]; Then Stime= ' ps-ef|grep pt-heart|grep daemonize |gawk ' {print $} ' pid= ' ps-ef|grep pt-heart|grep daemonize |gawk ' {print $} ' 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 fifi# Remove slave lag log.find $LogDir-name "*slave_lag*"- CTime + $Rentition-delete exit

3. Deployment Reference

[Email protected] run]$ Crontab-l#check Slave LAG*/1 * * * */run/ck_slave_lag.sh one 3/log/slavelag

MySQL Master-Slave delay Monitoring script (pt-heartbeat)