Mysql replication delay monitoring script and mysql delay script

Source: Internet
Author: User

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}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.