Seconds_Behind_Master is not a replica health check

The replica you would restore from is only worth something if it is current, consistent and out of an attacker's reach. Most monitoring checks one number, replication lag, and that number can read zero on a replica that stopped receiving changes an hour ago. Here is how to check a MySQL or MariaDB replica so the answer means something.

The problem with the lag figure

Seconds_Behind_Master (or Seconds_Behind_Source on newer MySQL) is computed by the replica's SQL thread: the difference between the timestamp of the event it is applying and the current time. It knows nothing about events it has not received.

So if the I/O thread that fetches changes from the primary is stuck on a half open TCP connection, the SQL thread finishes everything it already has and reports 0. The replica looks perfect. Meanwhile it has stopped moving. The figure also reads NULL when replication is stopped, and many checks treat NULL as "no lag".

Check one: both threads, and the error fields

The first test is boring and catches most problems:

SHOW SLAVE STATUS\G      -- or SHOW REPLICA STATUS\G

Require Slave_IO_Running: Yes and Slave_SQL_Running: Yes (the Replica_ names on newer MySQL), and require Last_IO_Error and Last_SQL_Error to be empty. Connecting on the I/O thread is not "running", it is a replica that cannot reach its source.

Also set a short slave_net_timeout (or replica_net_timeout) together with MASTER_HEARTBEAT_PERIOD on the replication channel. The primary then sends heartbeats when idle, and a dead connection is detected in seconds instead of waiting out the default network timeout.

Check two: compare positions, not timestamps

With GTID replication, every committed transaction has an identifier, and you can compare where the primary is with where the replica is directly.

On MariaDB:

-- on the primary
SELECT @@gtid_binlog_pos;
-- on the replica
SELECT @@gtid_slave_pos;

On MySQL, compare @@gtid_executed on both sides, or ask the replica directly whether it has caught up to a set:

SELECT WAIT_FOR_EXECUTED_GTID_SET('<primary gtid_executed>', 5);

When the primary's position keeps advancing and the replica's does not, you have a stalled replica, whatever the lag figure says. This comparison is also how you finish a reseed or a cutover: both sides must agree on the same transaction before anyone calls it done.

Check three: a heartbeat you write yourself

The most honest lag measurement is a row the primary updates with its own clock, read back on the replica. pt-heartbeat from Percona Toolkit does exactly that:

# on the primary, as a service
pt-heartbeat --update --database percona --create-table --daemonize
# on the replica, from monitoring
pt-heartbeat --check --database percona --master-server-id <id>

Because the measurement travels through replication itself, a broken I/O thread shows up as steadily growing lag, which is what you wanted to see in the first place. A test write on the primary followed by a read on the replica gives the same proof by hand, and it is the quickest acceptance check for a new replica.

Check four: is the data actually the same?

Replication can run happily on top of data that has drifted, after a statement that behaves differently on each side, a write made to the replica by mistake, or a table type that does not replicate safely. pt-table-checksum runs chunked checksums on the primary and lets replication carry them to the replica, then reports the tables that differ:

pt-table-checksum --replicate=percona.checksums --no-check-binlog-format h=<primary>

Run it on a schedule that suits your write volume, off peak. Set read_only = ON on every replica (and super_read_only = ON on MySQL) so that nothing, including an administrator's tool, can write to it by accident.

Check five: which way the connection goes

This one is not about lag at all. A replica is your recovery copy, so it should be the side that reaches out. The replica opens the replication connection to the primary; the primary should have no network path to the replica at all.

Test it rather than assume it. From the primary:

nc -vz -w 3 <replica-address> 3306

That should time out. If it connects, an attacker who owns your production server can also reach the copy you plan to recover from. We enforce the direction on the firewall appliances, not on the hosts, and prove it on every build.

Alert on state, not on every sample

Collect all of the above into one state per replica: OK, LAGGING (above an agreed threshold), or BROKEN (a thread down, an error set, or the GTID position frozen while the primary moves). Then alert only when the state changes, including the change back to OK.

state=$(replica_state)          # your check, returns OK, LAGGING or BROKEN
last=$(cat /var/lib/replcheck/state 2>/dev/null)
if [ "$state" != "$last" ]; then
  notify "replica: $last -> $state"
  echo "$state" > /var/lib/replcheck/state
fi

Run it every few minutes from a timer. Engineers receive one message when something changes and one when it recovers, not a stream they learn to ignore. The real risk with replicas is not lag; it is a replica that stopped weeks ago and nobody noticed.

Reseeding without guesswork

When a replica does need rebuilding, GTID removes the hunt for binary log coordinates. A consistent logical dump with the position embedded, or a physical backup with Mariabackup or Percona XtraBackup, gives you the exact transaction to start from. On MariaDB the replica side reduces to:

SET GLOBAL gtid_slave_pos = '<position from the dump or backup>';
CHANGE MASTER TO MASTER_HOST='<primary>', MASTER_USER='repl',
  MASTER_PASSWORD='<secret>', MASTER_USE_GTID=slave_pos;
START SLAVE;

Then run checks one to three again and do not sign off until both sides report the same GTID after a test write.

Your checklist

• Both replication threads running, error fields empty, a heartbeat period set on the channel.
• Primary and replica GTID positions compared, not just the lag figure.
• A heartbeat row or test write proving changes actually arrive.
• Scheduled checksums, and read_only on every replica.
• The primary cannot open a connection to the replica, proven from the primary.
• Alerts on state changes, so a stalled replica is a message within minutes, not a discovery on the day you need it.

If you would like a second pair of eyes on your replicas, that is the kind of work we enjoy.

Talk to an engineer

Related: Database replication and off-site replicas · Clustered databases with Percona XtraDB Cluster · Managed datacenter migrations, rehearsed