首页 > 代码库 > 实时检查MySQL数据库延迟状况复制中断数据延迟
实时检查MySQL数据库延迟状况复制中断数据延迟
脚本编写思路:
(1)根据show slave statusG;l列Seconds_Behind_Master;
(2)Seconds_Behind_Master= NULL判断io或sql进程哪个停止或者二者都停止
(3)Seconds_Behind_Master= 0复制正常
(4)Seconds_Behind_Master>0 说明主从延迟
上述(2)、(4)会发出邮件报警,正常时忽略,脚本10s采集数据一次。
#!/bin/bash
########################################
# automatic check mysql replacation status from salve database #
# edit by rgf in 2014/09/03 #
# The scropt distributes in slave databases,if slave db faults, #
# then it will send email to administrator or dba #
########################################
export LANG=zh_CN
base_dir=/usr/bin
logs_dir=/opt/mysql/log
db_user=root
db_pwd=123456
EmailFile=$logs_dir/${HOSTNAME}_mail_`date +%Y%m%d`.log
Email=gfsunny@163.com
while true
do
echo " " > $EmailFile
echo "-----------------------" >> $EmailFile
echo "`date +"%y-%m-%d %H:%M:%S"`" >> $EmailFile
echo "-------------------------" >> $EmailFile
Port=`netstat -ntlp|grep mysqld | awk -F ‘ ‘ ‘{print $4}‘ | awk -F ‘:‘ ‘{print $2}‘`
Ip=`ifconfig |grep "inet addr:"|grep -v "inet addr:127.0.0.1" | awk -F ‘ ‘ ‘{print $2}‘ | awk -F ‘:‘ ‘{print $2}‘`
echo "${HOSTNAME}:$Ip:$Port" >> $EmailFile
Seconds_Behind_Master=`${base_dir}/mysql -u$db_user -p$db_pwd -e "show slave statusG;" | grep "Seconds_Behind_Master:" | awk -F ‘:‘ ‘{print $2}‘` &> /dev/null
Slave_IO_Running=`${base_dir}/mysql -u$db_user -p$db_pwd -e "show slave statusG;" | grep "Slave_IO_Running:" | awk -F ‘:‘ ‘{print $2}‘` &> /dev/null
Slave_SQL_Running=`${base_dir}/mysql -u$db_user -p$db_pwd -e "show slave statusG;" | grep "Slave_SQL_Running:" | awk -F ‘:‘ ‘{print $2}‘` &> /dev/null
if [ $Seconds_Behind_Master = "NULL" ];then
echo "Disruption of replication" >> $EmailFile
if [ $Slave_IO_Running = "Yes" -a $Slave_SQL_Running = "No" ];then
echo "Slave_IO_Running is OK and Slave_SQL_Running is failure" >> $EmailFile
mail -s "Slave_IO OK AND Slave_SQL failured!!" $Email < $EmailFile
elif [ $Slave_IO_Running = "No" -a $Slave_SQL_Running = "Yes" ];then
echo "Slave_IO_Running is failure and Slave_SQL_Running is OK" >> $EmailFile
mail -s "Slave_IO failured AND Slave_SQL OK!!" $Email < $EmailFile
else
echo "Slave_IO_Running is failure and Slave_SQL_Running is failure" >> $EmailFile
mail -s "Slave_IO failure AND Slave_SQL failure!!" $Email < $EmailFile
fi
elif [ $Seconds_Behind_Master -eq 0 ];then
echo "slave is ok!" &> /dev/null
elif [ $Seconds_Behind_Master -gt 0 ];then
echo "slave has beened delayed compared with master" >> $EmailFile
mail -s "slave has beened delayed" $Email < $EmailFile
else
echo "slave Unknown fault!" >> $EmailFile
mail -s "slave Unknown fault!!" $Email < $EmailFile
fi
sleep 10
done
实时检查MySQL数据库延迟状况复制中断数据延迟