MySQL Replication Latency Monitoring script

#!/bin/sh#[email protected] #repdelay. sh# See how much event#### #1 the replication delay. 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-E cho.# parameter $ = message# parameter $ = color{local default_msg= "No message passed." message=${1:-$default _msg}# If no input is the default value default_msg.color=${2:-black}# if no input is the default 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 Restore default printf "$black" Return}cechon () # color-echo.# parameter 1 $ = Messag e# Parameter 2 $ = color{local default_msg= "No message passed." # doesn ' t really need to be a local variable.message=${1:-$default _msg}# if $ $ is not entered then the default value default_msg.color=${2:-black}# if $ 1 No input is the default value 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 restores the default 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 login user password sqlresponse= ' mysql-h${slavehost}-u${mysql_user}-p${mysql_pass} mysql-e "show slave status \g" |grep ' \&lt ; Slave_sql_running\> ' |awk ' {print $} ' ioresponse= ' Mysql-h${slavehost}-u${mysql_user}-p${mysql_pass} mysql-e " Show slave status \g "|grep ' \<slave_io_running\> ' |awk ' {print $} ' behind= ' Mysql-h${slavehost}-u${mysql_user}- P${mysql_pass} mysql-e "show slave status \g" |grep ' \<seconds_behind_master\> ' |awk ' {print $} ' if ["$SQLresponse "=" No "]; Thenecho "" error= "MySQL server ($slavehost) replication has stopped working : Slave_sql_running:no; Unable to get replication latency. " Cechon "${error}" Redecho "" Exit 0fiif ["$IOResponse "=" No "]; Thenecho "" error= "MySQL server ($slavehost) replication has stopped working : Slave_io_running:no; Unable to get replication latency. "  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 '} ' |awk-f '. " ' {print $} ' b= ' sed-n '/\<relay_master_log_file\>/p ' ${tmpfile_02} |sed ' s/^[]*//g ' |awk-f ': ' {print $} ' |awk- F "." ' {print $} ' bhtime= ' sed-n '/\<seconds_behind_master\>/p ' ${tmpfile_02} |sed ' s/^[]*//g ' |awk-f ': ' {print $} ' If ["$b" = "$a"];thenc= ' tail-1 ${tmpfile_01} |  Grep-v "Log_name" |awk ' {print $} ' d= ' sed-n '/\<read_master_log_pos\>/p ' ${tmpfile_02} |sed ' s/^[]*//g ' |awk-f        ":" ' {print $} ' k= ' Sed-n "/\<exec_master_log_pos\>/p" ${tmpfile_02} |sed ' s/^[]*//g ' |awk-f ': ' {print $} ' 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 "Ech O "*****************************************************************************" Fielif [${b}-lt ${a}];thenexbin= ' Sed-n '/\<exec_master_log_pos\>/p ' ${tmpfile_02} |sed ' s/^[]*//g ' |awk-f ': ' {print $} ' y= ' Sed-n '/\<Rela  y_master_log_file\>/p "${tmpfile_02} |sed ' s/^[]*//g ' |awk-f ': ' {print $} ' #u = ' awk ' {if ($1~/'" ' $y ' "/) print NR} ' ${tmpfile_01} ' u= ' Grep-n "$y" ${tmpfile_01}|awk-f: ' {print $ '} ' x= ' awk ' nr>= ' ' $u ' ' {print $} ' ${tmpfile_01}| awk ' begin{total=0}{total+=$2}end{print total} ' re= ' expr $x-$exbin ' echo-e ' \e[1;31m there is multiple log files are N OT synchronized,the Events Is:${re} \e[0m "Firm-rf ${tmpfile_01}rm-rf ${tmpfile_02}

MySQL Replication Latency Monitoring script