Mysql replication delay monitoring script and mysql delay script
#! /Bin/sh # ocpyang@126.com # repdelay. sh # view the specific number of replication delays ##### 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. # parameter $1 = message # parameter $2 = color {local default_msg = "No message passed. "message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.color =$ {2:-black} # If $1 is not input, the default value is 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 restores the default value printf "$ black" return} cechon () # Color-echo. # parameter 1 $1 = message # parameter 2 $2 = color {local default_msg = "No message passed. "# doesn' t really need to be a local variable. message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.color =$ {2:-black} # If $1 is not input, the default value is 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" tp Ut sgr0 # tput sgr0 restores the default value printf "$ black" return }#### 2. jude the rep delay status # configure rep master and slave envmasterhost = "192.168.99.htm" slavehost = "192.168.1.220" mysql_user = root # mysql username mysql_pass = '2016' # mysql username 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 "]; the replication of the thenecho "" error = "mysql Server ($ slavehost) has stopped working: Slave_ SQL _Running: No; the replication delay cannot be obtained. "cechon" $ {error} "redecho" "exit 0 fiif ["$ IOresponse" = "No"]; thenecho "" error = "copying of mysql Server ($ slavehost) has stopped working: Slave_IO_Running: No; unable to obtain replication latency. "cechon" $ {error} "redecho" "exit 0fi # set mysql slave = root MYSQL_PASS_MASTER = '000000' MYSQL_HOST_MASTER = 192.168.99.137MYSQL _ USER_SLAVE = root slave = '000000' slave = 192.168.1.220tmpfile _ 01 = "tmp01. 'date when policyymm1_d1_h1_m1_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 '{pr Int $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; 3 1 m & Synchronization has been completed! & \ E [0 m "echo "************************* **************************************** * *********** "elif [" $ {e} "-eq 0-o" $ {bhtime} "-gt 0]; thenecho "************************************** ***************************************" echo-e "\ e [1; 31 m Has been synchronized to the same log file! Wait a moment \ e [0 m "echo-e" \ e [1; 31 m Not synchronized binlog events is :$ {dfevt }, behind master tims is $ {bhtime} \ e [0 m "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; 31 m There are multiple log files are not synchronized, the events is :$ {re} \ e [0 m "firm-rf $ {tmpfile_01} rm-rf $ {tmpfile_02}