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

4etfeww

脚本编写思路：
（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=gfsunny@163.com


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