MySQL GTID Replication and Its Impact on CDC Consumers

MySQL offers no purpose-built change capture facility. Every tool that streams changes out of a MySQL database does so by reading the binary log, the same event stream that MySQL's own replicas consume. The binary log is not an optional source of truth. It is the only source.

When a transaction commits, MySQL records it in the binlog as a sequence of structured events. A CDC tool opens a connection over the standard MySQL wire protocol, issues a replication command such as COM_BINLOG_DUMP or, when GTID mode is active, COM_BINLOG_DUMP_GTID, and registers itself as a replica with a unique server_id. There is no separate CDC pathway with different guarantees.

This shared substrate makes the binlog's format settings decisive for data correctness. Row-based replication is not optional for CDC work. Statement and mixed formats log the SQL text rather than the resulting row values, and any statement that invokes a non-deterministic function, NOW() or UUID() being the canonical examples, will produce different results when replayed. The rule is simple: binlog_format must be set to ROW for any CDC workload.

Even with row-based replication enabled, the binlog_row_image setting determines what a consumer actually receives. The FULL setting emits complete before and after values for every changed row, which is what a CDC pipeline needs. NOBLOB sits between the two, excluding unchanged binary columns as a volume compromise. Each setting trades fidelity for bandwidth, but the default on many older instances is not the one a CDC pipeline should run with.

How file-and-offset coordinates become a liability the moment topology changes

A binlog coordinate is a pair of values: a filename and a byte offset within that file. It is meaningful only on the server that wrote the file. This is not an implementation detail. It is the defining property of the traditional approach to tracking replication position, and it is server-local by construction. When a MySQL instance writes binlogs, it names them sequentially and the offsets within each file are positions in that specific file's byte stream.

The moment topology changes, this coordinate system breaks. If a replica is promoted to primary, the new primary does not continue the old primary's binlog sequence. The only recovery path is a full re-snapshot of the source database, which restarts the pipeline from the beginning of the data.

That re-snapshot is not a minor operational inconvenience. On a large database, the initial load can take hours. For a team that treats its analytics as production infrastructure, hours of downtime is a serious incident, not a routine maintenance event.

The failure mode is often silent. Teams running file-and-offset tracking are not making an operational mistake. They are working with a mechanism that cannot, by its structure, survive a topology change.

What GTIDs are and how they make replication position topology-independent

A GTID is a globally unique transaction identifier, a pair consisting of the server's UUID and a sequence number, written as server_uuid:sequence_number. MySQL assigns one to every committed transaction, and because the server UUID is unique to the instance that first executed the transaction, the identifier is unique across the entire replication topology. This is the core structural change: the identifier travels with the transaction, not with the file it happened to be written to.

A CDC connector running with GTID mode records its last committed GTID set. When it reconnects, it tells any suitable server what that set is, and that server sends every event after the last GTID in the set. If the binlog containing the last committed GTID still exists, streaming continues with no manual intervention.

The mechanics that make this possible run through normal replication. Replicas configured with log_replica_updates=ON re-emit the transactions they apply into their own binlogs, preserving GTID continuity. This is what lets a promoted replica serve as a valid resume point: the transactions it applied while it was a replica carry their original GTIDs forward into its own binlog, so a consumer can find the same logical position on the new primary that it had on the old one.

GTID mode also unlocks a capability that file-and-offset tracking complicates. If a team wants to run CDC from a read replica, GTID mode is not a preference. It is a requirement. Without GTIDs, a consumer reading from a replica would be pinned to that replica's local binlog coordinates and would have no clean path to resume elsewhere if the replica failed or was promoted. GTIDs eliminate the need to refer to log file positions at all, and they simplify the question of whether a source and its replicas are consistent. The position is a set of transaction identifiers, and any server that has applied those transactions is a valid continuation point.

How GTIDs change failover behavior for a CDC consumer in practice

With GTID mode enabled, the failover scenario that would force a manual re-snapshot under file-and-offset tracking becomes an automatic resume. Success depends on a single condition: whether binlog retention covers the gap between the connector's last processed GTID and the current state of the promoted server. It does not depend on whether the connector polled recently or whether the operator manually recorded a new position.

A lagging consumer cannot stall a switchover. But the high-availability switchover completes immediately regardless. This decouples the availability of the database from the progress of the CDC pipeline, which is a property file-and-offset tracking cannot offer.

The industry has converged on this conclusion independently. Apache Flink's MySQL CDC connector achieves high availability of MySQL HA clusters by using GTID information, and its documentation states the condition plainly: to obtain high availability, the MySQL cluster must enable GTID mode. The connector then reads with exactly-once processing even when failures happen.

ClickPipes adopted the same position as its default. Its GA release made GTID-based replication the default for new ClickPipes, treating file position-based replication as a legacy mechanism that breaks on failover and requires manual intervention. Existing pipes using the legacy mechanism continue to work, but the direction of travel is unambiguous.

These are three independent production systems, built by three different teams for three different audiences, arriving at the same conclusion through the same reasoning. The argument for GTID over file-and-offset is not a matter of vendor preference. It is a property of the mechanism itself.

Why MySQL's failover model for CDC is structurally simpler than Postgres's

Postgres takes a different approach to change capture, and its design creates a coupling that MySQL's GTID model avoids by construction. The Postgres logical replication slot is a durable, primary-local object. If the client lags, WAL accumulates on the primary. The brittle part appears when the cluster needs to fail over.

In a Postgres HA topology, standby nodes that have not observed slot progress are not eligible for promotion. If the CDC client has not connected in hours, a freshly added or recently restarted standby may meet none of the three. The consequence is that the CDC consumer's health becomes a precondition for database failover.

