Reads from a Replica Must Show How Many Minutes Old the Data Is

A reporting system I built for one organization reads from a replicated database rather than the primary. The reason is that the primary serves day-to-day operations, with people using it all day, and I did not want heavy reporting queries getting in the way. One day, someone called to say the numbers in the reports did not match what they saw in the main system. Only after digging into it did I find that replication had stopped the night before. The website still worked normally, the graphs looked fine, and there was not a single error. The data was just stuck at yesterday. So I sat down and wrote a monitor, then made the website always display a label showing how many minutes old the data on screen was.

The database names, table names, and IP addresses in this article are made up. The logic and threshold values are what I actually use.

The Problem Is the Silence, Not the Slowness

Two Signals to Check Together That Measure Different Things

The first signal is provided by MariaDB itself, available through SHOW SLAVE STATUS.

mariadb -h 10.10.10.3 -u"$DB_USER" -p"$DB_PASS" -e "SHOW SLAVE STATUS\G" \
  | grep -E 'Slave_(IO|SQL)_Running|Seconds_Behind_Master|Last_SQL_Error'

The second signal is one I measure myself: business data freshness, comparing the latest date in the same table on the primary and the replica.

-- ยิงคำสั่งนี้ทั้งสองเครื่อง แล้วเอาผลมาลบกัน
SELECT MAX(closed_at) FROM core_db.tx_daily;

I stress this because it fooled me before. I checked Seconds_Behind_Master, saw 0, and relaxed, but the data had already been stale for two days. The reason was that the IO thread had died at the start, and everything in the relay log had long since been processed. That 0 meant "nothing left to process," not "the data is fresh."

Credentials Must Come from Outside the Script

Before getting into the script, I want to cover this first because I got it wrong in the first version: I put the username and password right at the top of the script, and that script was already committed to the repo.

# /etc/default/replica-watch  — chmod 600 root:root ไม่อยู่ใน repo
DB_USER=monitor_ro
DB_PASS=<ใส่ค่าจริงที่นี่>
MASTER_HOST=10.10.10.2
REPLICA_HOSTS="10.10.10.3 10.10.10.4"
WHITELIST="10.10.10.7,10.10.10.2"

#!/bin/bash
# replica_watch.sh — READ-ONLY ต่อ master/replica ; เขียนผลลงฐานของแอปเราเองเท่านั้น
set -o pipefail
. /etc/default/replica-watch
: "${DB_USER:?ยังไม่ได้ตั้งค่าใน /etc/default/replica-watch}"
: "${DB_PASS:?}"

LAG_SECS_WARN=1800     # ตามหลังเกิน 30 นาที = lagging
LAG_DAYS_WARN=2        # ข้อมูลธุรกิจเก่ากว่า master เกิน 2 วัน = lagging

q(){ mariadb -h "$1" -u"$DB_USER" -p"$DB_PASS" -N -e "$2" 2>/dev/null; }

Read SHOW SLAVE STATUS Correctly

There is a small trap here that confused me for a while: if I add -N out of habit to remove the table headers, the output of \G no longer comes out in the Field: value format that grep can match.

# ผิด: ใส่ -N แล้ว \G ไม่ออกเป็น Field: value
SS=$(mariadb -h "$H" -u"$DB_USER" -p"$DB_PASS" -N -e "SHOW SLAVE STATUS\G" 2>/dev/null)

# ถูก: ไม่ใส่ -N สำหรับ \G โดยเฉพาะ แล้วดึงค่าด้วยฟังก์ชันเล็ก ๆ
SS=$(mariadb -h "$H" -u"$DB_USER" -p"$DB_PASS" -e "SHOW SLAVE STATUS\G" 2>/dev/null)
fld(){ printf '%s\n' "$SS" | grep -m1 -E "[[:space:]]$1:" | sed "s/.*$1:[[:space:]]*//" | tr -d ' \r'; }
IO=$(fld Slave_IO_Running); SQLT=$(fld Slave_SQL_Running); SEC=$(fld Seconds_Behind_Master)
[ "$SEC" = "NULL" ] && SEC=""

The value to watch is Seconds_Behind_Master. It can return NULL as text, which means the SQL thread is not running. If I compare it numerically without cleaning it up first, bash interprets it as zero and reports that everything is fine, which is about as wrong as an answer can get.

Catching People Writing Directly to the Replica

Another cause of replication failure is someone connecting directly to the replica and writing to it. Usually it is unintentional: an old program whose config points to the wrong machine, or someone who leaves a database management tool open and accidentally hits save. When a row on the replica does not match what the master sends, the SQL thread stops with a duplicate key error.

-- หา foreign writer: ใครกำลังยิงคำสั่งเขียนอยู่บนเครื่องนี้ นอกจาก replication
SELECT GROUP_CONCAT(DISTINCT CONCAT(SUBSTRING_INDEX(host,':',1),'/',user) SEPARATOR ', ')
FROM information_schema.processlist
WHERE db IN ('core_db','ref_db')
  AND user <> 'system user'
  AND SUBSTRING_INDEX(host,':',1) NOT IN ('10.10.10.7','10.10.10.2')
  AND command IN ('Query','Execute')
  AND info REGEXP '^[[:space:]]*(INSERT|UPDATE|DELETE|REPLACE|LOAD|ALTER|DROP|TRUNCATE|CREATE)';

The resulting text must always have its characters sanitized before I put it into my SQL, because it comes from outside.

# ล้าง quote กับ backslash + ตัดความยาว ก่อนเอาไปประกอบเป็น SQL
san(){ tr -d "'\"\\\\" | tr '\n' ' ' | sed 's/^[[:space:]]*//; s/[[:space:]]*$//' | cut -c1-240; }

