From Crashing Databases to Zero Data Loss: MySQL Replication Evolution

Why the real problem is not just replication lag

Many teams upgrade MySQL replication after a serious incident, not because of a planned roadmap. Typical failure scenarios include a full‑disk crash on the primary, delayed replicas, manual binlog position hunting, and data inconsistencies such as "payment succeeded but no order".

Understanding the three core replication links

Transaction commit chain : InnoDB writes to the redo log, marks prepare, then the server writes the binlog and finally commits.

Replication transport chain : Source (binlog dump thread) → Replica I/O Thread (pulls binlog into relay log) → Replica SQL/Worker Thread (executes events).

Replica replay chain : Event parsing → dependency analysis → worker dispatch → InnoDB execution → log flush → commit order enforcement.

Only by linking these three can you locate where data loss or bottlenecks occur.

From file/position to GTID

File/position replication ( (binlog_file, binlog_pos)) works in small labs but fails in high‑concurrency, multi‑master, or cross‑datacenter environments. GTID ( source_uuid:transaction_id) gives each transaction a globally unique identifier, turning the problem from "which file/offset" to "which transactions have been executed". source_uuid:transaction_id GTID enables automatic positioning ( MASTER_AUTO_POSITION=1) and reliable failover because the new primary can be chosen by comparing Executed_Gtid_Set sets.

Semi‑synchronous replication (AFTER_SYNC)

Asynchronous replication leaves a window where the primary commits but the replica has not received the binlog, causing permanent loss if the primary crashes. Semi‑sync guarantees that at least N replicas have received the binlog before acknowledging the client.

The crucial distinction is between AFTER_COMMIT (ack after primary commit) and AFTER_SYNC (ack after replica receipt). AFTER_SYNC is the practical way to approach “zero loss”.

Parallel replication in MySQL 8.0

Single‑threaded replay is the main bottleneck. MySQL 8.0 offers three parallelism modes:

DATABASE : parallel only when transactions touch different databases.

LOGICAL_CLOCK : uses group commit information to parallelize more transactions.

WRITESET : extracts row‑level write sets (XXHASH64) to schedule truly independent transactions. This is the most effective for single‑database workloads.

Parallelism is limited by transaction dependencies, hotspot conflicts, large transactions, and CPU/I/O capacity.

Real‑world case: Order center latency reduction

A high‑traffic order service suffered 18 s replica lag during nightly batch jobs, causing users to see "payment succeeded" while the order was missing. The migration steps were:

Set binlog_format=ROW on the primary.

Enable GTID on all servers.

Switch replication to semi‑sync AFTER_SYNC.

Enable parallel apply with replica_parallel_workers=16 and replica_parallel_type=WRITESET.

Break large batch jobs into 500‑row chunks.

Introduce a 3‑second write‑behind‑read‑master strategy for critical order queries.

Result: steady replica lag of 100‑300 ms, nightly peak reduced to 700 ms‑1.2 s, and RPO improved from “uncontrollable” to near zero.

Full production‑grade architecture

The final design consists of:

Primary with GTID, semi‑sync, durable InnoDB settings.

One semi‑sync ACK replica as failover candidate.

Dedicated read replicas for online queries.

Separate backup/analytics replicas to avoid load interference.

ProxySQL/MySQL Router for traffic routing, health checks, and automatic lag‑based replica eviction.

Orchestrator (or MGR) for automatic arbitration, promotion, and old‑master lock‑down.

# Primary example
[mysqld]
server_id=101
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
rpl_semi_sync_source_enabled=ON
rpl_semi_sync_source_wait_point=AFTER_SYNC
sync_binlog=1
innodb_flush_log_at_trx_commit=1

# Replica example
[mysqld]
server_id=201
read_only=ON
super_read_only=ON
gtid_mode=ON
enforce_gtid_consistency=ON
rpl_semi_sync_replica_enabled=ON
replica_parallel_workers=16
replica_parallel_type=WRITESET
replica_preserve_commit_order=ON

Monitoring and alerting

Four‑layer monitoring is recommended:

Availability: Replica_IO_Running, Replica_SQL_Running, error fields.

Performance: Retrieved_Gtid_Set vs Executed_Gtid_Set, worker count, relay‑log backlog.

Data safety: semi‑sync status, sync_binlog, innodb_flush_log_at_trx_commit.

Disaster‑recovery: pt‑table‑checksum diffs, backup success rate, failover rehearsal time.

Prometheus alerts can be tiered (P1‑critical, P2‑warning, P3‑info) for issues such as stopped replication threads, high lag, or semi‑sync fallback.

Backup, consistency checks, and rehearsal

Backups are not a substitute for replicas. A production‑grade pipeline includes daily physical backups (e.g., XtraBackup), continuous binlog archiving, and periodic logical checksum verification with pt‑table‑checksum. Monthly restore drills validate PITR capability and confirm that recovery scripts work end‑to‑end.

Step‑by‑step deployment checklist

Enable GTID, enforce consistency, and ROW binlog on all nodes.

Configure primary with sync_binlog=1 and innodb_flush_log_at_trx_commit=1.

Set replicas to read_only=ON, super_read_only=ON, and enable relay_log_recovery=ON.

Deploy at least one semi‑sync ACK replica as failover candidate.

Separate read, backup, and analytics replicas.

Integrate ProxySQL/MySQL Router with lag‑based traffic eviction.

Automate failover with Orchestrator or MGR and test monthly.

Implement application‑level read‑after‑write consistency (session stickiness, GTID wait, or lag thresholds).

Run regular consistency checks and backup restore drills.

Following this evolution—from position‑based async replication to GTID‑based semi‑sync parallel replication with automated failover—turns a fragile setup into a reliable, near‑zero‑loss MySQL foundation.