Monitor MySQL Replication Status with a Bash Script

The blog is older than you know. I prefer counting from the emergence of one integral anomaly to the emergence of the next, in which case this is the forth version.

#!/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

Monitor MySQL Replication Status with a Bash Script

Comments

More from this blog

Cron Prompts vs Agent Skills: A Separation of Concerns

Da Ma Cai 大马彩 API

Cheatsheet & Examples: chmod

Cheatsheet & Examples: chown

Command Palette

Comments

More from this blog