replication monitoring script

This script monitors mysqld and replication status. If mysql is not running or replication slave is not running then it will log messages into log file. you can customize this script to send an alert via email etc.

##########

HOST=`hostname -s`

USR=root

PASS=xxxxx

MYSQL="/usr/bin/mysql -h$HOST -u$USR -p$PASS"

TMP=/tmp/mysql.log

LOG=/var/logs/monitoring.txt

MYSQLADMIN="/usr/bin/mysqladmin -h$HOST -u$USR -p$PASS"

MAXBEHIND=7200 # [Critical] Two hours behind

MINBEHIND=3600 # [Warning] One hour behind

# Check if mysqld is alive and accepting connections

IS_ALIVE=`$MYSQLADMIN ping | grep -c 'alive'`

if [ "$IS_ALIVE" != "1" ]; then

ERRMSG="[Critical] MySQL Down. `date`"

echo $ERRMSG >> $LOG

exit

fi

# Replication Monitoring

$MYSQL -e"show slave status\G" > $TMP

SB=`grep Seconds_Behind_Master $TMP | cut -f2 -d: | tr -d " "`

SBNUM=$SB

if [ "$SB" = "NULL" ]; then

ERRMSG="[Critical] Replication Down. `date`"

echo $ERRMSG >> $LOG

$MYSQL -e"show slave status\G" >> $LOG

elif [ $SBNUM -ge $MAXBEHIND ]; then

ERRMSG="[Critical] Replication behind ${SBNUM}s. `date`"

echo $ERRMSG >> $LOG

elif [ $SBNUM -ge $MINBEHIND ]; then

ERRMSG="[Warning] Replication behind ${SBNUM}s. `date`"

echo $ERRMSG >> $LOG

fi

########################

Check log file for warnings

shell# tail -f /var/logs/monitoring.txt