rmancinasandClaude Opus 5
7bec2a13d8
my.jorgecuadros.com reads customer data from the Oracle VPS replica, and a replica that has silently stopped applying serves stale balances rather than erroring — so "is it replicating" needed an answer that is not a human squinting at SHOW REPLICA STATUS. Runs entirely against the replica over ssh, so it needs no credentials for the galactus master, and exits non-zero on failure so it can be driven from cron or a monitor. It deliberately does not trust the two fields an operator reaches for first. Replica_IO_Running reports Yes while the SQL thread is stopped, because the network thread is still downloading binlog it will never apply — verified by stopping SQL_THREAD and watching IO stay Yes. Seconds_Behind_Source reads 0 both when there is nothing to apply and when nothing is connected. The trustworthy signal is GTID_SUBTRACT(Retrieved, Executed): binlog fetched but not applied. NULL lag means either thread is down, so it is reported as "not applying" rather than blamed on a specific thread — the thread fields above already say which, and guessing there produced a wrong diagnosis. Uses sed rather than `head -n1`; on this machine `head` resolves to LWP's HTTP head(1), which mangles the pipeline instead of failing. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
86 lines
3.3 KiB
Bash
Executable File
86 lines
3.3 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
#
|
|
# Is the my.jorgecuadros.com read replica actually replicating?
|
|
#
|
|
# deploy/scripts/check-replication.sh
|
|
#
|
|
# Answers it from the REPLICA alone, so it needs no credentials for the
|
|
# galactus master — only ssh to the VPS. Exits non-zero when replication is
|
|
# broken or lagging, so it is usable from cron or a monitor.
|
|
#
|
|
# Why not just eyeball `SHOW REPLICA STATUS`: the two obvious fields are both
|
|
# misleading on their own.
|
|
#
|
|
# * "Replica_IO_Running: Yes" only means the network thread is alive. The SQL
|
|
# thread can be stopped with a duplicate-key error while IO keeps happily
|
|
# downloading binlog, so the replica looks busy and falls further behind.
|
|
#
|
|
# * "Seconds_Behind_Source: 0" reads 0 both when there is genuinely nothing
|
|
# to apply AND when the IO thread is disconnected — there is no event to
|
|
# measure staleness against, so absence of work is reported as being current.
|
|
#
|
|
# The trustworthy check is GTID_SUBTRACT(Retrieved, Executed): binlog we have
|
|
# fetched but not yet applied. Empty means genuinely caught up.
|
|
|
|
set -uo pipefail
|
|
|
|
REPLICA_HOST="${REPLICA_HOST:-opc@163.192.62.37}"
|
|
MAX_LAG="${MAX_LAG:-30}"
|
|
|
|
raw=$(ssh -o ConnectTimeout=10 -o BatchMode=yes "$REPLICA_HOST" \
|
|
'sudo mysql -e "SHOW REPLICA STATUS\G"' 2>/dev/null)
|
|
|
|
if [ -z "$raw" ]; then
|
|
echo "FAIL: could not reach $REPLICA_HOST or mysql returned nothing"
|
|
exit 2
|
|
fi
|
|
|
|
# sed rather than `head -n1`: on some machines `head` is shadowed by LWP's
|
|
# HTTP head(1), which silently mangles the pipeline instead of erroring.
|
|
field() { printf '%s\n' "$raw" | grep -E "^[[:space:]]*$1:" | sed -n '1p' | sed -E "s/^[[:space:]]*$1:[[:space:]]*//"; }
|
|
|
|
io=$(field Replica_IO_Running)
|
|
sql=$(field Replica_SQL_Running)
|
|
lag=$(field Seconds_Behind_Source)
|
|
io_err=$(field Last_IO_Error)
|
|
sql_err=$(field Last_SQL_Error)
|
|
|
|
# The authoritative "am I caught up" test: anything fetched but not applied.
|
|
backlog=$(ssh -o ConnectTimeout=10 -o BatchMode=yes "$REPLICA_HOST" \
|
|
'sudo mysql -NB -e "
|
|
SELECT IFNULL(NULLIF(GTID_SUBTRACT(
|
|
(SELECT RECEIVED_TRANSACTION_SET FROM performance_schema.replication_connection_status),
|
|
@@GLOBAL.gtid_executed), \"\"), \"(none)\")" 2>/dev/null' 2>/dev/null)
|
|
[ -z "$backlog" ] && backlog="(performance_schema off — using lag only)"
|
|
|
|
echo "replica : $REPLICA_HOST"
|
|
echo "IO thread : $io"
|
|
echo "SQL thread : $sql"
|
|
if [ "$lag" = "NULL" ] || [ -z "$lag" ]; then
|
|
echo "lag : NULL"
|
|
else
|
|
echo "lag : ${lag}s"
|
|
fi
|
|
echo "unapplied : $backlog"
|
|
[ -n "$io_err" ] && echo "IO error : $io_err"
|
|
[ -n "$sql_err" ] && echo "SQL error : $sql_err"
|
|
|
|
rc=0
|
|
[ "$io" = "Yes" ] || { echo "FAIL: IO thread not running"; rc=1; }
|
|
[ "$sql" = "Yes" ] || { echo "FAIL: SQL thread not running"; rc=1; }
|
|
[ -n "$io_err" ] && { rc=1; }
|
|
[ -n "$sql_err" ] && { rc=1; }
|
|
# SHOW reports NULL lag whenever EITHER thread is down — there is no applied
|
|
# event to measure against. Never report which one from the lag alone; the
|
|
# thread fields above already said, and guessing produces a wrong diagnosis.
|
|
if [ "$lag" = "NULL" ] || [ -z "$lag" ]; then
|
|
echo "FAIL: lag is NULL (replication not applying)"
|
|
rc=1
|
|
elif [ "$lag" -gt "$MAX_LAG" ] 2>/dev/null; then
|
|
echo "WARN: lag ${lag}s exceeds ${MAX_LAG}s"
|
|
rc=1
|
|
fi
|
|
|
|
[ $rc -eq 0 ] && echo "OK: replica is running and caught up"
|
|
exit $rc
|