monitoring MySQL master-slave synchronization is abnormal and alarm enterprise case simulation

Enterprise Face question 1: (production case): monitor MySQL master-slave synchronization is abnormal, if abnormal, send sms or mail to the administrator. Note: If there is no master-slave synchronization environment, you can use the following text in the file to read to simulate:
Phase 1: Develop a daemon script to detect every 30 seconds.
Phase 2: If the following error number (1158,1159,1008,1007,1062) is present in the synchronization, the error is skipped.
Phase 3: Use array technology to implement the above script (get master-slave judgment and error number section)

This topic from: http://oldboy.blog.51cto.com/2561410/1632876

Answer reference 1:

#!/bin/sh#oldboy linux training#2015-05-17# Description: This script from the old boy linux21 period student Zhang Yao development! # source function library.  /etc/init.d/functions# defined variablesmysqluser=rootmysqlpass=oldboy123mysqlport= 3307mysqlsock=/data/$MysqlPort/mysql.sockerrorno= (1158 1159 1008 1007 1062) errorlog=/tmp/ Error_skip.logmysqlcmd= "/application/mysql/bin/mysql -u$mysqluser -p$mysqlpass -s $ Mysqlsock "# judge mysql server is ok? [ -s  $Mysqlsock  ] | | {echo  "Maybe mysql have someting wrong" exit 1}# defined skip  Error functionsfunction error_skip () {local flagflag=0for num in ${errorno[@]}   do    if [  "$"  ==  "$num"  ];then         $MysqlCmd  -e ' Stop slave;set global sql_slave_skip_counter=1;start  slave; '        echo  "$ (date +%f_%r)  $1"  >> $errorlog     else            echo  "$ (date +%f_%r)  $1"  >> $errorlog         ((flag++))     fidone[   "$flag"  ==  "${#ErrorNo [@]}"  ] &&{action  "Mysql slave"  /bin/ falseuniq  $errorlog |mail -s  "Mysql slave is error"  [email protected]}} # defined check slave functionsfunction check_slave () {MyResult= ' $MysqlCmd  -e ' Show slave status\g ' |egrep  ' _running| behind_master| Sql_errno '  |awk  ' {print  $NF} ' array= ($MyResult) if [  "${array[0]}"  ==  "Yes"  -a  "${array[1"} " == " Yes " -a " ${array[2]} " == " 0 " ]   thenaction  "Mysql slave"  /bin/true| | Error_skip ${arrAy[3]}fi}# defined main functionsfunction main () {while true   do      check_slave     sleep 60done}main

Answer reference 2:

#!/bin/sh#oldboy linux#2015-05-17# Description: This script from the old Boys Education 21 period student Li Yunyu! User=rootpassword=123456port=3307error= (1158 1159 1008 1007 1062) MYSQLCMD= "mysql -u$ user -p$password -s /data/$PORT/mysql.sock "Is_run () {  [  ' lsof -i: $PORT |WC  -l '  -lt 2 ]&&{    echo  ' mysql server is  Stopped "    exit 1  }}status_array () { status= ($ ($MYSQLCMD  -e   "Show slave status\g" |egrep  "_running| last_errno| Behind_master "|awk  ' {print  $NF} '))}status_error () {for (i=0;i<${#error [*]};i++)] Do  if  [  "$"  ==  "${error[$i]}"  ]    then        $MYSQLCMD  -e  "stop slave;set global sql_slave_skip_counter=1;start slave;"   else       echo  "Mysql slave is failed,&nbsP;errorno is $1 "  fidone}judge_slave () {  status_array  if [  "${status[0]}"  ==  "yes"  -a  "${status[1"} " == " yes " -a " ${status[3 "}"  =  "0"  ]  then    echo  "Mysql slave is ok"   else    status_error ${status[2]}  fi}main () {While truedo   is_run  judge_slave  sleep 60done}main

Old boy teacher hint: the enterprise actual scene, also may according to the main library writes the timestamp the way to judge the database whether the synchronization is more rigorous.

This article is from the "Old boy Linux ops" blog, please be sure to keep this source http://oldboy.blog.51cto.com/2561410/1652086

monitoring MySQL master-slave synchronization is abnormal and alarm enterprise case simulation