MySQL主从服务器的守护进程监视

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_30621919

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量117

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 awk

原文链接：http://www.cnblogs.com/it-hack/p/7518539.html

本文介绍了一个用于检查MySQL从库状态并自动处理特定错误的shell脚本。该脚本每隔30秒运行一次，当检测到预设错误号时会自动跳过或重启从库，确保复制过程正常进行。

检测myslq从库状态，跳过固定的错误号，每隔30秒检测一次，如果符合条件自动跳过或者是重启从库

1）取出mysql从库的关键字

1 [root@localhost scripts]# mysql -u root -p123qq.com3307 -S /data/3307/mysql.sock -e "show slave status\G"|grep -E "Running|Seconds_Behind_Master|Last_SQL_Errno"|awk '{print $NF}'
2 Yes
3 Yes
4 0
5 0

2）把错误号定义在数组里面

3）while ture

根据思路调试出如下脚本

 1 [root@localhost scripts]# cat check_mysql_slave.sh 
 2 #/bin/bash
 3 #Date:    
 4 #Author:   
 5 #Mail:     917667797@qq.com
 6 #Function: This scripts  function is check mysql slave is ok
 7 #Version:  1.1
 8 qq="917667796@qq.com"
 9 cmd="mysql -u root -p123qq.com3307 -S /data/3307/mysql.sock -e"
10 ip=`ifconfig eth2|sed -n 's#^.*ddr:\(.*\) Bc.*$#\1#gp'`
11 skip=`$cmd "stop slave;set global sql_slave_skip_counter=1;start slave;"`
12 error_numb=(1158 1159 1008 1007 1062)
13 while true
14 do
15 status=(`$cmd "show slave status\G"|grep -E "Running|Seconds_Behind_Master|Last_SQL_Errno"|awk '{print $NF}'`)
16  
17    if [ "${status[0]}"  == "Yes" -a "${status[1]}" == "Yes" -a "${status[2]}" == "0" ]
18        then
19            echo  "mysql slave is ok"
20        else
21            for ((n=0;n<${#error_numb[*]};n++))
22            do
23               if [ "${status[3]}" == "${error_numb[n]}" ];then
24               ${skip}
25               else
26                $cmd "stop slave;start slave;"
27               fi
28            done
29            echo  "mysql salve is not ok"
30            echo "${ip} mysql is not ok"|mail -s "mysql slave error at `date +%F%T`" $qq
31    fi
32 sleep 3
33 done