概述

包括mysql宕機(jī)報(bào)警，mysql主從io,sql狀態(tài)異常報(bào)警以及主從延遲過長報(bào)警

通過外部STMP發(fā)郵件

# vi /etc/mail.rc

添加以下設(shè)置：

# 發(fā)送郵件郵箱
set from=WENTAO_Wanna@126.com
# 外部stmp服務(wù)器地址
set smtp=smtp.126.com
# 外部smtp服務(wù)器認(rèn)證的用戶名
set smtp-auth-user=WENTAO_Wanna
# 外部smtp服務(wù)器認(rèn)證的用戶密碼,注意是外部服務(wù)器的授權(quán)碼并非郵箱登錄密碼
set smtp-auth-password=123456
# 郵件認(rèn)證的方式
set smtp-auth=login

調(diào)試

# echo mail_content | mail -s "mail_title" WENTAO_Wanna@126.com,WENTAO_Wanna@foxmail.com

shell腳本實(shí)現(xiàn)自動警告郵件提示

1. 創(chuàng)建保存警告文件的路徑：/data/mysql_warning

   # cd /data
   # mkdir mysql_warning
   

2. 創(chuàng)建備份腳本文件:/data/mysql_bak/mysql_monitoring.sh

   # cd /data/mysql_warning
   # touch mysql_monitoring.sh
   # vi mysql_monitoring.sh
   

   輸入以下內(nèi)容：

   #check MySQL Slave's Runnning Status
   #Crontab time 00:10
   MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'`
   MYSQLIP=`ifconfig eth0|grep "inet" | awk ' {print $2}'`
   STATUS=$(/usr/bin/mysql -uroot -pyour-password -S /data/mysql/mysql.sock -e "show slave status\G" | grep -i "running")
   DELAYED=$(/usr/bin/mysql -uroot -pyour-password -S /data/mysql/mysql.sock -e "show slave status\G" | grep -i "Seconds_Behind_Master")
   #echo "$STATUS"
   IO_env=`echo $STATUS | grep IO | awk  ' {print $2}'`
   echo "============================="
   echo "$STATUS"
   echo "$DELAYED"
   SQL_env=`echo $STATUS | grep SQL | awk  ' {print $4}'`
   DELAYED_env=`echo $DELAYED | awk  ' {print $2}'`
   DATA=`date +"%y-%m-%d %H:%M:%S"`
   
   function checkMysqlStatus(){
       if [ "$MYSQLPORT" == "3306" ]
       then
           /usr/bin/mysql -uroot -pyour-password --connect_timeout=5 -e "show databases;" &>/dev/null 2>&1
           if [ $? -ne 0 ]
           then
               echo "Server: $MYSQLIP mysql is down, please try to restart mysql by manual!" > /data/mysql/mysql.err
               mail -s "WARN! server: $MYSQLIP  mysql is down." WENTAO_Wanna@126.com < /data/mysql/mysql.err
           else
               echo "mysql is running..."
           fi
       else
           mail -s "WARN!Server: $MYSQLIP mysql is down." WENTAO_Wanna@126.com
       fi
   }
   echo "================================="
   echo "$IO_env"
   echo "$SQL_env"
   echo "$DELAYED_env"
   echo "================================="
   checkMysqlStatus
   
   # 延時大于60s
   if [ "$DELAYED_env" -ge 60 ]
   then
     echo "MySQL Slave is delayed $DELAYED_env s!"
     echo "####### $DATA #########">> /data/mysql_slave/mysql_slave_status.log
     echo "MySQL Slave is delayed $DELAYED_env s!" >>    /data/mysql_slave/mysql_slave_status.log
     echo "MySQL Slave is delayed $DELAYED_env s!" | mail -s "WARN! $MYSQLIP MySQL Slave is delayed $DELAYED_env s!" WENTAO_Wanna@126.com
   fi
   if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]
   then
     echo "MySQL Slave is running!"
   else
     echo "####### $DATA #########">> /data/mysql_slave/mysql_slave_status.log
     echo "MySQL Slave is not running!" >>    /data/mysql_slave/mysql_slave_status.log
     echo "MySQL Slave is not running!" | mail -s "WARN! $MYSQLIP MySQL Slave is not running." WENTAO_Wanna@126.com
   fi
   

檢測參數(shù)說明：

• Slave_IO_Running：該參數(shù)可作為io_thread的監(jiān)控項(xiàng)，Yes表示io_thread的和主庫連接正常并能實(shí)施復(fù)制工作，No則說明與主庫通訊異常，多數(shù)情況是由主從間網(wǎng)絡(luò)引起的問題；
• Slave_SQL_Running：該參數(shù)代表sql_thread是否正常，具體就是語句是否執(zhí)行通過，常會遇到主鍵重復(fù)或是某個表不存在。
• Seconds_Behind_Master：是通過比較sql_thread執(zhí)行的event的timestamp和io_thread復(fù)制好的event的timestamp(簡寫為ts)進(jìn)行比較，而得到的這么一個差值；
  NULL—表示io_thread或是sql_thread有任何一個發(fā)生故障，也就是該線程的Running狀態(tài)是No，而非Yes。
  0 — 該值為零，是我們極為渴望看到的情況，表示主從復(fù)制良好，可以認(rèn)為lag不存在。
  正值 — 表示主從已經(jīng)出現(xiàn)延時，數(shù)字越大表示從庫落后主庫越多。
  負(fù)值 — 幾乎很少見，我只是聽一些資深的DBA說見過，其實(shí)，這是一個BUG值，該參數(shù)是不支持負(fù)值的，也就是不應(yīng)該出現(xiàn)。

3. 修改文件屬性，使其可執(zhí)行

   # chmod +x /data/mysql_warning/mysql_monitoring.sh 
   

4. 修改/etc/crontab

   # vi /etc/crontab
   

   在下面添加

   10 00 * * * root  /data/mysql_warning/mysql_monitoring.sh 
   

   表示每天早上8點(diǎn)至晚上12點(diǎn)每30分鐘執(zhí)行一次監(jiān)控腳本

5. 重新啟動crond使設(shè)置生效

   # systemctl enable crond.service #設(shè)為開機(jī)啟動
   # systemctl restart crond.service #重新啟動
   

6. 手動執(zhí)行效果

# sh /data/mysql_warning/mysql_monitoring.sh 
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
=============================
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
        Seconds_Behind_Master: 0
=================================
Yes
Yes
0
=================================
mysql is running...
MySQL Slave is running!

郵件效果

郵件效果圖

參考

LINUX下通過外部SMTP發(fā)郵件(直接拋棄sendmail和postfix)
Seconds_Behind_Master分析主從延遲