??? 對于mysql數據庫主從復制延遲的監控，我們可以借助percona的有力武器pt-heartbeat來實現。pt-heartbeat通過使用時間戳方式在主庫上更新特定表，然后在從庫上讀取被更新的時間戳然后與本地系統時間對比來得出其延遲。本文主要是通過腳本來定期檢查從庫與主庫復制的延遲度并發送郵件，供大家參考。
??? 有關pt-heartbeat工具的安裝可以參考：percona-toolkit的安裝及簡介
??? 有關pt-heartbeat工具的介紹可以參考：使用pt-heartbeat監控主從復制延遲
?
1、腳本概述
?? a、腳本定期使用–check方式單次檢查當前的延遲性(定期的方式可以使用cron job比如每1分鐘或5分鐘)
?? b、通過設定指定的延遲閥值來判斷當時的延遲性是否在可控范圍
?? c、一旦當前的延遲大于指定閥值，則馬上使用–monitor方式不停的監控其延遲性并寫入到日志文件
?? d、對于–monitor方式，其進程運行超過30分鐘，自kill其進程，以避免無限期運行導致日志過大，空間不夠用
?
2、腳本內容?
[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 #?Author?:?Leshami #?Blog???:?http://www.php.cn/ 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?|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 ????fi fi #?Remove?history?slave?lag?log. find?$LogDir?-name?"*slave_lag*"?-ctime?+$Rentition?-delete? exit</logdir></maxlag></servier-id>
3、部署參考
[mysql@SZDB?run]$?crontab?-l #check?slave?lag */1?*?*?*?*?/run/ck_slave_lag.sh?11?3?/log/SlaveLag
?以上就是MySQL 主從延遲監控腳本(pt-heartbeat)的內容，更多相關內容請關注PHP中文網（www.php.cn）！
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
