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 envmasterhost="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 0fiif [ "$IOresponse" = "No" ]; then	echo "                                                                           "	error="mysql服务器($slavehost)的复制已经停止工作:Slave_IO_Running: No;无法获取复制延迟情况."	cechon  "${error}" red	echo "                                                                           "	exit 0	fi#set mysql evnMYSQL_USER_MASTER=root  MYSQL_PASS_MASTER='123'  MYSQL_HOST_MASTER=192.168.99.137MYSQL_USER_SLAVE=root  MYSQL_PASS_SLAVE='123'  MYSQL_HOST_SLAVE=192.168.1.220tmpfile_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 "*****************************************************************************"	fielif  [ ${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"firm -rf ${tmpfile_01}rm -rf ${tmpfile_02}