mysql延迟监控_实战:mysql 5.6复制延迟监控

#repdelay.sh

#!/bin/sh

[email protected]

#查看复制延迟具体多少event

#set mysql evn

MYSQL_USER_MASTER=root

MYSQL_PASS_MASTER='password'

MYSQL_HOST_MASTER=192.168.2.188

MYSQL_USER_SLAVE=root

MYSQL_PASS_SLAVE='password'

MYSQL_HOST_SLAVE=192.168.2.14

tmpfile_01="tmp01.`date +%Y%m%d%H%M%S`.txt"

tmpfile_02="tmp02.`date +%Y%m%d%H%M%S`.txt"

mysql -h${MYSQL_HOST_MASTER} -u${MYSQL_USER_MASTER} -p${MYSQL_PASS_MASTER} -e"SHOW BINARY LOGS;" >${tmpfile_01}

mysql -h${MYSQL_HOST_SLAVE} -u${MYSQL_USER_SLAVE} -p${MYSQL_PASS_SLAVE} -e"SHOW SLAVE STATUS\G;" >${tmpfile_02}

#tail -1 ${tmpfile_01} | grep -v "Log_name"

#cat ${tmpfile_02} | grep -E 'Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos' | grep -v "Relay_Master_Log_File" |sed 's/^[ ]*//g'

a=`tail -1 ${tmpfile_01} | grep -v "Log_name" |awk '{print $1}'|awk -F "." '{print $2}'`

b=`sed -n "/\/p" ${tmpfile_02} |sed 's/^[ ]*//g' |awk -F ":" '{print $2}'|awk -F "." '{print $2}'`

bhtime=`sed -n "/\/p" ${tmpfile_02} |sed 's/^[ ]*//g' |awk -F ":" '{print $2}'`

if [ "$b" = "$a" ];then

c=`tail -1 ${tmpfile_01} | grep -v "Log_name" |awk '{print $2}'`

d=`sed -n "/\/p" ${tmpfile_02} |sed 's/^[ ]*//g' |awk -F ":" '{print $2}'`

e=`expr $c - $d`

if [ "${e}" -eq 0 -a "${bhtime}" -eq 0 ]; then

echo "*****************************************************************************"

echo -e "\e[1;31m &&&&&&&Synchronization has been completed!&&&&&&& \e[0m"

echo "*****************************************************************************"

elif [ "${e}" -eq 0 -o "${bhtime}" -eq 0 ];then

echo "*****************************************************************************"

echo -e "\e[1;31m Has been synchronized to the same log file! Wait a moment \e[0m"

echo -e "\e[1;31m Not synchronized binlog events is:${e},behind master tims is ${bhtime} \e[0m"

echo "*****************************************************************************"

fi

elif [ ${b} -lt ${a} ];then

f=`sed -n "/\/p" ${tmpfile_02} |sed 's/^[ ]*//g' |awk -F ":" '{print $2}'`

g=`awk '$1 >= "$b" {print $2}' ${tmpfile_01} |awk 'BEGIN{total=0}{total+=$1}END{print total}'`

re=`expr $g - $f`

echo -e "\e[1;31m There are multiple log files are not synchronized,the events is:${re} \e[0m"

fi

rm -rf ${tmpfile_01}

rm -rf ${tmpfile_02}

原文：http://blog.csdn.net/yangzhawen/article/details/39207857