How to solve the Shell monitoring Mysql master-slave interrupt delay and the number of connections

This article introduces how to solve the Shell monitoring Mysql master-slave interrupt delay and the number of connections, the content is very detailed, interested friends can refer to, hope to be helpful to you.

#! / bin/bash# log configuration curdate=$ (date + "% Y%m%d") curtime=$ (date +'% Y-%m-%d% HV% MV% S') logname= "repl_check_" $curdate ".log" logfile=/mysqldata/repl_check_log/$logname## two ways to obtain the master-slave relationship that needs to be detected. Recommend the second method (storing DB information in the metadata table for easy management) # 1. From the inspection list of library instances, the hard-coded list is # Slave_list= ("10.10.10.10 3306 10.10.10.100 3306") # main library list # MainDB_list= ("101.101.101.101) 3306 101.101.101.100 3306") # 2. Get the instance list Slave_list=$ (/ usr/bin/mysql-h227.0.0.1-P3306-uroot-- default-character-set=utf8 cmdb-Ne'select concat (slave_ip, ":", port) from cmdb_mysql_info where slave_inst in () from the metadata information type ') MainDB_list=$ (/ usr/bin/mysql-h227.0.0.1-P3306-uroot-- default-character-set=utf8 cmdb-Ne'select concat (slave_ip, ":", port) from cmdb_mysql_info where msater_inst in () ') # RO instance replication status inspection function slv_check () {local ip_port= ($1) local wechat_url= ($2) for i in ${ip_port [@]} do send_flag= "init" ProcCnt=0 ip=$ (echo $I | awk-F ":"' {print $1}') port=$ (echo $I | awk-F ":"'{print $2}') io_thread=$ (mysql-uroot-h$ip-P$port-e " Show slave status\ G "| grep Slave_IO_Running: | awk'{print $2}') sql_thread=$ (mysql-uroot-h$ip-P$port-e" show slave status\ G "| grep Slave_SQL_Running: | awk'{print $2}') slv_delay=$ (mysql-uroot-h$ip-P$port-e" show slave status\ G "| grep Seconds_Behind_Master: | awk'{print $2}') ProcCnt=$ (mysql-uroot-h$ip-P$port-Ne" select count (*) from information_schema.processlist " ") TotalProc=$ (mysql-uroot-h$ip-P$port-Ne" show variables like 'max_connections'; "| awk' {print $2}') ProcPct=$ (printf"% d "$((ProcCnt*100/TotalProc) echo $TotalProc $ProcCnt $ProcPct if [!-n" $io_thread "] & & [!-n" $sql_thread "]; then msg=" instance "$ip": "$port": master / slave configuration is empty, please check it in time! " Else if [x "$io_thread" = = x slv_delay Yes'] & & [x "$sql_thread" = = x cities Yes']; then if [$slv_delay-Yes10] Then msg= "instance" $ip ": $port": delay "$slv_delay" second "echo $curtime"$msg > > $logfile else send_flag=" replication_ok "msg=" instance "$ip": " $port ": replication_ok" echo $curtime "$msg > > $logfile fi else msg=" instance "$ip": "$port" If io_thread or sql_thread is disconnected, please check "echo $curtime", "$msg > > $logfile fi fi if [$ProcPct-gt 75] & & [$ProcPct-le 90] Then msg= "instance" $ip ":" $port "connection percentage reaches" $ProcPct "%, please note!" Echo $curtime "$msg > > $logfile elif [$ProcPct-gt 90]; then msg=" instance "$ip": "$port" connection percentage reaches "$ProcPct"%, please check it now! " Echo $curtime "$msg > > $logfile else msg=" instance "$ip": "$port" connection percentage reaches "$ProcPct"% Normal "echo $curtime"$msg > > $logfile fidone} function MasterLoadMon () {local ip_port= ($1) for i in ${ip_port [@]} do ip=$ (echo $I | awk-F": "'{print $1}') port=$ (echo $I | awk-F": "'{print $2}') ProcCnt=$ (mysql-uroot-h$ip-P$port-Ne" select count (*) from information_schema.processlist ") TotalProc=$ (mysql-uroot-h$ip-P$port-Ne" show variables like 'max_connections'; "| awk' {print $2}') ProcPct=$ (printf"% d "$((ProcCnt*100/TotalProc) if [$ProcPct-gt 75] & & [$ProcPct-le 90]; then msg=" instance "$ip": "$port" connection percentage reaches "$ProcPct"%, please note! " Echo $curtime "$msg > > $logfile elif [$ProcPct-gt 90]; then msg=" instance "$ip": "$port" connection percentage reaches "$ProcPct"%, please check it now! " Echo $curtime "$msg > > $logfile else msg=" instance "$ip": "$port" connection percentage reaches "$ProcPct"% Normal "echo $curtime"$msg > > $logfile fidone} # # Wechat official account sends an alarm function wechat_send () {local w_url=$1local w_msg=$2echo" starting send msg to "$w_urlcurl" $1 "\-H 'Content-Type: application/json'\-d' {" message ": { "content": "'$wimpmsg'"}}'} # Master function function main () {slv_check "${slave_list [@]}" $prod_url "MasterLoadMon" ${MainDB_list [@]} "" $prod_url "# # Start Running...main on how to solve the Shell monitoring Mysql master-slave interrupt delay and the number of connections are shared here. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.