Simple MySQL slave monitor

Here's a simple Bash script to monitor a MySQL slave, checking that a) MySQL is running, b) the slave process is running, and c) that it's not too far behind the master.

We have a database slave that gets taken down every night for backups and this script checks that it came back up OK.

It's designed to be executed via cron and it runs on our production Solaris servers from around the time that the backups should finish. With a couple of minor mods (path to mysql, for instance) it runs just fine on Linux too.

#!/bin/bash

# A simple MySQL process/replication status monitor - checks for
# a Seconds_Behind_Master threshold and that both MySQL and the 
# slave process are running.

# Errors
ERR_SLAVE_STOPPED=1
ERR_SLAVE_OVER_THRESHOLD=2
ERR_MYSQL_STOPPED=3

ERR=0

# Backup vars - if a backup's running we don't bother alerting
BACKUP_DIR=/u07/mysql/exports

# Threshold settings
SBM_THRESHOLD=150 # Seconds behind master threshold 
PROCS_THRESHOLD=2 # Minimum number of MySQL processes

# Values
NUM_SBM=`/opt/coolstack/mysql/bin/mysql -e "SHOW SLAVE STATUS \G" \
    | grep Seconds_Behind_Master | cut -d ":" -f 2 | sed -e 's/\ //g'`
NUM_PROCS=`pgrep mysql | wc -l | sed -e 's/\ //g'`

# Tests
if [ "$NUM_PROCS" -lt $PROCS_THRESHOLD ]; then
    # Check backup is running
    if [ ! -f $BACKUP_DIR/backingup ]; then
        MSG="There are less than $PROCS_THRESHOLD MySQL processes running"
        ERR=$ERR_MYSQL_STOPPED
    fi
fi

if [ "$NUM_SBM" = "NULL" ]; then
    MSG="The slave replication process has faulted"
    ERR=$ERR_SLAVE_STOPPED
fi

if [ "$NUM_SBM" -gt $SBM_THRESHOLD ]; then
    MSG="The slave replication process is $NUM_SBM seconds behind the master"
    ERR=$ERR_SLAVE_OVER_THRESHOLD
fi

# Send email if errors
if [ $ERR -gt 0 ]; then
    echo $MSG | mailx -s "[CRON] MySQL Errors on `hostname`" you@example.com
    exit $ERR
fi

exit 0