Shell develops MySQL master-slave monitoring script

Monitor whether the master-slave synchronization of MySQL is abnormal. If not, send SMS or email to the administrator. 1) develop a daemons script to detect every 30 seconds.
2) if the following error number (11581159100810071062) occurs during synchronization, the error will be skipped. Stage 3:
3) if the IO and SQL threads are abnormal, notify the administrator by email

Use array technology to implement the above script (get master-slave judgment and error number part)

The master-slave monitoring script is as follows:

#!/bin/bash
CODE=(
1158
1159
1008
1007
1062
2003
)
fun_Base(){
#1. Define variables

#1.1 capture IP address
IP=`ifconfig eth0 |awk 'NR==2{print $2}'`

#1.2 get slave IO and SQL status, Err code
My_SQL=`mysql -e "show slave status\G" |egrep "SQL_Running:" |awk '{print $NF}'`
My_IO=`mysql -e "show slave status\G" |egrep "IO_Running:" |awk '{print $NF}'`
My_CODE=`mysql -e "show slave status\G" |egrep "Last_IO_Errno:" |awk '{print $NF}'`

#1.3 timing time variable
Time=`date +%F-%H:%M:%S`

#1.4 define log directory
DIR=/tmp/slave_${Time}
Status_Log=$DIR/slave_status_${Time}.log
Check_log=$DIR/slave_check_${Time}.log
Erro_log=$DIR/slave_err_${Time}.log
#1.5 define email
Total="$IP slave status $Time"
Mail_Rec="jasonminghao@163.com"

#2. Save the state of slave to the log file
[ -d $DIR ] || mkdir $DIR -p
mysql -e "show slave status\G" >$Status_Log
}

#3. Error code to judge the slave status
fun_Status(){
RETVAL=0
for  ((i=0;i<${#CODE[*]};i++))
do
    if [ $My_CODE -eq ${CODE[i]}  ];then
       mysql -e "stop slave;" && RETVAL=$?
       [ $RETVAL -eq 0 ] && mysql -e "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;" && RETVAL=$?
       [ $RETVAL -eq 0 ] && mysql -e "start slave;" && RETVAL=$?
       [ $RETVAL -eq 0 ] && echo "slave errno code is successful." >$Erro_log
       [ $RETVAL -eq 0 ] && mail -s "$Total" $Mail_Rec <$Status_Log && mail -s "$Total" $Mail_Rec <$Erro_log
    fi
done
}

#4. Judge whether IO and SQL threads are normal
fun_Check(){
  if [ "$My_SQL" == "Yes" -a "$My_IO" == "Yes" ];then
    echo "slave status is successful." 
    echo "slave status is successful." >$Check_log
    mail -s "$Total" $Mail_Rec <$Status_Log
    mail -s "$Total" $Mail_Rec <$Check_log
  else
    echo "slave status is failed."
    echo "slave status is failed." >>$Check_log
    mail -s "$Total" $Mail_Rec <$Status_Log
    mail -s "$Total" $Mail_Rec <$Check_log
 fi
}

#5. Main function
main(){

while true
do
 fun_Base
 fun_Status
 fun_Check
 sleep 20
done
}

main

Tags: Linux MySQL SQL

Posted on Mon, 02 Dec 2019 05:52:55 -0500 by dsinghldh