Check MySQL Replication Status

#!/bin/bash

# Filename: /usr/local/sbin/mysql-replication-check.sh
# Check mysql 'show slave status' to ensure replication lag is less than 120s

# Max permitted replication lag in seconds
lag_threshold='120'

MAILTO="rsubr@bitsathy.ac.in,nmc@bitsathy.ac.in,ithelpdesk@bitsathy.ac.in,deepikaj@bitsathy.ac.in"
MAILTO="${MAILTO},rv.nataraj@bitsathy.ac.in,amp@bitsathy.ac.in"

WGET="wget -O /dev/null -q"
HC_PING="http://hc.bit.lan/ping/766c52f1-74f4-4b3c-96a8-25"

# Indicate job start
${WGET} "${HC_PING}/start"

# Create temporary SLAVE_STATUS output file
SLAVE_STATUS=$(mktemp)

# Remove temp files on exit
trap "{ rm -f $SLAVE_STATUS; }" EXIT

mysql -e 'show slave status\G' > ${SLAVE_STATUS}

last_errno=$(grep Last_Errno: ${SLAVE_STATUS} | awk '{print $2}')
lag_sec=$(grep Seconds_Behind_Master: ${SLAVE_STATUS} | awk '{print $2}')
slave_io_running=$(grep  Slave_IO_Running:  ${SLAVE_STATUS} | awk '{print $2}')
slave_sql_running=$(grep Slave_SQL_Running: ${SLAVE_STATUS} | awk '{print $2}')

# Exit now if all ok
if  [ "$last_errno" -eq "0" ] && \
    [ "$slave_io_running"  == "Yes" ] && \
    [ "$slave_sql_running" == "Yes" ] && \
    [ "$lag_sec" -lt "$lag_threshold" ]; then

        # Indicate job end
        ${WGET} "${HC_PING}"
        exit 0
fi

# Send failure email to everyone
mail --content-filename=mysql-show-slave-status.txt --attach="${SLAVE_STATUS}" \
    -s 'URGENT: camps2 slave DB is out of sync' \
    "${MAILTO}" <<EOT

Dear CAMPS Administrator,

CAMPS2 mariadb replication problem: slave potentially out of sync
Output of the 'show slave status' command is attached.

Slave status:
Last_Errno: $last_errno -- expecting (expecting "0")
Slave_IO_Running:  $slave_io_running -- (expecting "Yes")
Slave_SQL_Running: $slave_sql_running -- ("expecting "Yes")
Seconds_Behind_Master: $lag_sec -- (max threshold $lag_threshold)

Generated by: camps2:/usr/local/sbin/mysql-replication-check.sh
Generated on: `date`

EOT

# Indicate replication check failure
${WGET} "${HC_PING}/fail"
exit 1