MySQL Binlog CDC Configuration for Production Workloads

Most MySQL CDC documentation ends at "enable the binlog and point a tool at it." The configuration decisions that determine whether a pipeline survives a failover, a schema change, or an extended outage are almost never covered. This article walks through those decisions in the order a team would make them: what each setting controls, what goes wrong when it's skipped, and how to verify the result before the pipeline goes live.

Why MySQL CDC is harder to keep running than it is to start

The binlog is not a purpose-built CDC feature. It is MySQL's internal replication mechanism, and every constraint that governs MySQL replica behavior also governs a CDC consumer reading from it. A CDC client registers with MySQL using the same COM_BINLOG_DUMP and COM_BINLOG_DUMP_GTID protocol a real replica uses, so every replication rule applies. A gap in one setting, whether retention, row image, GTID mode, or privileges, produces failures that can be silent for hours before they surface. This article assumes you have chosen binlog CDC and need to configure it correctly for production. It does not re-explain what CDC is or argue for it over batch processing.

The minimum viable binlog configuration and what each setting actually controls

A MySQL instance that is production-ready for CDC needs a specific set of settings in my.cnf. Each one controls a distinct behavior, and skipping any one of them introduces a class of failures covered in the sections that follow. The full block is:

[mysqld]
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 604800
gtid_mode = ON
enforce_gtid_consistency = ON

log_bin enables the binary log. If log_bin is off, there is nothing to read. binlog_format = ROW records actual before and after column values for each changed row, not the SQL text. MIXED mode is not a safe middle ground: MySQL's "unsafe statement" detection is imperfect, and some edge cases produce statement-format events that slip through.

binlog_row_image = FULL includes all columns in both the before and after image of every changed row. If it is, updates to JSON columns only log what changed inside the JSON value, not the full value. This is silent and easy to miss until data is compared against the source. It is a documented production hazard. binlog_expire_logs_seconds = 604800 sets retention to seven days. The value and why it matters are covered fully in the retention section that follows. gtid_mode = ON and enforce_gtid_consistency = ON enable GTID-based position tracking, which has its own section as well.

On managed cloud MySQL, including AWS RDS and Aurora, Google Cloud SQL, and Azure Database for MySQL, these settings are applied through the platform's parameter group or flag interface rather than by editing my.cnf. On RDS and Cloud SQL, binlog_format and GTID settings require a custom parameter group. Check current parameter names against your provider before applying them, because managed services rename parameters periodically.

Binlog retention: setting the window wide enough to survive real downtime

The seven-day retention value in the baseline configuration is not a suggestion to be trimmed. It is the first production-hardening decision a team makes, but the defaults in many environments are routinely too short. When a CDC consumer falls behind, for an incident, a deploy, or a scheduled maintenance window on the consumer itself, the only thing standing between recovery and a full re-snapshot is whether the binlog files it hasn't read yet still exist. The pipeline cannot resume. It must be rebuilt from scratch.

On AWS RDS and Aurora, the platform manages purging independently and can drop binlog files within hours of them being written. On RDS you check mysql.rds_show_configuration and set binlog retention hours before you rely on stop-and-resume behavior. The same caution applies to any managed platform that controls binlog lifecycle outside the customer's direct reach.

Retention alone is not enough. You have to watch consumer lag all the time, or the gap can grow until it crosses the retention window. A gap trending upward is a pre-failure warning.

One common objection is that the team already has alerts on replication lag. That is not the same signal. If the consumer is slow but steady, the lag alerts may not fire until the binlog window is nearly gone, and by then you have no time left to act.

GTID mode: why file-and-offset position tracking breaks on failover and what GTIDs fix

Traditional binlog position tracking, which records a filename and byte offset, breaks on failover because a newly promoted primary has different binlog files with different positions. The CDC consumer's bookmark becomes meaningless, and the pipeline must restart from a fresh snapshot. For a system whose entire purpose is to keep a downstream copy in sync, this is a significant failure mode.

GTIDs assign a globally unique identifier, of the form server_uuid:sequence_number, to every committed transaction. The GTID history is consistent across the cluster when properly configured. This is the core problem GTIDs solve: after a failover, file and offset coordinates are topology-specific and become stale, while GTID sets are topology-independent.

Enabling GTIDs requires both gtid_mode = ON and enforce_gtid_consistency = ON. The CDC consumer must also be configured to use GTID-based positioning, COM_BINLOG_DUMP_GTID, rather than file and offset. That is typically a connector-level setting, not a MySQL server setting, and it warrants equal attention at deploy time.

The replication user: minimum privileges and server_id collision avoidance

The CDC replication user needs a specific, bounded set of privileges. Granting too much creates unnecessary security exposure. The complete minimum set is five privileges:

GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT, RELOAD, SHOW DATABASES
ON *.* TO 'cdc_user'@'%';

REPLICATION SLAVE allows the client to connect and read binlog events using the replication protocol. REPLICATION CLIENT allows the client to query binlog position metadata, including SHOW MASTER STATUS and SHOW BINARY LOGS. Together these five form the complete set a CDC consumer needs.

The second identity decision concerns server_id. Every replication client attached to MySQL, including the CDC consumer, must register with a unique server_id. A CDC consumer that registers with a server_id already in use by a production replica can be disconnected or can interfere with the replica's own replication channel, and the symptom may not point at the cause.

