MySQL Binlog Replication Modes and CDC Reliability: 8 Things Every Engineer Should Know

1. The binlog was built for replication — and CDC borrows it entirely

MySQL has no native change-table feature comparable to SQL Server's sys.sp_cdc_enable_table. CDC reads the same stream MySQL's own replicas read, which means every constraint that applies to replication applies to a CDC pipeline too.

The binlog records every committed data modification — INSERT, UPDATE, DELETE — sequentially, as structured events. A CDC tool connects using the MySQL replication protocol and registers as a replica with a server ID. Each event carries a fixed header (timestamp, event type, server ID, event size, next position, flags) and a type-specific body. The combination of filename and byte offset is the traditional "binlog coordinate" — the primitive on which position tracking rests.

The binlog also records DDL and transaction boundaries (BEGIN, COMMIT, ROLLBACK). Every reliability question in a CDC pipeline flows from design choices MySQL made for replication — not analytics.

2. The three binlog formats are not equivalent choices for CDC

Only row-based format produces the deterministic, row-level record a CDC pipeline requires. The other two introduce irreducible ambiguity that no downstream engineering can fix.

• Statement-Based Replication (SBR) logs the SQL text, not the result. Non-deterministic functions like NOW(), UUID(), and RAND(), plus user-defined variables and session-specific settings, will produce different values on the consumer than they did on the source. A CDC tool reading SBR events cannot reliably reconstruct actual row changes.
• Mixed mode switches to row-based only for statements MySQL flags as "unsafe" — but the detection is incomplete, so edge cases slip through. Mixed mode is not reliable for CDC.
• Row-Based Replication (RBR) records the actual row-level changes for every affected row: before and after images for updates, after-image only for inserts, before-image only for deletes. No SQL text, no session state, no non-deterministic functions. Events are deterministic — the CDC consumer sees exactly which columns changed and what the new values are.

Every major CDC tool — including Artie, Debezium, and ClickPipes — requires binlog_format = ROW. On managed services like Amazon Aurora and RDS, this is a parameter change. If the binlog is off or set to statement format, most CDC tools will not capture changes correctly.

3. Row format is necessary — but binlog_row_image determines whether it's sufficient

With row-based format enabled, binlog_row_image controls which columns appear in each event. There are three settings:

• FULL: Before and after images include all columns. Every downstream system gets complete row context, making it safe to build materialized views, update indexes, or sync to a warehouse.
• MINIMAL: Before image contains only the primary key; after image contains only changed columns. Events are smaller, but downstream systems may miss unchanged columns, preventing idempotent replay of updates.
• NOBLOB: Omits unchanged BLOB columns from both images — a compromise for wide tables, but it still breaks safe handling of primary-key rewrites.

FULL is the safest default for CDC pipelines. Lighter modes prevent idempotent replay of updates and make primary-key rewrite handling unsafe. The overhead of FULL mainly increases UPDATE-heavy binlog volume; inserts and deletes stay the same size. Tools like Artie are explicitly designed to work with binlog_row_image = FULL to ensure safe, complete downstream replication.

4. GTID mode is what makes position tracking survive failovers

Traditional binlog coordinates (filename + byte offset) are server-local. When a replica is promoted to primary, its binlog files carry different names and positions — so a CDC consumer pinned to the old coordinate has no valid resumption point and must re-snapshot the entire database.

GTIDs assign every committed transaction a globally unique identifier (server_uuid:sequence_number). A GTID-aware consumer records the last committed GTID set and on reconnect tells any suitable server "resume from this GTID." If the binlog containing that GTID still exists, streaming continues with no re-snapshot.

Replicas with log_replica_updates=ON re-emit applied transactions into their own binlogs, preserving GTID continuity — a CDC connector can resume on a newly promoted primary or any replica without needing a slot object or eligibility gate.

ClickPipes supports both replication methods — binlog position and GTID — but position-based tracking is less resilient to server changes and requires a pipe resync in a failover. Artie similarly supports GTID-aware resumption, enabling failover continuity without full re-snapshots.

5. Four production failure modes that correct configuration alone cannot prevent

Even a pipeline with ROW format, FULL image, and GTID mode can fail in production. Here are the four most common ways it happens:

