Chapter 14 · Asynchronous Replication, GTIDs, Topologies, and Operational Safety
Source/Replica Architecture, Binary Log Flow, Relay Logs, and Replication Threads
Trace one committed ServiceHub transaction from the source binary log through the replica receiver, relay log, and applier workers; then prove why a connected replica can still be stale or incorrect.
Learning outcomes
ServiceHub now needs a second MySQL server for read capacity and recovery options. The dangerous beginner assumption is that replication means “the two servers are the same.” Asynchronous replication is a pipeline: a transaction commits on the source, becomes binary-log events, crosses a network connection, is written to a relay log, and is later applied by one or more replica workers. Every stage can be healthy, stopped, delayed, or wrong independently.
Trace a committed transaction from the source binary log through receiver, relay log, coordinator/worker, and replica data state.
Configure a disposable GTID-based source/replica pair with current source/replica terminology and least-privilege replication credentials.
Read SHOW REPLICA STATUS and Performance Schema replication tables without treating one “running” flag as proof of correctness.
Create a controlled state where the receiver is connected but the applier is stopped, then diagnose the resulting lag.
Define operational acceptance criteria that separate connectivity, freshness, and data correctness.
The replication pipeline: commit first, copy later
In asynchronous replication, the application transaction commits on the source without waiting for a normal asynchronous replica to apply it. MySQL writes the source transaction to its binary log. When a replica connects, the source uses a binary-log dump thread to send required events. The replica's receiver thread—historically called the I/O thread—copies those events into local relay logs. The applier—historically the SQL thread, or a coordinator plus parallel workers—executes the transactions on the replica.
| Stage | Primary evidence | What healthy means | What it does not prove |
|---|---|---|---|
| Source commit | binary log / GTID state | transaction durably entered the source history | any replica has received it |
| Receiver | replication_connection_status | replica is connected and queueing transactions | transactions are applied |
| Relay log | relay-log files / metadata | events are stored locally for application | SQL worker is keeping up |
| Applier | replication_applier_status(_by_worker) | transactions are being executed | data is semantically identical if drift already exists |
| Application read | business query/invariant | this query sees expected state | all tables/GTIDs are correct |
This separation is the central operational model for the chapter. “Replica connected” is an availability statement about one edge of the pipeline, not a consistency proof.
Build a disposable two-node topology
The mandatory topology uses only free MySQL Community Server instances. Containers are convenient because the destructive exercises later in the chapter can be reset by removing volumes. If you prefer two native local installations, use two distinct data directories, ports, and unique server_id values and reproduce the same server variables shown below.
The passwords in this lab are intentionally disposable and local. Do not reuse them, commit production credentials, or copy this open host pattern into a real network.
services: mysql-source: image: mysql:8.4 container_name: servicehub-mysql-source environment: MYSQL_ROOT_PASSWORD: LabRootOnly_2026! ports: ["3314:3306"] command: - --server-id=101 - --log-bin=mysql-bin - --binlog-format=ROW - --gtid-mode=ON - --enforce-gtid-consistency=ON - --binlog-expire-logs-seconds=604800 volumes: - source-data:/var/lib/mysql mysql-replica: image: mysql:8.4 container_name: servicehub-mysql-replica environment: MYSQL_ROOT_PASSWORD: LabRootOnly_2026! ports: ["3315:3306"] command: - --server-id=102 - --log-bin=mysql-bin - --relay-log=relay-bin - --log-replica-updates=ON - --gtid-mode=ON - --enforce-gtid-consistency=ON - --relay-log-recovery=ON - --skip-replica-start=ON - --read-only=ON - --super-read-only=ON volumes: - replica-data:/var/lib/mysqlvolumes: source-data: replica-data:# Bash / macOS / Linux / Git Bash# Save the YAML as compose.yaml in an empty lab directory.docker compose up -ddocker compose psdocker exec -it servicehub-mysql-source mysql -uroot -pdocker exec -it servicehub-mysql-replica mysql -uroot -p# PowerShell uses the same docker commands:docker compose up -ddocker compose psdocker exec -it servicehub-mysql-source mysql -uroot -pdocker exec -it servicehub-mysql-replica mysql -uroot -pSELECT @@hostname, @@server_id, @@version, @@global.log_bin, @@global.binlog_format, @@global.gtid_mode, @@global.enforce_gtid_consistency;CREATE USER IF NOT EXISTS 'servicehub_repl'@'%' IDENTIFIED BY 'LabReplOnly_2026!';GRANT REPLICATION SLAVE ON *.* TO 'servicehub_repl'@'%';CREATE DATABASE IF NOT EXISTS servicehub_repl_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE IF NOT EXISTS servicehub_repl_lab.work_orders ( work_order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, site_code VARCHAR(20) NOT NULL, status ENUM('OPEN','IN_PROGRESS','DONE','CANCELLED') NOT NULL DEFAULT 'OPEN', summary VARCHAR(180) NOT NULL, priority TINYINT UNSIGNED NOT NULL DEFAULT 3, opened_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY ix_work_orders_status_opened(status, opened_at)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_repl_lab.replication_markers ( marker_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, marker_name VARCHAR(100) NOT NULL UNIQUE, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;INSERT IGNORE INTO servicehub_repl_lab.replication_markers(marker_name)VALUES ('CH14_SOURCE_BASELINE');SHOW BINARY LOG STATUS;SELECT @@GLOBAL.gtid_executed AS source_gtids;SELECT @@hostname, @@server_id, @@version, @@global.gtid_mode, @@global.enforce_gtid_consistency, @@global.read_only, @@global.super_read_only;STOP REPLICA;CHANGE REPLICATION SOURCE TO SOURCE_HOST='mysql-source', SOURCE_PORT=3306, SOURCE_USER='servicehub_repl', SOURCE_PASSWORD='LabReplOnly_2026!', SOURCE_AUTO_POSITION=1, GET_SOURCE_PUBLIC_KEY=1;START REPLICA;SHOW REPLICA STATUS\GBecause the default MySQL 8.4 authentication policy uses caching_sha2_password, this isolated non-TLS container lab uses GET_SOURCE_PUBLIC_KEY=1 for RSA-protected password exchange. A production channel should normally use TLS and a deliberately scoped source account rather than relying on this lab shortcut.
Prove the full path with one marker transaction
INSERT INTO servicehub_repl_lab.work_orders(site_code,status,summary,priority)VALUES ('BAKU-01','OPEN','Inspect compressor vibration',2);SET @source_marker = CONCAT('FLOW_', UUID());INSERT INTO servicehub_repl_lab.replication_markers(marker_name)VALUES (@source_marker);SELECT @source_marker AS marker_written;SELECT @@GLOBAL.gtid_executed AS source_gtids;SHOW BINARY LOG STATUS;SELECT COUNT(*) AS replicated_ordersFROM servicehub_repl_lab.work_orders;SELECT marker_name, created_atFROM servicehub_repl_lab.replication_markersORDER BY marker_id DESC LIMIT 5;SHOW REPLICA STATUS\GIf the marker appears, you have evidence that this transaction reached the replica. You still have not proven there is no old drift in another table. Replication moves changes; it is not a continuous checksum of all existing data.
Failure lab: connected receiver, stopped applier
This is the simplest way to make the “connected is not caught up” distinction visible without breaking the network.
STOP REPLICA SQL_THREAD;SHOW REPLICA STATUS\GINSERT INTO servicehub_repl_lab.work_orders(site_code,status,summary,priority)VALUES ('BAKU-02','OPEN','Replace pump seal',1), ('BAKU-01','OPEN','Calibrate pressure sensor',2);SELECT COUNT(*) AS source_countFROM servicehub_repl_lab.work_orders;SELECT CHANNEL_NAME, SERVICE_STATE, SOURCE_UUID, LAST_QUEUED_TRANSACTION, QUEUEING_TRANSACTIONFROM performance_schema.replication_connection_status;SELECT CHANNEL_NAME, SERVICE_STATE, REMAINING_DELAY, COUNT_TRANSACTIONS_RETRIESFROM performance_schema.replication_applier_status;SELECT CHANNEL_NAME, WORKER_ID, SERVICE_STATE, LAST_APPLIED_TRANSACTION, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGEFROM performance_schema.replication_applier_status_by_workerORDER BY CHANNEL_NAME, WORKER_ID;SELECT COUNT(*) AS stale_replica_countFROM servicehub_repl_lab.work_orders;START REPLICA SQL_THREAD;-- Re-run until the applier has caught up.SHOW REPLICA STATUS\GSELECT COUNT(*) AS replica_count_after_catchupFROM servicehub_repl_lab.work_orders;During the pause, the receiver can remain active and continue writing relay logs. This topology is connected yet stale. If a read router sends freshness-sensitive traffic there, the application can observe old data even though no replication connection error exists.
What should you monitor?
SHOW REPLICA STATUS remains useful for an operator, but Performance Schema tables are easier to query and correlate. In current MySQL 8.4, replication_connection_status describes the receiver and queued transactions, while replication_applier_status and replication_applier_status_by_worker expose applier state, delay, retries, and per-worker errors. For lag, commit timestamps and transaction-processing timestamps are more informative than blindly relying on one seconds-behind field.
SELECT CHANNEL_NAME, SERVICE_STATE, SOURCE_UUID, LAST_QUEUED_TRANSACTION, QUEUEING_TRANSACTIONFROM performance_schema.replication_connection_status;SELECT CHANNEL_NAME, SERVICE_STATE, REMAINING_DELAY, COUNT_TRANSACTIONS_RETRIESFROM performance_schema.replication_applier_status;SELECT CHANNEL_NAME, WORKER_ID, SERVICE_STATE, LAST_APPLIED_TRANSACTION, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGEFROM performance_schema.replication_applier_status_by_workerORDER BY CHANNEL_NAME, WORKER_ID;Production judgment
Asynchronous replication can improve read capacity, geographic copies, maintenance flexibility, and recovery options, but it does not by itself guarantee zero data loss or automatic failover. A source can accept a commit that has not yet reached a replica. Promotion therefore requires fencing the old writer, choosing a sufficiently caught-up and correct candidate, and validating application state after role change.
Ask: Can the replica receive? Can it apply and keep up? Is its data correct for the workload you intend to serve? Those are different tests.
Knowledge check
- Which thread writes incoming source events to the relay log?
- Why can Replica_IO_Running=Yes coexist with stale query results?
- What does a successful marker query prove?
- Why must server_id values be unique?
- What should happen before promoting a replica to writer?
Reveal answers
- The replica receiver (I/O) thread receives binary-log events from the source and stores them in relay logs.
- The receiver can be connected while the applier is stopped or behind, so newly received events have not changed replica tables yet.
- It proves that marker transaction reached and was applied on this replica; it does not prove all historical data is drift-free.
- MySQL uses server identity in replication topology behavior; duplicate identities make topology state ambiguous and unsafe.
- Fence the old writer, assess catch-up/correctness and outstanding transactions, then promote and perform post-promotion validation.