脚本监控mysql主从

脚本监控mysql主从

1. 获取slave的SQL线程和IO线程的状态
2. 判断两个线程的状态来判断同步是否正常
3. 同步异常时触发邮件报警

简单版本 亲测可用

#!/bin/bash
IO=$(mysql -e 'show slave status \G;' | awk '/Slave_IO_Running/{print $2}')
SQL=$(mysql -e 'show slave status \G;' | awk '/Slave_SQL_Running/{print $2}')

DATE_TIME=$(date +%F%T)
R_USER=1113822884@qq.com
LOG_FILE=/tmp/slave_check.log

if [ $IO == 'No' -o $SQL == "No" ];then
    echo "$DATE_TIME $HOSTNAME slave no  ok" >> $LOG_FILE
    sendemail.py $R_USER "mysql trouble!" "$DATE_TIME $HOSTNAME  SLAVE IS NOT WORKONG"
fi
if [ $IO == 'Yes' -o $SQL == "Yes" ];then
    echo "$DATE_TIME $HOSTNAME slave ok" >> $LOG_FILE
    sendemail.py $R_USER "mysql trouble!" "$DATE_TIME $HOSTNAME  SLAVE IS WORKONG"
fi

复杂版本

#!/bin/bash
while true;do
        IO=$(mysql -e 'show slave status \G;' | awk '/Slave_IO_Running/{print $2}')
        SQL=$(mysql -e 'show slave status \G;' | awk '/Slave_SQL_Running/{print $2}')

        DATE_TIME=$(date +%F_%T)
        R_USER=1113822884@qq.com
        LOG_FILE=/tmp/slave_check.log
        echo 'nihao'
        if [ $IO == 'Yes' -a $SQL == "Yes" ];then
          echo "$DATE_TIME $HOSTNAME slave   ok" >> $LOG_FILE
          echo 'ok'
 #          sendemail.py $R_USER "mysql trouble!" "$DATE_TIME $HOSTNAME  SLAVE IS  WORKONG"

        else
          echo "$DATE_TIME $HOSTNAME slave not  ok" >> $LOG_FILE
          sendemail.py $R_USER "mysql trouble!"     "$DATE_TIME $HOSTNAME  SLAVE IS not  WORKONG"
          num=1
           while [ $num -lt 15 ];do
             IO=$(mysql -e 'show slave status \G;' | awk '/Slave_IO_Running/{print $2}')
             SQL=$(mysql -e 'show slave status \G;' | awk '/Slave_SQL_Running/{print $2}')
            if [ $IO == 'Yes' -a $SQL == "Yes" ];then
                echo "$DATE_TIME $HOSTNAME slave   ok" >> $LOG_FILE
                sendemail.py $R_USER "mysql trouble!" "$DATE_TIME $HOSTNAME  SLAVE IS  WORKONG"
                break
             else
                 let num++

            fi
            sleep 60
           done
        fi
       sleep 60
done