We found out during a failover rehearsal, which is the second-worst time to find out. The plan was to promote a read replica, replay traffic against it for an hour, and fail back. The rehearsal died in minute nine when a customer called: their account showed orders on the old primary that did not exist on the new one, and — the detail that ruined my afternoon — the new one contained 214 order rows that existed nowhere else. Every replication metric had been green for months. Seconds behind source: zero. IO and SQL threads: running. No errors in the log. Replication health and data identity are different claims, and MySQL only reports the first one. After the rollback we ran pt-table-checksum across the fleet and found drift in eleven tables on three replicas, most of it months old. This is the workflow I wish we had run quarterly instead of once, in a panic: how replicas diverge while every dashboard smiles, how the checksum tool proves it chunk by chunk without melting the primary, and how to repair diverged data without rebuilding the replica from a backup.
How do replicas drift while every metric stays green?
Replication copies events, and anything that changes data without producing the right events — or that changes the replica directly — creates divergence that no thread status will ever report. The causes, ranked by how often they have bitten me or people I have helped debug. Direct writes to the replica are the classic: read_only was off during a migration, someone ran a manual fix against the wrong endpoint, a schema change tool connected to the replica "just for a second." Non-deterministic statements under statement-based binlogging are the insidious one — UPDATE ... LIMIT without ORDER BY, NOW()-dependent logic mixed with triggers, anything whose result depends on plan or timing — and the transition pain in replication filter setups is a cousin: filtered-out statements apply on the primary, never ship, and leave the replica holding history the primary discarded. Crashes contribute too: a replica that comes back from an unclean stop can replay or skip transactions in ways that converge for thousands of tables and diverge for three, which is why replica crash safety settings are drift prevention, not just durability tuning. Errant transactions — writes that happened on a replica with the primary's GTID set nowhere in its history — are the drift that actively breaks failover later, and the GTID errant transaction notes cover that specific knife. The common thread: none of these touch replication's health signals, because replication only knows whether events apply, not whether the resulting data matches.
How does pt-table-checksum prove divergence without melting the primary?
It walks each table in chunks — a thousand or so rows at a time, bounded by a unique index — computes a CRC over each chunk with a single statement, and writes the results into a checksum table whose writes replicate to every replica like ordinary application traffic. Each replica replays the same per-chunk checksum statements against its own copy of the data, so after the run you compare primary CRCs against replica CRCs chunk by chunk: a mismatch pinpoints exactly which slice of which table diverged. The replication-based design is what makes it safe on a live fleet — no parallel connections pounding replicas, no bespoke comparison jobs to babysit — and it is also the source of its operational rules. It sets binlog_format=STATEMENT for its own session so checksum statements replicate as statements, which is why it refuses to run if your replication filters or binlog settings would mangle that. It throttles itself: it pauses whenever replica lag exceeds --max-lag (one second by default) and watches for load, so a checksum of a terabyte table is a days-long background hum rather than an incident. The minimal invocation I run from a utility host:
-- dry-run first: reports what it would checksum and what it will skip
pt-table-checksum --host=primary.internal --user=checksum --password=... --databases=orders --recursion-method=processlist --max-lag=1 --chunk-time=0.5 --no-check-binlog-format --dry-run
-- the real run; results land in percona.checksums on every server
pt-table-checksum --host=primary.internal --user=checksum --password=... --databases=orders --recursion-method=processlist --max-lag=1 --chunk-time=0.5 --no-check-binlog-format
-- afterwards, on each replica: which chunks disagree?
SELECT db, tbl, chunk, this_cnt, master_cnt, this_crc, master_crc
FROM percona.checksums
WHERE (this_crc <> master_crc OR this_cnt <> master_cnt)
AND master_crc IS NOT NULL
ORDER BY db, tbl, chunk;What will the tool refuse to tell you, and what will it get wrong?
Tables without a unique key cannot be chunked reliably and get skipped — the tool tells you, but only in its output, not in any metric, so a fleet with keyless legacy tables has blind spots you must enumerate yourself. FLOAT and DOUBLE columns are the classic false-positive factory: different rounding paths between servers make checksums differ on identical-looking data, and the tool warns about float columns for exactly this reason; treat any diff confined to float-bearing chunks with suspicion before treating it as drift. Timestamp columns under inconsistent time_zone settings can produce the same effect. The checksum table itself is a small write workload, so on a primary already at the edge of write capacity, schedule the run in a quiet window even though the tool throttles. And the comparison query above needs care: chunks that exist on the primary but never reached a lagging replica show master_crc with NULL this_crc until replication catches up, so run the diff query only after lag is zero, or you will page yourself over arithmetic that just has not arrived yet.
How do you repair drift without rebuilding the replica?
pt-table-sync computes the row-level differences from the checksum output and emits the statements that would bring the replica in line — which you read before you execute, every time. The repair runs on the primary side of the data flow in the tool's default mode: it makes the replica match the primary, so it must be pointed at the primary with the replica as the target, and it requires the same unique-key chunking that checksum required. My rule is that --print output gets reviewed by a human who knows the application before --execute is ever typed, because "make B match A" is a destructive claim when the drift direction turns out to be backwards — our 214 phantom rows were legitimate orders inserted directly on the replica during an earlier migration mistake, and syncing them away would have deleted real customer data. The fix was to export those rows first, then sync, then re-apply the export through the primary. For drift beyond a few thousand rows, or drift in tables that churn constantly, rebuilding the one table from a logical dump is often cheaper than a million row-by-row sync statements; for drift beyond a few tables, rebuild the replica from a fresh backup and fix the process that let it drift, because widespread divergence means you cannot trust the replica's history anyway.
How do you keep drift from coming back?
Checksum on a schedule — quarterly at minimum, monthly for anything money-bearing — with the diff query wired into alerting so the run is useless-to-nobody; a checksum nobody reads is a ritual, not a control. Close the direct-write hole with read_only plus super_read_only on every replica, and audit SUPER grants on replica hosts twice a year, because every drift incident I have personally investigated eventually traced to a human with credentials and a reasonable-sounding reason. Keep binlog_format=ROW unless you have a specific, documented exception, and grep the slow log and application code for UPDATE/DELETE with LIMIT and no ORDER BY — those are drift seeds under any format ambiguity. After any replica crash, treat the first checksum run afterward as mandatory rather than routine. And when you promote a replica, run the checksum diff against the promotion candidate beforehand; the replication lag diagnosis playbook tells you the candidate is current, but only a checksum tells you it is identical. Drift is not a replication bug to be fixed once — it is entropy, and the checksum schedule is the thermostat.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — lag as a guardrail rather than a truth claim, replica health separated from replica correctness, scheduled checksum diffs surfaced as alerts instead of cron output — are exactly what the MySQL work is designed to surface, so silent divergence shows up as a finding, not a failover-day surprise. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side today, and the rest of these MySQL field notes live on the blog.