20必会shell题：监控MySQL主从同步是否异常，

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

答案一：
 [root@backup_server scripts]# cat mysql_rep_check.sh
 #!/bin/bash
 mysqluser=root
 mysqlpasswd=oldboy123
 myport=3306
 mysqlcmd="mysql -u$mysqluser -p$mysqlpasswd -S /data/$myport/mysql.sock "

a=`netstat -lntup|grep $myport|wc -l`
 if [ $a -ne 1 ];then
 echo "slave mysql server is not running!!!!"
 echo "slave mysql server is not running!!!"|mail -s "warning!!!" clc@yarnway.com
 exit 2
 fi

main (){
 while true
 do
 array=($($mysqlcmd -e 'show slave status\G'|egrep "Running|Seconds|Last_SQL_Errno"|awk '{print $2}'))

b=($($mysqlcmd -e 'show slave status\G'|egrep "Running|Seconds|Last_SQL_Errno"|awk '{print $1}'))

if [ ${array[1]} == "No" ]
 then
 for n in 1 2 3
 do

b=($($mysqlcmd -e 'show slave status\G'|egrep "Running|Seconds|Last_SQL_Errno"|awk '{print $1}'))

if [ ${array[3]} -eq 1158 ] ;then
 $mysqlcmd -e 'stop slave;' &>/dev/null
 $mysqlcmd -e 'set global sql_slave_skip_counter=1;' &>/dev/null
 $mysqlcmd -e 'start slave;' &>/dev/null
 e=${array[3]}
 fi

if [ ${array[3]} -eq 1159 ] ;then
 $mysqlcmd -e 'stop slave;' &>/dev/null
 $mysqlcmd -e 'set global sql_slave_skip_counter=1;' &>/dev/null
 $mysqlcmd -e 'start slave;' &>/dev/null
 e=${array[3]}
 fi

if [ ${array[3]} -eq 1008 ] ;then
 $mysqlcmd -e 'stop slave;' &>/dev/null
 $mysqlcmd -e 'set global sql_slave_skip_counter=1;' &>/dev/null
 $mysqlcmd -e 'start slave;' &>/dev/null
 e=${array[3]}
 fi

if [ ${array[3]} -eq 1007 ] ;then
 $mysqlcmd -e 'stop slave;' &>/dev/null
 $mysqlcmd -e 'set global sql_slave_skip_counter=1;' &>/dev/null
 $mysqlcmd -e 'start slave;' &>/dev/null
 e=${array[3]}
 fi

if [ ${array[3]} -eq 1062 ] ;then
 $mysqlcmd -e 'stop slave;' &>/dev/null
 $mysqlcmd -e 'set global sql_slave_skip_counter=1;' &>/dev/null
 $mysqlcmd -e 'start slave;' &>/dev/null
 fi

done
 fi

if [ ${array[0]} == "No" -o ${array[1]} == "No" ]
 then
 echo -e "mysql rep have errors,the last error code is $e \n ${b[0]}${array[0]}\n${b[1]}${array[1]}\n${b[2]}${array[2]}\n${b[3]}${array[3]}" |mail -s "warning!!!" clc@yarnway.com
 fi

if [ ${array[0]} == "Yes" -a ${array[1]} == "Yes" ]&& [ ${array[2]} -gt 3 ]
 then
 echo -e "mysql rep have errors,the last error code is $e ,it is olny delay!!!\n ${b[0]}${array[0]}\n${b[1]}${array[1]}\n${b[2]}${array[2]}\n${b[3]}${array[3]}" |mail -s "delay!!!" clc@yarnway.com
 fi

sleep 30
 done
 }
 main

答案二：（对一作了优化）

[root@backup_server scripts]# cat mysql_rep_check.sh
 #!/bin/bash
 mysqluser=root
 mysqlpasswd=oldboy123
 myport=3306
 mysqlcmd="mysql -u$mysqluser -p$mysqlpasswd -S /data/$myport/mysql.sock "

a=`netstat -lntup|grep $myport|wc -l`
 if [ $a -ne 1 ];then
 echo "slave mysql server is not running!!!!"
 echo "slave mysql server is not running!!!"|mail -s "warning!!!" clc@yarnway.com
 exit 2
 fi

main (){
 while true
 do
 array=($($mysqlcmd -e 'show slave status\G'|egrep "Running|Seconds|Last_SQL_Errno"|awk '{print $2}'))

b=($($mysqlcmd -e 'show slave status\G'|egrep "Running|Seconds|Last_SQL_Errno"|awk '{print $1}'))

if [ ${array[1]} == "No" ]
 then
 #第一个for是保证有时候错误可能有两个，一个跳不过去。
 for n in 1 2
 do

for m in 1158 1159 1007 1008 1062
 do

if [ ${array[3]} -eq $m ] ;then
 $mysqlcmd -e 'stop slave;' &>/dev/null
 $mysqlcmd -e 'set global sql_slave_skip_counter=1;' &>/dev/null
 $mysqlcmd -e 'start slave;' &>/dev/null
 e=${array[3]}
 fi
 done
 done
 fi

if [ ${array[0]} == "No" -o ${array[1]} == "No" ]
 then
 echo -e "mysql rep have errors,the last error code is $e \n ${b[0]}${array[0]}\n${b[1]}${array[1]}\n${b[2]}${array[2]}\n${b[3]}${array[3]}" |mail -s "warning!!!" clc@yarnway.com
 fi

if [ ${array[0]} == "Yes" -a ${array[1]} == "Yes" ]&& [ ${array[2]} -gt 3 ]
 then
 echo -e "mysql rep have errors,the last error code is $e ,it is olny delay!!!\n ${b[0]}${array[0]}\n${b[1]}${array[1]}\n${b[2]}${array[2]}\n${b[3]}${array[3]}" |mail -s "delay!!!" clc@yarnway.com
 fi

sleep 30
 done
 }
 main

以下是nagios被动模式插件可以用的：
 [root@backup_server scripts]# cat mysql_rep_check_nagios.sh
 #!/bin/bash
 mysqluser=root
 mysqlpasswd=oldboy123
 mysqlcmd="mysql -u$mysqluser -p$mysqlpasswd -S /data/3306/mysql.sock"
 $mysqlcmd -e 'show slave status\G' 1>/dev/null 2>&1
 a=$?
 if [ $a -ne 0 ];then
 echo "slave mysql server is not running!!!!"
 exit 2
 fi
 array=($($mysqlcmd -e 'show slave status\G'|egrep "Seconds|Running"|awk '{print $2}'))

if [ ${array[0]} == "Yes" -a ${array[1]} == "Yes" ] && [ ${array[2]} -le 3 ];then
 echo -e "Slave_IO_Running: ${array[0]}\nSlave_SQL_Running: ${array[1]}\nSeconds_Behind_Master: ${array[2]}"
 exit 0
 elif [ ${array[0]} != "Yes" -o ${array[1]} != "Yes" ];then
 echo -e "Slave_IO_Running: ${array[0]}\nSlave_SQL_Running: ${array[1]}\nSeconds_Behind_Master: ${array[2]}"
 exit 2

elif [ ${array[0]} =="Yes" -a ${array[1]} =="Yes" ] && [ ${array[2]} -gt 3 ];then
 echo -e "Slave_IO_Running: ${array[0]}\nSlave_SQL_Running: ${array[1]}\nSeconds_Behind_Master: ${array[2]}"
 exit 1
 fi

原文链接：20必会shell题：监控MySQL主从同步是否异常，，转载请注明来源！