Replication Heartbeat Monitoring

Implement external health checks to monitor node synchronization status.

In this lesson, you will learn how to implement an external "heartbeat" mechanism to accurately track replication lag and ensure your secondary nodes are truly synchronized with the primary.

The Problem with Default Latency Metrics

When monitoring asynchronous replication, relying solely on the primary's binary log position can be misleading. If the primary stops receiving writes, the replication lag might appear to be zero, even if the network connection between the primary and replica is completely dead. This "false positive" can lead to stale data being served to your application. To solve this, we implement a heartbeat table.

Implementing the Heartbeat

A heartbeat mechanism works by having a monitoring script or an automated event on the primary node update a specific row in a dedicated table at a set interval (e.g., every second).

Create a Heartbeat Table: On the primary, create a small, memory-optimized table (e.g., InnoDB or MEMORY engine):

CREATE TABLE heartbeat (
    id INT PRIMARY KEY,
    ts TIMESTAMP(6) NOT NULL
);
INSERT INTO heartbeat VALUES (1, NOW(6));

The Update Loop: An external process or a GLOBAL EVENT on the primary runs periodically:
```
UPDATE heartbeat SET ts = NOW(6) WHERE id = 1;
```
External Monitoring: Your monitoring tool (or a script running on the replica) polls this table. It compares the ts value from the replica’s copy of the table against the current system time. The difference represents the true replication lag.

Why this is superior

By using a timestamp-based heartbeat, you are not just checking if a binary log packet was received; you are verifying that the replica has actually applied the transaction containing the updated timestamp. If the time difference exceeds your defined threshold (e.g., 5 seconds), your monitoring system can trigger an alert or shift traffic away from that replica. This provides a transparent, end-to-end view of the data pipeline that default metrics often obscure.

Key Takeaway: A heartbeat table provides a reliable, application-level verification of data freshness that bypasses the limitations of passive binary log tracking.