mysql 复制延迟诊断_mysql复制延迟监控脚本

#!/bin/sh

#ocpyang@126.com

#repdelay.sh

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

#####1.juede the rep slave status

export black=' 33[0m'

export boldblack=' 33[1;0m'

export red=' 33[31m'

export boldred=' 33[1;31m'

export green=' 33[32m'

export boldgreen=' 33[1;32m'

export yellow=' 33[33m'

export boldyellow=' 33[1;33m'

export blue=' 33[34m'

export boldblue=' 33[1;34m'

export magenta=' 33[35m'

export boldmagenta=' 33[1;35m'

export cyan=' 33[36m'

export boldcyan=' 33[1;36m'

export white=' 33[37m'

export boldwhite=' 33[1;37m'

cecho ()

## -- Function to easliy print colored text -- ##

# Color-echo.

# 参数 $1 = message

# 参数 $2 = color

{

local default_msg="No message passed."

message=${1:-$default_msg}# 如果$1没有输入则为默认值default_msg.

color=${2:-black}# 如果$1没有输入则为默认值black.

case $color in

black)

printf "$black" ;;

boldblack)

printf "$boldblack" ;;

red)

printf "$red" ;;

boldred)

printf "$boldred" ;;

green)

printf "$green" ;;

boldgreen)

printf "$boldgreen" ;;

yellow)

printf "$yellow" ;;

boldyellow)

printf "$boldyellow" ;;

blue)

printf "$blue" ;;

boldblue)

printf "$boldblue" ;;

magenta)

printf "$magenta" ;;

boldmagenta)

printf "$boldmagenta" ;;

cyan)

printf "$cyan" ;;

boldcyan)

printf "$boldcyan" ;;

white)

printf "$white" ;;

boldwhite)

printf "$boldwhite" ;;

esac

printf "%sn" "$message"

tput sgr0# tput sgr0即恢复默认值

printf "$black"

return

}

cechon ()

# Color-echo.

# 参数1 $1 = message

# 参数2 $2 = color

{

local default_msg="No message passed."

# Doesn't really need to be a local variable.

message=${1:-$default_msg}# 如果$1没有输入则为默认值default_msg.

color=${2:-black}# 如果$1没有输入则为默认值black.

case $color in

black)

printf "$black" ;;

boldblack)

printf "$boldblack" ;;

red)

printf "$red" ;;

boldred)

printf "$boldred" ;;

green)

printf "$green" ;;

boldgreen)

printf "$boldgreen" ;;

yellow)

printf "$yellow" ;;

boldyellow)

printf "$boldyellow" ;;

blue)

printf "$blue" ;;

boldblue)

printf "$boldblue" ;;

magenta)

printf "$magenta" ;;

boldmagenta)

printf "$boldmagenta" ;;

cyan)

printf "$cyan" ;;

boldcyan)

printf "$boldcyan" ;;

white)

printf "$white" ;;

boldwhite)

printf "$boldwhite" ;;

esac

printf "%s" "$message"

tput sgr0# tput sgr0即恢复默认值

printf "$black"

return

}

####2.jude the rep delay status

#configure rep master and slave env

masterhost="192.168.99.137"

slavehost="192.168.1.220"

mysql_user=root #mysql的用户名

mysql_pass='123' #mysql的登录用户密码

SQLresponse=`mysql -h${slavehost} -u${mysql_user} -p${mysql_pass} mysql -e "show slave status G" |grep ''|awk '{print $2}'`

IOresponse=`mysql -h${slavehost} -u${mysql_user} -p${mysql_pass} mysql -e "show slave status G" |grep ''|awk '{print $2}'`

Behind=`mysql -h${slavehost} -u${mysql_user} -p${mysql_pass} mysql -e "show slave status G" |grep ''|awk '{print $2}'`

if [ "$SQLresponse" = "No" ]; then

echo " "

error="mysql服务器($slavehost)的复制已经停止工作:Slave_SQL_Running: No;无法获取复制延迟情况."

cechon "${error}" red

echo " "

exit 0

fi

if [ "$IOresponse" = "No" ]; then

echo " "

error="mysql服务器($slavehost)的复制已经停止工作:Slave_IO_Running: No;无法获取复制延迟情况."

cechon "${error}" red

echo " "

exit 0

fi

#set mysql evn

MYSQL_USER_MASTER=root

MYSQL_PASS_MASTER='123'

MYSQL_HOST_MASTER=192.168.99.137

MYSQL_USER_SLAVE=root

MYSQL_PASS_SLAVE='123'

MYSQL_HOST_SLAVE=192.168.1.220

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 STATUSG;" >${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}'`

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

e=`expr $c - $d`

dfevt=`expr $d - $k`

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

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

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

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

elif [ "${e}" -eq 0 -o "${bhtime}" -gt 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:${dfevt},behind master tims is ${bhtime} e[0m"

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

fi

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

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

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

#u=`awk '{if($1~/'''''$y'''''/ ) print NR}' ${tmpfile_01}`

u=`grep -n "$y" ${tmpfile_01}|awk -F: '{print $1'}`

x=`awk 'NR>='''$u''' { print $0}' ${tmpfile_01}|awk 'BEGIN{total=0}{total+=$2}END{print total}' `

re=`expr $x - $exbin`

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}