MySQL master/slave latency monitoring script (pt-heartbeat)

Source: Internet
Author: User
Tags percona
We can use percona's powerful weapon pt-heartbeat to monitor the master-slave replication latency of MySQL databases. Pt-heartbeat updates a specific table on the master database using timestamps, then read the updated timestamp from the database and compare it with the local system time to monitor the https://www.php1.cn/code/5799.html "target =" _ blank "> MySQL database master/slave replication latency, we can use percona's powerful weapon pt-heartbeat. Pt-heartbeat updates a specific table in the master database by using a timestamp. then, it reads the updated timestamp from the slave database and compares it with the local system time to get its latency. This article uses scripts to regularly check the replication latency between the slave database and the master database and send emails for your reference.

For how to install the pt-heartbeat tool, refer to percona-toolkit installation and introduction.
For more information about the pt-heartbeat tool, see use pt-heartbeat to monitor master-slave replication latency.

1. script overview
A. regular use of the script -- check method for a single check of the current delay (regular use of cron jobs such as every 1 minute or 5 minutes)
B. determine whether the delay is within the controllable range by setting the specified delay threshold.
C. Once the current latency is greater than the specified threshold value, the -- monitor method will be used to continuously monitor its delay and write it into the log file.
D. for the -- monitor mode, the process runs for more than 30 minutes and kill the process from time to avoid excessive logs and insufficient space due to indefinite running.

2. script content

[mysql@SZDB run]$ more ck_slave_lag.sh #!/bin/bash#set -xif [ $# -ne 3 ];then      echo "usage:"      echo "ck_slave_lag.sh"      exit 0;fi# Author : Leshami# Blog   : http://blog.csdn.net/leshamiServerID=$1MaxLag=$2LogDir=$3Timestamp=`date +%Y%m%d_%H%M%S`Rentition=7LogFile=$LogDir/slave_lag_$Timestamp.logLagDetail=$LogDir/slave_lag_Detail_$Timestamp.logmailadd=leshami@12306.cnecho $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 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    fifiif [ -n "$ptStatus" ] ; then    STime=`ps -ef|grep pt-heart|grep daemonize |gawk '{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    fifi# Remove history slave lag log.find $LogDir -name "*slave_lag*" -ctime +$Rentition -delete exit

3. Deployment reference

[mysql@SZDB run]$ crontab -l#check slave lag*/1 * * * * /run/ck_slave_lag.sh 11 3 /log/SlaveLag

The above is the content of the MySQL master/slave latency monitoring script (pt-heartbeat). For more information, see PHP Chinese network (www.php1.cn )!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.