mysql復制延遲監控腳本
#!/bin/sh
#[email protected]
#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的用戶名
mysql_pass='123' #mysql的登錄用戶密碼
SQLresponse=`mysql -h${slavehost} -u${mysql_user} -p${mysql_pass} mysql -e "show slave status \G" |grep '\<Slave_SQL_Running\>'|awk '{print $2}'`
IOresponse=`mysql -h${slavehost} -u${mysql_user} -p${mysql_pass} mysql -e "show slave status \G" |grep '\<Slave_IO_Running\>'|awk '{print $2}'`
Behind=`mysql -h${slavehost} -u${mysql_user} -p${mysql_pass} mysql -e "show slave status \G" |grep '\<Seconds_Behind_Master\>'|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 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 "/\<Relay_Master_Log_File\>/p" ${tmpfile_02} |sed 's/^[ ]*//g' |awk -F ":" '{print $2}'|awk -F "." '{print $2}'`
bhtime=`sed -n "/\<Seconds_Behind_Master\>/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 "/\<Read_Master_Log_Pos\>/p" ${tmpfile_02} |sed 's/^[ ]*//g' |awk -F ":" '{print $2}'`
k=`sed -n "/\<Exec_Master_Log_Pos\>/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 "/\<Exec_Master_Log_Pos\>/p" ${tmpfile_02} |sed 's/^[ ]*//g' |awk -F ":" '{print $2}'`
y=`sed -n "/\<Relay_Master_Log_File\>/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}
