Saturday, 4 March 2017

Script to monitor MYSQL replication

About Script: 
This shell script can be use as an alternative if there is no monitoring tool in place

Function of script?
It will raise alarm on any of below three conditions comes true
  1. If slave is lagging
  2. If slave is struck
  3. If slave is down 
How to Use?
This script can be scheduled with CRON and will work on polling based mechanism at defined intervals.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/bash
Date=`date`
#DBhost=node1
DBhost=localhost
hostname=`hostname`
DBuser="root"
DBpass="google@123"
MaxDelay="10"

LOG="/tmp/replication_monitor.log"
Slave_OLD_pos=`cat /tmp/.slave_pos`
EMAIL="jasjeet9800@gmail.com"
mysql -u"$DBuser" -p"$DBpass" -h"$DBhost" -e'show slave status \G' > "$LOG"
Slave_NEW_position=`cat "$LOG" |grep "Exec_Master_Log_Pos" |awk '{print $2}'`
slavedelay=`cat "$LOG" |grep "Seconds_Behind_Master"|awk '{print $2}'`

echo "***********************"
echo "Slave old position: "$Slave_OLD_pos
echo "Slave new position: "$Slave_NEW_position
echo "Slave Delay: "$slavedelay"Sec"
echo "***********************"

if [[ $slavedelay -gt $MaxDelay ]]; then
#echo $slavedelay
echo "Slave is behind"
/usr/bin/mail $EMAIL -s "Slave is behind master by $slavedelay on $hostname sec at $Date" <  $LOG
fi

if [[ $slavedelay == "NULL" ]]; then
/usr/bin/mail $EMAIL -s "Slave error occured on $hostname sec at $Date" <  $LOG
echo "Slave error occured"
exit 1
fi

if [[ $Slave_NEW_position -eq $Slave_OLD_pos ]]; then
echo $Slave_NEW_position
/usr/bin/mail $EMAIL -s "Slave is struck on $hostname sec at $Date" <  $LOG
echo "Slave is struck"
fi
echo $Slave_NEW_position > /tmp/.slave_pos
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1 comment: