check_mysql_slave.sh

#!/bin/bash
############################################################
# $Name:         check_mysql_slave.sh
# $Version:      v1.0
# $Functions:
# $Author:       Jeson Li
# $organization: www.lzbbg.cn
# $Create Date:  2018-10-10
# $Description:  Check MySQL slave script
# 001 监控MySQL主从同步脚本
# 监控MySQL主从同步是否异常，如果异常，则发送短信或者邮件给管理员。
# 阶段1：开发了一个守护进程脚本每30秒实现检测一次。
# 阶段2：如果同步出现如下错误号（1158,1159,1008,1007,1062），则跳过错误。
#     注释：    1158:网络错误,出现读错误,请检查网络连接状况
#            1159:网络错误,读超时,请检查网络连接状况
#            1007:MYSQL数据库已存在,创建数据库失败
#            1008:MYSQL数据库不存在,删除数据库失败 
# 阶段3：使用了数组技术实现上述脚本（获取主从判断及错误号部分）
################################################################

# Source function library.
. /etc/init.d/functions
 
# Defined variables
MysqlUser=root
MysqlPass=jeson123456
MysqlPort=3302
Mysqlsock=/mysql_data/slave01_${MysqlPort}/mysql.sock
ErrorNo=(1158 1159 1008 1007 1062)
errorlog=/var/log/error_skip.log
PidFile=/var/run/check_mysql_slave.pid
MysqlCmd="/apps/links/mysql/bin/mysql -Uu$MysqlUser -p$MysqlPass -S $Mysqlsock"

# Check PID process exist
if [ -s $PidFile -a $(ps -ef|grep "$(cat ${PidFile})"|wc -l) -gt 1 ];then
    echo  "$0 is running now ; PID=[$(cat $PidFile)] and exit"
    exit 1
else
    echo "$$" >$PidFile
fi

# Check mysql server is started?
[ -S $Mysqlsock ] ||{
    echo "Maybe MySQL are stoped"
    exit 1
}
 
# Defined skip error Functions
function error_skip(){
    local flag
    flag=0
    for num in ${ErrorNo[@]}
      do
        if [ "$1" == "$num" ];then
           $MysqlCmd -e'stop slave;set global sql_slave_skip_counter=1;start slave;'
           echo "$(date +%F_%R) $1">>$errorlog
        else
           echo "$(date +%F_%R) $1">>$errorlog
           ((flag++))
        fi
    done
    [ "$flag" == "${#ErrorNo[@]}" ] &&{
        action "MySQL Slave" /bin/false
        uniq $errorlog|mail -s "MySQLSlave is error" 309769163@qq.com
    }
}
 
# Defined check slave Functions
function check_slave(){
  MyResult=$($MysqlCmd -e 'show slave status\G'|egrep '_Running|Behind_Master|SQL_Errno'|awk '{print $NF}')
    array=($MyResult)
    if [ "${array[0]}" == "Yes" -a "${array[1]}" == "Yes" -a "${array[2]}" == "0" ];then
        action "MySQL Slave" /bin/true
    else
        error_skip ${array[3]}
    fi
}
 
# Defined main Functions
function main(){
    while :
       do
         check_slave
         sleep 30
    done
}
main