监控MySQL主从同步是否异常并报警企业案例模拟

rwrr322

企业面试题1：（生产实战案例）：监控MySQL主从同步是否异常，如果异常，则发送短信或者邮件给管理员。提示：如果没主从同步环境,可以用下面文本放到文件里读取来模拟：
阶段1：开发一个守护进程脚本每30秒实现检测一次。
阶段2：如果同步出现如下错误号（1158,1159,1008,1007,1062），则跳过错误。
阶段3：请使用数组技术实现上述脚本（获取主从判断及错误号部分）



解答参考1：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

#!/bin/sh #2015-05-17 # Source function library. . /etc/init.d/functions # Defined variables MysqlUser=root MysqlPass=oldboy123 MysqlPort=3307 Mysqlsock=/data/$MysqlPort/mysql.sock ErrorNo=(1158 1159 1008 1007 1062) errorlog=/tmp/error_skip.log MysqlCmd="/application/mysql/bin/mysql -u$MysqlUser -p$MysqlPass -S $Mysqlsock" # Judge mysql server is ok? [ -S $Mysqlsock ] ||{     echo "Maybe MySQL have someting wrong"     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 "MySQL Slave is error" 12345678@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||error_skip ${array[3]}     fi } # Defined main Functions function main(){     while true        do          check_slave          sleep 60     done } main

解答参考2：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

#!/bin/sh #oldboy linux #2015-05-17 #说明：本脚本来自老男孩教育21期学员李新宇！ USER=root PASSWORD=123456 PORT=3307 error=(1158 1159 1008 1007 1062) MYSQLCMD="mysql -u$USER -p$PASSWORD -S /data/$PORT/mysql.sock" is_run(){   [ `lsof -i:$PORT|wc -l` -lt 2 ]&&{     echo "mysql server is stopped"     exit 1   } } status_array(){ status=($($MYSQLCMD -e "show slave status\G"|egrep "_Running|Last_Errno|Behind_Master"|awk '{print $NF}')) } status_error(){ for((i=0;i<${#error };i++)) do   if [ "$1" == "${error[$i]}" ]     then       $MYSQLCMD -e "stop slave;set global sql_slave_skip_counter=1;start slave;"   else       echo "MySQL slave is failed, errorno is $1"   fi done } judge_slave(){   status_array   if [ "${status[0]}" == "Yes" -a "${status[1]}" == "Yes" -a "${status[3]}" = "0" ]   then     echo "MySQL slave is ok"   else     status_error ${status[2]}   fi } main(){ while true do   is_run   judge_slave   sleep 60 done } main