Monitor MySQL master-slave synchronization status is abnormal, if the exception occurs, SMS or mail to the administrator

Phase 1: Develop a daemon script to detect every 30 seconds.

Phase 2: If the following error number (1158,1159,1008,1007,1062) appears in sync, skip the error

Phase 3: Use array technology to implement the above script (get master-slave judgment and error number section)

[[email protected] ~]# mysql -u root -proot -e  "show slave  Status\g; "  1. row ***************************                slave_io_state: waiting for master  to send event                   Master_Host: 172.16.1.2       #当前的mysql   Master server Host                    Master_User: myslave                   Master_Port: 3306                 Connect_Retry: 60               master_log_file: master-bin.000003           Read_Master_Log_Pos: 471                Relay_Log_File: relay-log-bin.000002                 Relay_Log_Pos: 252         Relay_Master_Log_File: master-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: Yes                      Master_SSL_Key:                              seconds_behind_master: 0     #和主库比同步延迟的秒数 

Preparation: Egrep "_running| Behind_master "Slave.log #过滤 slave_io_running:yes slave_sql_running:yes seconds_behind_m aster:0 [[email protected] ~]# egrep "_running| Behind_master "Slave.log | awk ' {print $NF} ' YesYes0

Phase one: Develop a daemon script that is instrumented every 30 seconds.

#!/bin/bashwhile Truedo array= ($ (egrep "_running| Behind_master "Slave.log|awk ' {print $NF} ')) if [" ${array[0]} "= =" Yes "-a" ${array[1]} "= =" Yes "-a" ${array[2]} "= =" 0 "] then echo" MySQL was slave is ok "else char=" MySQL slave are not OK "echo" $char "echo" $char "|mail  -S "$char" [email protected] break fi sleep 30done execution result: [[email protected] ~]# sh test.sh MySQL is slave is slave is OK

Ultimate Edition:

#!/bin/bash#date:2017-7-3#author:xcn ([email protected]) #version  1.0mysql_cmd= "mysql -u  Root -proot "errorno= (1158 1159 1008 1007 1062) while truedo  array= ($ ($ mysql_cmd -e  "Show slave status\g" |egrep  ' _running| behind_master| Last_sql_errno ' |awk  ' {print  $NF} '))   if [  "${array[0]}"  ==  "Yes"   -a  "${array[1"} " == " Yes " -a " ${array[2 "}"  ==  "0"  ]  then     echo  "Mysql is slave is ok"   else       for  ((i=0;i<${#errorno [*]};i++)]       do         if [  "${array[3]}"  =  "${errorno[$i]}"  ];then          $mysql _cmd -e  "Stop slave &&set  global sql_slave_skip_counter=1;start slave; "         fi      done       char= "Mysql slave is not ok"       echo   "$char"       echo  "$char" |mail -s  "$char"  [email  Protected]      break  fi  sleep 30done

Hint: This script can be used in production environment to monitor MySQL master-slave synchronization state is abnormal, according to

' _running| behind_master| Last_sql_errno '

This judgment, if not normal, will further determine the status code, and then output, will send an e-mail or text message to the operation and maintenance personnel

This article is from the "Little Rookie" blog, please be sure to keep this source http://baishuchao.blog.51cto.com/12918589/1944170

Monitor MySQL master-slave synchronization status is abnormal, if the exception occurs, SMS or mail to the administrator