The sound practice is to reserve distinct numeric ranges for distinct purposes. One range for read replicas, a separate range for CDC consumers, and documentation of the assignments so that future consumers don't collide. This is a small amount of discipline that eliminates a class of failures that are disproportionately painful to debug.

Row image completeness and the binlog_row_metadata setting that reduces DDL fragility

Beyond binlog_row_image = FULL, there is a second setting that most guides omit: binlog_row_metadata = FULL. This setting provides column names and type metadata inside the binlog event itself, reducing the CDC consumer's dependence on querying the live schema for column resolution. If a schema change such as a column drop or rename has already been applied, the consumer may map column positions incorrectly for events that were written before the change. The setting provides concrete benefits, per ClickHouse's ClickPipes CDC guide: binlog_row_metadata = 'FULL' provides benefits for column filtering, avoids issues caused by dropped columns on the source, and enables richer enum/set support in the CDC stream. The combination of binlog_row_image = FULL and binlog_row_metadata = FULL gives the consumer the maximum available context per event, making it less dependent on live schema state and more resilient to DDL changes that arrive out of order.

The trade-off to acknowledge is volume. NOBLOB is a reasonable compromise for tables with large, rarely-changed BLOB columns where binlog volume is a documented constraint. But for most schemas, the additional safety is worth the marginal increase in disk.

Handling schema changes without breaking the pipeline

Schema changes are not just another binlog event. The failure is particularly dangerous because it doesn't necessarily stop the pipeline. Not all DDL changes carry the same risk. Column drops and renames are dangerous. Column drops or renames require a coordinated schema-registry update on the consumer side.

Large transactions are a related hazard. MySQL serializes large transactions in the binlog, so a single massive DDL or bulk DML event can stall a CDC consumer that must process the entire event before moving on. Replication lag spikes suddenly, and the downstream system falls behind without warning.

The configuration choices that reduce DDL fragility start with binlog_row_metadata = FULL, which gives the consumer column names inside the event and reduces its dependence on the live schema at read time. For destructive changes, specifically drops, renames, and type changes, pause the consumer, apply the DDL, update the consumer's schema registration, then resume. Never apply destructive DDL while the consumer is running against the old schema.

Tables without primary keys and why they create downstream consistency problems

A table without a primary key can emit binlog events, but the CDC consumer cannot reliably perform idempotent row lookups or safe replay of those events in the downstream system. It requires only the existence of tables that lack primary keys. The result is a recurring source of duplicate or out-of-order writes in the target. Run this check before you enable CDC on a database:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_db'
AND table_name NOT IN (

);

Add surrogate primary keys to any tables that need them before the pipeline starts. That is true, and those tables are safe if they truly never receive updates or deletes. The hazard is any table where the downstream system needs to apply updates or deletes to maintain a consistent copy, which requires a stable row identity.

Pointing CDC at a read replica and what the replica configuration must include

Running the CDC consumer against a read replica rather than the primary database eliminates replication-related load from the primary, but the replica must meet the same configuration requirements the primary would, plus two additional constraints specific to replicas. A misbehaving consumer cannot compete with production write traffic. The replica must have its own unique server_id, distinct from both the primary and any other replicas or CDC consumers in the topology. The replica must also have binary logging enabled. On a replica, binary logging is enabled by default since MySQL 8.0, and log_slave_updates, called log_replica_updates in MySQL 8.0.26 and later, is also ON by default when binary logging is enabled, so that changes received from the primary are written to the replica's own binlog for the CDC consumer to read.

All binlog format and row image settings must match the production-ready configuration from earlier. Set binlog_format, binlog_row_image, and binlog_row_metadata on the replica to the same values as on the primary. A replica that inherits MINIMAL row images from a primary configured that way, or that carries different metadata settings, will produce the same data-completeness problems as if the consumer were reading the primary directly. Monitor both, and set an alerting threshold on replica lag that accounts for acceptable end-to-end latency.

Managed MySQL platforms: where configuration paths diverge from self-managed instances

On managed MySQL platforms, the configuration settings are the same, but the mechanism for applying them differs by provider, and some settings are controlled or overridden by the platform in ways that require explicit action beyond editing my.cnf. If you have configured self-managed MySQL before, you still need to learn the managed path. On AWS RDS and Aurora, binary logging is only automatically enabled when automated backups are enabled. Confirm that before you assume binlog is available: binlog_format and binlog_row_image need a custom DB parameter group, because the default parameter group cannot be modified. Binlog retention must be set explicitly using mysql.rds_set_configuration('binlog retention hours', N) and verified with mysql.rds_show_configuration.

Validating the configuration before the pipeline goes live

The configuration decisions in the preceding sections only hold up if they are verified against the running instance, not just applied in a config file. The verification queries to run against the live instance are straightforward:

SHOW VARIABLES LIKE 'log_bin';                    -- must return ON
SHOW VARIABLES LIKE 'binlog_format';              -- must return ROW
SHOW VARIABLES LIKE 'binlog_row_image';           -- must return FULL
SHOW VARIABLES LIKE 'binlog_row_value_options';   -- must not return PARTIAL_JSON
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- must match retention decision
SHOW VARIABLES LIKE 'gtid_mode';                  -- must return ON
SHOW VARIABLES LIKE 'binlog_row_metadata';        -- must return FULL

Check the binlog retention variable during pre-flight verification, because it is easy to set in a config file and have it fail to apply. Finally, run the primary-key gap query one final time before you enable the stream. Tables you added since the first audit may have no primary keys, and fixing them now costs less than repairing downstream inconsistencies later.