MySQL主从同步状态监控脚本及邮件通知

网络版本

#!/bin/bash

mysql_cmd="mysql -u root -pxxxxxxxxx"

errorno=(1158 1159 1008 1007 1062)

while true

do

array=($($mysql_cmd -e "show slave status\G"|egrep ‘_Running|Behind_Master|Last_SQL_Errno‘|awk ‘{ print $NF }‘))

if [ "${array[0]}"=="Yes" -a "${array[1]}"=="Yes" -a "${array[2]}"=="0" ]

then

echo "MySQL is slave is ok"

else

for ((i=0;i<${#errorno[*]};i++))

do

if [ "${array[3]}"="${errorno[$i]}" ];then

$mysql_cmd -e "stop slave &&set global sql_slave_skip_counter=1;start slave;"

fi

done

char="MySQL slave is error"

echo "$char"

echo "$char"|mail -s "$char" 1234567@qq.com

break

fi

sleep 30

done

?

?

精简原因：

1、root账号权限太大，新建一个query账号，相关赋权：

GRANT REPLICATION CLIENT on *.* to ‘query‘@‘%‘ ;

FLUSH PRIVILEGES

2、因我的db启用了GTID，不适用于set global sql_slave_skip_counter=1命令，故取消该步骤

3、‘_Running’过滤时会剩下Slave_SQL_Running_State，不想要，改为‘_Running’

?

简化后脚本：

#!/bin/bash

mysql_cmd="mysql -uquery -pxxxxxxxxx"

while true

do

array=($($mysql_cmd -e "show slave status\G"|egrep ‘_Running:|Behind_Master|Last_SQL_Errno‘|awk ‘{ print $NF }‘))

if [ "${array[0]}"=="Yes" -a "${array[1]}"=="Yes" -a "${array[2]}"=="0" ]

then

echo "MySQL is slave is ok"

else

char="MySQL slave is error"

echo "$char"

echo "$char"|mail -s "$char" 1234567@qq.com

break

fi

sleep 30

done

?

MySQL主从同步状态监控脚本及邮件通知

标签：set   xxx   root   精简   egrep   glob   简化   ati   stat