shell脚本监控MySQL主从同步

企业面试题1:监控MySQL主从同步是否异常,如果异常,则发送短信或者邮件给管理员。
  阶段1:开发一个守护进程脚本每30秒实现检测一次。
  阶段2:如果同步出现如下错误号(1158,1159,1008,1007,1062)发送邮件,否则跳过错误。
  阶段3:请使用数组技术实现上述脚本(获取主从判断及错误号部分)

提示:如果没主从同步环境,可以用下面文本放到文件里读取来模拟:

复制代码
#!/bin/sh
. /etc/init.d/functions
# Defined variables
MysqlUser=root
MysqlPass=root 
MysqlPort=3306
Mysqlsock=/var/mysql/mysql.sock        /*在/etc/my.cnf中找到*/
ErrorNo=(1158 1159 1008 10071062)
errorlog=/tmp/error_skip.log
MysqlCmd="mysql-u$MysqlUser -p$MysqlPass -S $Mysqlsock"
# Judge mysql server is ok?
[ -S $Mysqlsock ] ||{                 /*-S文件是否未0*/
    echo "Maybe MySQL have sometingwrong"
    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 globalsql_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" 12345678@qq.com
    }
}
# Defined check slave Functions
function check_slave(){
    MyResult=`$MysqlCmd -e'show slavestatus\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 true
       do
         check_slave
         sleep 60
    done
}
main
复制代码

 

文件内容:

复制代码
*************************** 1.row ***************************
               Slave_IO_State:Waiting formaster to send event
                  Master_Host:10.0.0.179   #当前的mysql master服务器主机
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000013
          Read_Master_Log_Pos: 502547
               Relay_Log_File:relay-bin.000013
                Relay_Log_Pos:251
        Relay_Master_Log_File:mysql-bin.000013
             Slave_IO_Running:Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                      Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 502547
              Relay_Log_Space:502986
              Until_Condition:None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0     #和主库比同步延迟的秒数,这个参数很重要
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
复制代码

 

posted @   sunziying  阅读(407)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示