mysql複寫延遲監控指令碼，mysql延遲指令碼

mysql複寫延遲監控指令碼，mysql延遲指令碼

#!/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 inblack) 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 inblack)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" ]; thenecho "                                                                           "error="mysql伺服器($slavehost)的複製已經停止工作:Slave_SQL_Running: No;無法擷取複寫延遲情況."cechon  "${error}" redecho "                                                                           "exit 0fiif [ "$IOresponse" = "No" ]; thenecho "                                                                           "error="mysql伺服器($slavehost)的複製已經停止工作:Slave_IO_Running: No;無法擷取複寫延遲情況."cechon  "${error}" redecho "                                                                           "exit 0fi#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"  ];thenc=`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   ] ; thenecho "*****************************************************************************"echo -e "\e[1;31m  &&&&&&&Synchronization has been completed!&&&&&&& \e[0m"echo "*****************************************************************************"elif [  "${e}" -eq 0  -o "${bhtime}" -gt 0    ] ; thenecho "*****************************************************************************"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} ];thenexbin=`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}