Monitor MySQL Replication Status with a Bash Script

This is a Bash script that check if the replica (slave) health status. If there is an issue, it will exist with non-zero status. This is useful when used with monitoring tools like [Monit](https://mmonit.com/monit/).

#!/usr/bin/env bash

# ==============================
# MySQL Replication Health Check
# ==============================

# Configurable variables
MYSQL_USER="${MYSQL_USER:-root}"
MYSQL_PASS="${MYSQL_PASS:-}"
MYSQL_HOST="${MYSQL_HOST:-127.0.0.1}"
MYSQL_PORT="${MYSQL_PORT:-3306}"
MAX_LAG="${MAX_LAG:-60}"   # Max allowed replication lag in seconds

MYSQL_CMD="mysql -u\({MYSQL_USER} -h\){MYSQL_HOST} -P${MYSQL_PORT} --connect-timeout=5"

if [[ -n "$MYSQL_PASS" ]]; then
    MYSQL_CMD="\({MYSQL_CMD} -p\){MYSQL_PASS}"
fi

# Try MySQL 8+ syntax first
STATUS=\((\)MYSQL_CMD -e "SHOW REPLICA STATUS\G" 2>/dev/null)

# Fallback for older versions
if [[ -z "$STATUS" ]]; then
    STATUS=\((\)MYSQL_CMD -e "SHOW SLAVE STATUS\G" 2>/dev/null)
fi

if [[ -z "$STATUS" ]]; then
    echo "CRITICAL: Replication not configured or MySQL not reachable."
    exit 2
fi

# Extract fields
IO_RUNNING=\((echo "\)STATUS" | awk -F': ' '/Replica_IO_Running|Slave_IO_Running/ {print $2}')
SQL_RUNNING=\((echo "\)STATUS" | awk -F': ' '/Replica_SQL_Running\>|Slave_SQL_Running\>/ {print $2}')
SECONDS_BEHIND=\((echo "\)STATUS" | awk -F': ' '/Seconds_Behind_Source|Seconds_Behind_Master/ {print $2}')
LAST_IO_ERROR=\((echo "\)STATUS" | awk -F': ' '/Last_IO_Error/ {print $2}')
LAST_SQL_ERROR=\((echo "\)STATUS" | awk -F': ' '/Last_SQL_Error/ {print $2}')

EXIT_CODE=0

# Validate IO thread
if [[ "$IO_RUNNING" != "Yes" ]]; then
    echo "CRITICAL: IO thread not running."
    EXIT_CODE=2
fi

# Validate SQL thread
if [[ "$SQL_RUNNING" != "Yes" ]]; then
    echo "CRITICAL: SQL thread not running."
    EXIT_CODE=2
fi

# Check lag
if [[ "\(SECONDS_BEHIND" == "NULL" || -z "\)SECONDS_BEHIND" ]]; then
    echo "CRITICAL: Replication lag unknown."
    EXIT_CODE=2
elif (( SECONDS_BEHIND > MAX_LAG )); then
    echo "WARNING: Replication lag is \({SECONDS_BEHIND}s (threshold \){MAX_LAG}s)."
    [[ $EXIT_CODE -lt 1 ]] && EXIT_CODE=1
fi

# Check errors
if [[ -n "$LAST_IO_ERROR" ]]; then
    echo "CRITICAL: IO Error: $LAST_IO_ERROR"
    EXIT_CODE=2
fi

if [[ -n "$LAST_SQL_ERROR" ]]; then
    echo "CRITICAL: SQL Error: $LAST_SQL_ERROR"
    EXIT_CODE=2
fi

if [[ $EXIT_CODE -eq 0 ]]; then
    echo "OK: Replication running. Lag: ${SECONDS_BEHIND}s"
fi

exit $EXIT_CODE