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

最新推荐文章于 2023-09-25 23:59:50 发布

原创最新推荐文章于 2023-09-25 23:59:50 发布 · 132 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql复制延迟监控

本文提供了一个用于检查MySQL主从复制延迟状况的Shell脚本。该脚本通过比较主服务器上的二进制日志与从服务器上的复制状态来评估复制延迟，并使用颜色编码输出以清晰展示同步状态。

#!/bin/sh

#ocpyang@126.com

#repdelay.sh

#查看复制延迟详细多少event

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

export black='\033[0m'

export boldblack='\033[1;0m'

export red='\033[31m'

export boldred='\033[1;31m'

export green='\033[32m'

export boldgreen='\033[1;32m'

export yellow='\033[33m'

export boldyellow='\033[1;33m'

export blue='\033[34m'

export boldblue='\033[1;34m'

export magenta='\033[35m'

export boldmagenta='\033[1;35m'

export cyan='\033[36m'

export boldcyan='\033[1;36m'

export white='\033[37m'

export boldwhite='\033[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 "%s\n" "$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的username

mysql_pass='123' #mysql的登录用户password

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="mysqlserver($slavehost)的复制已经停止工作:Slave_SQL_Running: No;无法获取复制延迟情况."

cechon "${error}" red

echo " "

exit 0

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

echo " "

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

cechon "${error}" red

echo " "

exit 0

#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 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}'`

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 "*****************************************************************************"

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"

rm -rf ${tmpfile_01}

rm -rf ${tmpfile_02}