Store Results in One Table and Let the Website Read It

I do not have the script send alerts itself. I have it write the latest status into a table in the application, and the website reads that table. The advantage is that anyone with the website open can see it immediately without waiting for someone to send them a message.

CREATE TABLE replica_watch (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  host            VARCHAR(20)  NOT NULL UNIQUE,
  role            VARCHAR(12)  NOT NULL DEFAULT 'slave',
  io_running      VARCHAR(8)   NULL,
  sql_running     VARCHAR(8)   NULL,
  secs_behind     INT          NULL,
  lag_days        INT          NULL,
  max_biz_date    DATE         NULL,
  foreign_writers VARCHAR(255) NULL,
  status          VARCHAR(16)  NOT NULL DEFAULT 'ok',  -- ok|stopped|lagging|foreign-write|unreachable
  detail          VARCHAR(255) NULL,
  checked_at      DATETIME     NULL
) ENGINE=InnoDB;

The status logic is ordered from most to least severe. The first matching condition wins.

if [ "$IO" != "Yes" ] || [ "$SQLT" != "Yes" ]; then
  ST=stopped;  DT="replication หยุด (IO=$IO SQL=$SQLT) — ตรวจสาเหตุก่อนสั่งเดินต่อ"
  [ -n "$ERR" ] && DT="$DT | err: $ERR"
elif [ -n "$FW" ]; then
  ST=foreign-write; DT="พบการเขียนลงเครื่องนี้โดยตรงจาก: $FW"
elif [ -n "$SEC" ] && [ "$SEC" -gt "$LAG_SECS_WARN" ] 2>/dev/null; then
  ST=lagging;  DT="ตามหลัง master $(( SEC / 60 )) นาที — ข้อมูลยังไม่สด"
elif [ -n "$LAG" ] && [ "$LAG" -gt "$LAG_DAYS_WARN" ] 2>/dev/null; then
  ST=lagging;  DT="ข้อมูลธุรกิจเก่ากว่า master $LAG วัน (ล่าสุด $DCH vs $MASTER_DCH)"
else
  ST=ok;       DT="ปกติ (delay ${SEC:-?}s, ห่าง ${LAG:-?} วัน)"
fi

Monitoring the Monitor

The question I always need to ask when building a monitoring system is: if the monitor itself dies, who will know? My answer is to have the consumer of its results check their age as well. If the table has not been updated within the allowed time, cron has died or the machine running it has a problem.

// อ่านผลจากตาราง คืน null = ปกติ ; คืน array = ให้ layout ขึ้นแถบเตือนบนหัวเว็บ
class ReplicaAlert
{
    public const SOURCE_HOST = '10.10.10.3';   // เครื่องที่รายงานอ่านจริง
    private const STALE_MIN  = 130;            // cron รายชั่วโมง เผื่อพลาดได้ 1 รอบ

    public static function check(): ?array
    {
        try {
            $r = DB::table('replica_watch')->where('host', self::SOURCE_HOST)->first();
        } catch (\Throwable $e) {
            return null;                        // ฐานยังไม่พร้อม อย่าให้ตัวเฝ้าทำเว็บล่ม
        }
        if (!$r) return null;

        $checked = $r->checked_at ? strtotime((string) $r->checked_at) : null;
        if ($checked && (time() - $checked) > self::STALE_MIN * 60) {
            return ['level' => 'warn',
                    'title' => 'ตัวเฝ้า replication ไม่ได้อัปเดต',
                    'msg'   => 'ตรวจล่าสุด ' . $r->checked_at . ' — ไปดู cron ที่เครื่องรายงาน'];
        }
        if (($r->status ?? 'ok') !== 'ok') {
            return ['level' => 'err',
                    'title' => 'แหล่งข้อมูลผิดปกติ: ' . $r->status,
                    'msg'   => trim((string) $r->detail) . ' · ตรวจล่าสุด ' . $r->checked_at];
        }
        return null;
    }
}

The 130-minute value comes from cron running every hour, allowing one missed run before raising a warning. A tighter limit would cause false alarms when the machine is under heavy load and cron runs a little late. If it runs more frequently, adjust it as needed. The principle is the same: always allow one missed run.

# /etc/cron.d/replica-watch
0 * * * * appops /opt/app/ops/replica_watch.sh >> /var/log/replica_watch.log 2>&1

What This System Can and Cannot Tell Me

1. First, recognize that Seconds_Behind_Master and business data freshness are different things, and collect both.
2. Create a read-only account for the monitor, and always keep the password outside the script and the repo.
3. Do not use -N when reading SHOW SLAVE STATUS with \G, and always clean up NULL before comparing numbers.
4. Set read_only on the replica first, then use the processlist to help identify anyone writing directly to it.
5. Have the script write status into the application's own table, without sending alerts or fixing the system itself.
6. Have the reader check the age of the results as well, and always wrap it in try/catch so the monitor cannot take the website down.
7. Display a warning bar at the top of every page. Do not hide it on an admin page nobody opens.

That last point matters more than I expected. At first, I built a very nice separate status page, complete with tables and green and red indicators, and nobody opened it for two whole months. After I moved it to a red bar at the top of every page, someone asked me about it within twenty minutes on the day replication actually stopped. That was what I wanted from the start.

There are things this system cannot tell me, too. It does not know whether the replicated data is correct, only whether it has arrived. If the source wrote incorrect data in the first place, this monitor will report that everything is fine. Anyone who needs that level of checking will need another layer of per-table checksum comparisons, which is another topic just as long as this one. Once I have that working reliably, I will write about it. If I have missed anything in the explanation or made a typo, I will come back and fix it later.