mysql做主从监控_编写脚本实现MySQL主从复制状态监控

生产实战案例：监控MySQL主从同步是否异常，如果异常，则发送短信或者邮件给管理员。提示：如果没主从同步环境,可以用下面文本放到文件里读取来模拟：

阶段1：开发一个守护进程脚本每30秒实现检测一次。阶段2：如果同步出现如下错误号(1158,1159,1008,1007,1062)，则跳过错误。阶段3：请使用数组技术实现上述脚本(获取主从判断及错误号部分)

解答：

#!/bin/sh

# No1 define variables

array=(1158 1159 1008 1007 1062)

Date=$(date +%F)

Mysql_cmd="mysql -uroot -poldboy123 -S/data/3307/mysql.sock -e"

IP=$(ifconfig eth2|awk -F "[ :]+""NR==2{print $4}")

Port=3307

# No2 daemon for monitor MySQL replication

while true

do

# Skip error num 1158 1159 1008 1007 1062

Error_num=$(${Mysql_cmd} "show slave status\G"|grep"Last_SQL_Errno"|awk "{print $NF}")

for n in${array[@]}

do

if [$Error_num -eq $n ];then

${Mysql_cmd} "stop slave;set global sql_slave_skip_counter=1;startslave;"

fi

done

# No3 Check MySQL replication status

mysql_status=($($Mysql_cmd "show slave status\G"|egrep"_Running|_Behind_Master"|awk "{print $NF}"))

Flag=0

for n in${mysql_status[@]}

do

if ["$n" != "Yes" -a "$n" != "0" ];then

letFlag=Flag+1

fi

done

# No4 Mail to system manager

if [ $Flag-ne 0 ];then

echo"[ $Date ] There are $Flag problems on mysql slave.IP:$IPPort:$Port">>/tmp/slave.log

tail -1/tmp/slave.log|mail -s "Slave Warning" 1041282946@qq.com

sleep 300

else

echo "Mysqlslave is OK"

fi

# No5 Complete!

sleep 30

done