实时检查MySQL数据库延迟状况复制中断数据延迟

脚本编写思路：

（1）根据show slave statusG;l列Seconds_Behind_Master；

（2）Seconds_Behind_Master= NULL判断io或sql进程哪个停止或者二者都停止

（3）Seconds_Behind_Master= 0复制正常

（4）Seconds_Behind_Master>0 说明主从延迟

上述（2）、（4）会发出邮件报警，正常时忽略，脚本10s采集数据一次。

#!/bin/bash

########################################

# automatic check mysql replacation status from salve database #

# edit by rgf in 2014/09/03                                                  #

# The scropt distributes in slave databases,if slave db faults,      #

# then it will send email to administrator or dba                       #

########################################

export LANG=zh_CN

base_dir=/usr/bin

logs_dir=/opt/mysql/log

db_user=root

db_pwd=123456

EmailFile=$logs_dir/${HOSTNAME}_mail_`date +%Y%m%d`.log

Email=[email protected]

while true

do

echo "       " > $EmailFile

echo "-----------------------" >> $EmailFile

echo "`date +"%y-%m-%d %H:%M:%S"`" >> $EmailFile

echo "-------------------------" >> $EmailFile

Port=`netstat -ntlp|grep mysqld | awk -F ‘ ‘ ‘{print $4}‘ | awk -F ‘:‘ ‘{print $2}‘`

Ip=`ifconfig |grep "inet addr:"|grep -v "inet addr:127.0.0.1" | awk -F ‘ ‘ ‘{print $2}‘ | awk -F ‘:‘ ‘{print $2}‘`

echo "${HOSTNAME}:$Ip:$Port" >> $EmailFile

        Seconds_Behind_Master=`${base_dir}/mysql  -u$db_user -p$db_pwd -e "show slave statusG;" | grep "Seconds_Behind_Master:" | awk -F ‘:‘ ‘{print $2}‘` &> /dev/null

        Slave_IO_Running=`${base_dir}/mysql  -u$db_user -p$db_pwd -e "show slave statusG;" | grep "Slave_IO_Running:" | awk -F ‘:‘ ‘{print $2}‘` &> /dev/null

        Slave_SQL_Running=`${base_dir}/mysql  -u$db_user -p$db_pwd -e "show slave statusG;" | grep "Slave_SQL_Running:" | awk -F ‘:‘ ‘{print $2}‘` &> /dev/null

        if [ $Seconds_Behind_Master = "NULL" ];then

                echo "Disruption of replication" >> $EmailFile

                if [ $Slave_IO_Running = "Yes" -a $Slave_SQL_Running = "No" ];then

                        echo "Slave_IO_Running is OK and Slave_SQL_Running is failure" >> $EmailFile

                        mail -s "Slave_IO OK AND Slave_SQL failured!!" $Email < $EmailFile

                elif [ $Slave_IO_Running = "No" -a $Slave_SQL_Running = "Yes" ];then

                        echo "Slave_IO_Running is failure and Slave_SQL_Running is OK" >> $EmailFile

                        mail -s "Slave_IO failured AND Slave_SQL OK!!" $Email < $EmailFile

                else

                        echo "Slave_IO_Running is failure and Slave_SQL_Running is failure" >> $EmailFile

                        mail -s "Slave_IO failure AND Slave_SQL failure!!" $Email < $EmailFile

                fi

        elif [ $Seconds_Behind_Master -eq 0 ];then

                echo "slave is ok!" &> /dev/null

        elif [ $Seconds_Behind_Master -gt 0 ];then

                echo "slave has beened delayed compared with master" >> $EmailFile

                mail -s "slave has beened delayed" $Email < $EmailFile

        else

                echo "slave Unknown fault!" >> $EmailFile

                mail -s "slave Unknown fault!!" $Email < $EmailFile

        fi

sleep 10

done