MySQL / Concurrency, performance, and operations / Lesson 18

Replication, GTIDs, and operational readiness

Separate replication progress, availability decisions, and recovery responsibilities.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Interpret replication channels and GTID set relationships.
Plan lag-aware reads, failover fencing, upgrades, and observability.

Replication is an asynchronous pipeline

In a typical asynchronous MySQL topology, a source writes binary-log events, a replica receiver fetches them into relay logs, and applier workers execute them. Receiving an event does not mean applying it. A source commit can succeed before a replica reflects the change, so sending a post-purchase read to that replica may show stale state. Design read routing around the required consistency rather than assuming all endpoints are interchangeable.

GTIDs name transactions, not row contents

MySQL GTIDs identify originating server transactions, commonly using a server UUID and sequence interval. Set functions help compare known progress without touching a replication channel. The following UUID and intervals are synthetic teaching values. MariaDB GTIDs have different semantics and formats, so do not interchange their sets or configuration commands with MySQL GTIDs. A matching transaction set is useful evidence but is not a checksum proving identical table contents.

sql Ask whether required transactions are included

SELECT GTID_SUBSET(
 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-3',
 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-5') AS contained;

Expected output

contained
1

The result is 1 because transactions 1, 2, and 3 are all members of 1 through 5. This is pure set reasoning over literals and does not claim that any configured server has executed them. In operations, obtain the intended source and replica sets through authorized diagnostics.

Compute missing progress precisely

GTID_SUBTRACT returns members of the first set absent from the second. If required progress is 1 through 5 and applied progress is only 1 through 3, transactions 4 and 5 remain unaccounted for in that comparison. Sets can contain several UUIDs and disjoint intervals, so do not compare them as ordinary string prefixes or use the largest number alone as proof of catch-up.

sql Find the missing interval

SELECT GTID_SUBTRACT(
 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-5',
 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-3') AS missing;

Expected output

missing
aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:4-5

The synthetic result is aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:4-5. It identifies a required transaction interval, not elapsed seconds or missing row count. Two transactions may change radically different amounts of data and therefore require different apply times.

Monitor receiver and applier separately

SHOW REPLICA STATUS exposes channel state, receiver and SQL/applier status, errors, positions, and GTID progress. Seconds_Behind_Source is limited evidence: it may be NULL when progress is unknown and zero does not prove end-to-end freshness. Inspect worker errors, source connectivity, queue growth, and application-level freshness checks. Diagnostics need appropriate privileges and may reveal infrastructure identifiers, so avoid publishing raw operational output.

sql Read-only operational inspection commands

SHOW REPLICA STATUS;
SELECT @@GLOBAL.gtid_executed AS executed_transactions;
SHOW BINARY LOG STATUS;

Use these only on an authorized diagnostic connection. A standalone training server can have no replica channel, and output depends on topology and binary logging. MySQL 8.4 uses SHOW BINARY LOG STATUS here; older blog examples using removed or renamed administrative statements should be checked against the target manual.

Failover requires fencing and rehearsal

Before promoting a candidate, establish its actual applied progress and the acceptable loss boundary, fence the old writer, and route clients so two writers cannot diverge. GTID auto-positioning helps locate transactions but does not make an unsafe promotion safe. Retain independent backups and practice rejoining or rebuilding old members. Upgrades require supported paths, connector checks, rollback constraints, and rehearsed restore plans; a healthy replication thread alone proves none of these.

Common errors and fixes

A read after purchase returns an older order state.

Why it happens: The application routed the read to an asynchronous replica before apply completed.
How to fix it: Use source reads or an explicitly bounded progress-aware routing policy for that workflow.
Prevent it next time: Declare read-after-write requirements for each user operation.

A dashboard declares healthy replication from zero lag alone.

Why it happens: One lag field was treated as a complete source-to-application health test.
How to fix it: Inspect receiver, applier, errors, progress sets, and freshness probes together.
Prevent it next time: Alert on unknown state and stalled progress, not merely positive seconds.

Failover creates competing writers.

Why it happens: The old source remained reachable for writes while a replica was promoted.
How to fix it: Follow the incident runbook to fence writes and assess divergence before reconciliation.
Prevent it next time: Rehearse fencing, client routing, and promotion authority together.

Check your understanding

1. Does GTID_SUBSET(required,applied)=1 prove every table is identical?

Show worked solution

No. It proves the required transaction identifiers are included in the compared set. Filters, unauthorized writes, corruption, or operational history may still affect data. Use appropriate consistency validation and topology knowledge; a transaction progress check is not a universal integrity checksum.

2. A candidate is two transactions behind. Can the lost data be inferred from that number?