MySQL has no slot object. The CDC connector records a GTID set and resumes on any server that has the binlog. The MySQL model is simpler to reason about for CDC failover specifically, because the consumer's state never becomes a precondition for database availability.

Binlog retention as the operational constraint that determines whether GTID's failover guarantee holds

GTID-based resumption works only if the binlog containing the consumer's last GTID still exists. Binlog purge is therefore the primary failure mode for GTID-aware CDC, and it behaves very differently depending on whether the MySQL instance is self-managed or running on a managed cloud platform. MySQL automatically deletes binlog files older than the binlog_expire_logs_seconds setting. In MySQL 8.0, the default on self-managed instances is 30 days, a generous window that covers most realistic outages. The operational check is straightforward: run the mysql.rds_show_configuration stored procedure and set binlog retention hours explicitly before relying on stop-and-resume behavior.

The ClickPipes GA release paired its adoption of GTID-based replication with a minimum 72-hour binlog retention period, and the pairing is intentional. If retention is violated and the consumer's GTID is purged, the only recovery path is a full re-snapshot. The GTID advantage disappears entirely, and the team is back to the same downtime they would have faced with file-and-offset tracking.

The practical guidance follows from this constraint. Set retention to comfortably exceed the worst realistic downstream outage. The retention window is the contract between the database and the consumer, and it must be sized with the same care as any other operational guarantee.

Schema changes in the binlog and why MySQL's DDL capture is a CDC advantage

MySQL's binlog captures DDL statements alongside DML. A CDC consumer reading the stream can observe schema changes as they happen and propagate them to downstream targets. This is a capability that not all databases share, and it shapes how MySQL CDC pipelines handle schema evolution.

The binlog contains DDL statements such as ALTER TABLE, and modern CDC tools can parse these and automatically update the target schema. The behavior on the consumer side depends on what kind of change arrives and how the consumer was built. The DDL arrives in the stream regardless. Whether it is handled correctly is a property of the consumer, not of the source.

DDL capture is not a solved problem even for managed tools. AWS DMS release notes record a fix for a failure to capture special-format DDL changes during the CDC phase, which illustrates that DDL parsing correctness requires active maintenance even in mature platforms. Practitioners describe schema evolution as a silent killer in production, and the recommended mitigations are structural: add a schema registry, implement version checks, and set alerts for column drift alongside whatever DDL propagation the pipeline already does.

A related but distinct constraint applies to tables without primary keys. Such tables still emit binlog events. Production CDC pipelines effectively do.

Fan-out architectures and the server_id requirement for multiple CDC consumers

Multiple CDC consumers can read the same MySQL binlog simultaneously because each connects as an independent replication client with its own position tracking. The only hard requirement is that each consumer registers with a unique server_id, the same identifier that distinguishes replicas from one another. MySQL treats all of these clients as replicas, and replicas with duplicate server_ids conflict.

This is the standard fan-out pattern: one consumer feeds a data warehouse while another feeds a search index, and both read the same stream from the same source. With GTID mode, each consumer independently tracks its own committed GTID set. Consumers can fall behind or reconnect at different rates without affecting each other, because position is a property of the consumer, not of the connection or the server.

Without GTID, fan-out creates a coordination problem that compounds with topology changes. With GTID, each consumer simply reconnects to any server and sends its own GTID set. The mechanism scales the same way for one consumer or ten. Fan-out to multiple destinations, Snowflake, BigQuery, Redshift, and ClickHouse all being common targets, is a standard pattern for teams running real-time analytics alongside operational workloads. GTID makes this pattern resilient across topology changes in a way that file-and-offset tracking never could.

Large transactions, exactly-once delivery, and the limits of what GTID solves

GTID solves the position-tracking problem across topology changes. It does not, by itself, guarantee exactly-once delivery, and it does not protect against the latency and resource pressure that large transactions create. These are distinct classes of problems, and conflating them leads to pipelines that fail in ways their operators did not anticipate.

Large transactions are serialized in the MySQL binlog from the primary's perspective, and a CDC consumer may get stuck processing a single massive event. GTID tells the consumer where it is. It does not make large transactions any smaller or any faster to process.

Exactly-once semantics guarantee that each database change is delivered exactly one time even in the presence of failures. GTID tells the consumer where it is in the stream. It does not determine what the consumer does with duplicate delivery at the destination, and that is the key distinction. Idempotent writes and deduplication at the target are still required for true exactly-once guarantees, regardless of how precisely the consumer tracks its position.

Migration is not always zero-effort either. GTID removes a whole class of problems, the topology-dependent position-tracking failures, and makes a different class, delivery semantics and throughput behavior, easier to reason about by decoupling them from topology. It does not make those problems disappear.

How GTID-aware managed replication services remove the remaining operational burden

The configurations covered here, ROW format, binlog_row_image = FULL, GTID mode, explicit retention windows on RDS and Aurora, unique server_id per consumer, are prerequisites. Getting them right once, during initial setup, is not the same as operating them correctly over time. Configuration drifts, defaults change, new instances get provisioned with old settings, and an operator who configured GTID correctly on one cluster discovers, during a failover on another, that the new cluster was never set up the same way.

The ongoing operational concerns remain after the GTID configuration is correct. Understanding GTID mechanics is necessary for reasoning about whether a CDC pipeline will survive a failover. Implementing a production-grade GTID-aware pipeline is the sustained engineering work that a managed replication service absorbs.

The industry has settled the GTID versus file-and-offset debate for production workloads. The question for most teams is not whether to use GTIDs. The question is whether to build and operate the surrounding infrastructure, the retention monitoring, the schema evolution handling, the deduplication layer, or to rely on a service that has already done that work and continues to do it.