Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Saturday, 2 June 2018

MySQL Replication Monitoring Alert Script

I have written and used this script to get MySQL replication status alert on all scenario like Replication Down/ Second_behind_master going up / and again Replication status Up .

You can modify the notification logic as per your convenience.  

Just create /usr/bin/getmysqlrepl.sh blank script file and copy-paste below script. 

#!/bin/bash
# Checks MySQL Replication status.  Sends user(s) a notification of status
# BY- Alok Kumar Singh @ www.getmysql.info
# 31-may-2018 11:26 AM

MasterHost="192.168.1.100"
SlaveHost="192.168.1.101"
emails="alok@getmysql.info" #multiple emails space separated
HOST="sudo mysql --user=user --password=password" ##for your password security we suggest you kindly create seperate user for Alert with all Show/Select Privileges.


SQLresponse=$($HOST -e "show slave status \G" |grep -i "Slave_SQL_Running"|gawk '{print $2}')
IOresponse=$($HOST -e "show slave status \G" |grep -i "Slave_IO_Running"|gawk '{print $2}')
SSTATUS=$($HOST -e "SHOW SLAVE STATUS\G" | egrep "Master_Host|Master_User|Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Last_Errno|Last_Error|Seconds_Behind_Master")
SLAVEBEHIND=$($HOST -e "show slave status \G" |grep -i "Seconds_Behind_Master"|gawk '{print $2}')



if [ "$SQLresponse" == "No" ]
 then
       ERRMSG="Replication on the slave MySQL server($SlaveHost) has stopped working"$'\n'"$SSTATUS"
    DownSubject="Replication Status Down on $SlaveHost"
      status=1
elif [ "$IOresponse" == "No" ]
 then
      ERRMSG="Replication on the slave MySQL server($SlaveHost) has stopped working"$'\n'"$SSTATUS"
      DownSubject="Replication Status Down on $SlaveHost"
   status=1
elif [ "$SLAVEBEHIND" == "NULL" ]
 then
  ERRMSG="The Slave is reporting 'NULL' (Seconds_Behind_Master)"$'\n'"$SSTATUS"
  DownSubject="Replication Status Down on $SlaveHost"
  status=1
elif [ "$SLAVEBEHIND" != 0 ]
 then
    ERRMSG="The Slave is at least $SLAVEBEHIND seconds behind the master (Seconds_Behind_Master)"$'\n'"$SSTATUS" 
 DownSubject="Replication Status Lag Behind $SLAVEBEHIND on $SlaveHost"
 status=1
else
 GoodSubject="Replication Status UP on $SlaveHost"
    GoodMessage="MySQL replication on $SlaveHost is working fine"$'\n'"$SSTATUS"
 status=0
fi

# Sending mail Alert....

if [ $status == 1 ]
 then
      for address in $emails; do
                echo -e $ERRMSG | mail -s "$DownSubject" $address 
                echo "$(date): On $SlaveHost Replication down, sent email to $address"$'\n'"$ERRMSG" >> /home/alok/maillog.log ##writing log file for mail alert
      done
elif [ $status == 0 ]
 then
      for address in $emails; do
             echo -e $GoodMessage | mail -s "$GoodSubject" $address
                echo "$(date): On $SlaveHost Replication is up, Mail sent to $address"$'\n'"$GoodMessage" >> /home/alok/maillog.log
      done
fi

#########################################################
### Copyright@ Alok Kumar Singh with www.getmysql.info###
#########################################################
If you will get any issue , kindly contact us _ Click Here or write us comment.

No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...