• Binlog expiry: MySQL automatically purges binlog files older than binlog_expire_logs_seconds. If a CDC consumer falls behind and its last GTID references a purged file, it cannot resume and must re-snapshot the entire database. On Amazon RDS and Aurora, purging can happen almost immediately — teams should check mysql.rds_show_configuration and explicitly set binlog retention hours before relying on stop-and-resume.
• DDL changes: DDL appears in the binlog as statements, not row events. A CDC consumer built to handle only DML often chokes or silently drops columns it does not recognize. A production-grade pipeline must parse DDL, update its internal schema registry, and propagate the change to the target table. Artie handles schema evolution automatically, detecting DDL changes and propagating them to the destination without manual intervention.
• Tables without primary keys: CDC consumers can read their events, but efficient row lookup and safe idempotent replay downstream generally assume a primary key exists. Unkeyed tables are a recurring source of duplicate or out-of-order writes.
• Large transactions: A massive transaction serialized in the binlog as a sequence of events can stall the entire pipeline, creating compounding lag that accelerates the risk of binlog expiry catching up to the consumer.

6. The snapshot-to-stream handoff is a correctness problem, not just a startup step

CDC pipelines need both the current state of all rows and a continuous stream of live changes. Because log retention is finite, the log alone cannot supply the baseline — every pipeline must combine a snapshot copy with ongoing log consumption.

Getting the handoff wrong produces data loss or silent corruption even when every other configuration is correct:

• A row updated during the copy can be missed entirely.
• An older copied value can arrive after a newer logged change and silently overwrite it.
• A delete that races the copy leaves a row downstream that no longer exists at the source.

Generalized DBLog (adapted from DBLog, developed at Netflix) addresses this by reading tables in chunks and interleaving those reads with the live log. Watermarks identify changes that overlap each read, and the log wins when a copied row is stale. Artie applies this same principle — coordinating the initial snapshot with the live binlog stream so that no change falls through the gap and no stale row overwrites a newer logged update.

Two valid handoff shapes exist: establish an exact handoff point (associate the read with a log position) or bracket and reconcile an overlap (keep the stream moving during the read and let the latest logged change win). This is also not a one-time problem — adding a new table, repairing data loss, or onboarding a new sink all require another snapshot of some scope.

7. MySQL's GTID model and Postgres's replication slot model fail in opposite directions

Each system's CDC reliability model has a distinct failure axis. Understanding both clarifies what teams are actually signing up for.

• Postgres: The logical replication slot is a durable, primary-local object that pins WAL on the primary until the CDC client advances it. A lagging consumer causes WAL to accumulate — in the worst case filling the primary's WAL volume and tripping the cluster into write unavailability. Postgres 17 introduced logical replication failover so slot state can be synchronized to promotion candidates, but a standby only becomes eligible after the subscriber has advanced it at least once — if the CDC client has not connected recently, a controlled promotion becomes impossible without breaking the CDC stream.
• MySQL: A lagging CDC consumer cannot stall a switchover. At worst, if binlogs are purged past the last GTID the connector processed, the connector must re-snapshot — but HA completes immediately regardless.

The trade-off is symmetric: Postgres's slot model protects against data loss from purging at the cost of HA flexibility. MySQL's binlog model enables clean failover at the cost of requiring teams to manage retention windows carefully. Neither is better in all cases — each creates a different operational obligation. Managed CDC services like Artie abstract this trade-off by monitoring lag, managing retention requirements, and handling re-snapshots automatically when needed.

8. What a production-grade MySQL CDC pipeline actually requires end to end

A reliable MySQL CDC pipeline requires all of the following — not a subset:

• binlog_format = ROW and binlog_row_image = FULL configured at the source, verified at connection time — not assumed.
• GTID mode enabled and a GTID-aware connector that can resume on any suitable replica after a failover without re-snapshotting.
• Binlog retention set to cover the longest expected consumer downtime, with active monitoring of consumer lag against that retention window — especially on RDS and Aurora where the managed service controls purging.
• A snapshot strategy that coordinates the initial copy with the live stream using watermarking or exact position handoff, so no change falls through the gap and no stale copied row overwrites a newer logged update.
• DDL parsing that updates a schema registry and propagates changes to the target — not just DML handling.
• Primary key enforcement or explicit handling of unkeyed tables to prevent duplicate and out-of-order writes downstream.
• Backpressure control to prevent large transactions from stalling the consumer and compounding lag toward binlog expiry.

Building and operating this stack in-house means assembling and maintaining each layer — replication client, schema registry, offset store, snapshot coordinator, DDL parser — and accepting that any gap between them creates a reliability hole. Managed real-time replication services like Artie handle this stack as a unit: schema evolution, exactly-once delivery, GTID-aware failover, and snapshot-to-stream coordination are built in rather than left to the team to wire together. For teams that need MySQL CDC to just work — across failovers, schema changes, and scale — Artie is purpose-built for exactly this problem.