MySQL Binlog Parsing and Row Image Modes

The MySQL binary log is a sequential, append-only record of every data-modifying and schema-modifying event a server processes: inserts, updates, deletes, table creation, column changes, and the grants and revokes that fall under data control language. Its structure sets the outer boundary of what any downstream change data capture pipeline can recover: a CDC consumer can only ever see what the binlog wrote down. The binlog serves three distinct purposes inside a MySQL deployment. Replicas replay it to stay in sync with a primary. Backup tools use it for point-in-time recovery, replaying events recorded after a restored backup to bring a database forward to a specific moment. External consumers, including CDC pipelines, read it as a stream of change events, treating MySQL less as a database to query and more as a durable log to tail.

The format of what gets written is controlled by a single server variable, binlog_format, and the choice matters more than its name suggests. STATEMENT format logs the SQL text that caused a change, so the log stays compact, but correctness then depends on the exact conditions under which the statement ran. If a statement uses NOW(), UUID(), or RAND(), it produces a different result every time it executes, so a replica or a CDC consumer that replays it later has no guarantee it reproduces the same row values the primary actually wrote. ROW format sidesteps the problem entirely because it records the actual row data that changed, not the statement that changed it. It has no dependency on session variables, it introduces no ambiguity from non-deterministic functions, and a consumer never has to re-execute anything. Every major CDC tool requires ROW format for this reason: it is the only format that guarantees the bytes in the binlog correspond exactly to the row values that existed in the table.

An event written under ROW format can carry up to two images of the row it affects. The before image shows the row's state before the change, and you get it for UPDATE and DELETE operations. The after image captures the row's state after the change, and it applies to INSERT and UPDATE operations. Settling on ROW format answers the question of what kind of record gets written, but it leaves a second, equally consequential question open: how much of each row actually ends up in those before and after images.

What FULL, MINIMAL, and NOBLOB write into the binlog

That second question is answered by binlog_row_image, and its three possible values, FULL, MINIMAL, and NOBLOB, do not represent shades of the same behavior. Each one builds a structurally different event payload, so a consumer gets different guarantees about which columns are actually there to read.

FULL is the default, and it writes every column into both the before and after image of a row change. An UPDATE on a 40-column table generates a before image with all 40 columns as they existed prior to the change and an after image with all 40 columns as they exist afterward, regardless of how many columns the UPDATE statement actually touched. Binlog volume therefore scales with the width of the row, not with the number of columns that changed: a single-column update on a wide table still logs the full row twice. This cost falls almost entirely on UPDATE-heavy workloads, because inserts and deletes write a full row image either way, so FULL usually costs only a little more than narrower modes.

MINIMAL takes the opposite approach. Its before image carries only the columns needed to identify the row, typically the primary key or a unique index, and its after image carries only the columns that actually changed. If most columns on a wide table stay stable across updates, you end up with a substantially smaller event. The tradeoff is specific: a MINIMAL UPDATE event tells a reader what changed and what its new value is, but the unchanged columns' values before and after the event are never written, so no record of them exists.

NOBLOB sits between the two. It behaves like FULL for every ordinary column, so you get full before and after images, but if a BLOB or TEXT column was not modified by the change, both images exclude it. A row with a large unchanged TEXT column gets that column's value omitted from the event; a row where that same column was edited gets the new value written in full. The savings are targeted at schemas where large object columns exist but rarely change.

| Mode | Before image | After image | Size impact | |---|---|---|---| | FULL | all columns | all columns | largest, scales with row width | | MINIMAL | identifying columns only | changed columns only | smallest | | NOBLOB | all columns except unchanged BLOB/TEXT | all columns except unchanged BLOB/TEXT | moderate, targeted at large-object schemas |

Checking which mode a server is running is a single statement, SHOW VARIABLES LIKE 'binlog_row_image';, and changing it at runtime is equally direct with SET GLOBAL binlog_row_image = 'MINIMAL';. Persisting the setting across restarts means placing it under the [mysqld] block in my.cnf alongside binlog_format = ROW.

Why MINIMAL and NOBLOB silently break CDC consumers

MySQL's own documentation treats MINIMAL as a safe, supported option for standard replica setups, and that endorsement is accurate as far as it goes. A MySQL replica that receives a MINIMAL UPDATE event already has the full current row in its own storage engine. When the event arrives with only a primary key and a set of changed columns, the replica looks up the existing row locally, applies the delta to the columns the event specifies, and leaves the rest untouched. The replica never needed the unchanged columns in the event, because it already had them.

A CDC consumer has no equivalent fallback. It reads the binlog stream directly, and the event payload is the entirety of what it ever sees of that transaction. There is no local storage engine holding a mirror of the source table, no row to look up, nothing to reconcile the event against. When a MINIMAL or NOBLOB event omits unchanged columns to save space, that information is simply never written down. A downstream materialized view, search index, or warehouse table that receives a MINIMAL UPDATE event is left with exactly two options: write a row with only the changed columns populated, producing an incomplete record, or discard the event, losing the change. Neither preserves the correctness guarantee the pipeline was presumably built to provide.

The failure sharpens in the case of a primary key rewrite. If an UPDATE changes the value of the primary key itself, you need the before image to carry the old key value so you can locate the correct row downstream. In MINIMAL mode, the before image always carries just the primary key, which is enough for the ordinary case. But when the key is the column being changed, the event's structure introduces ambiguity about which row the change actually targets, a class of error that FULL images don't produce because they carry every column, old and new, with nothing left implicit. The same structural gap applies to NOBLOB for any pipeline that needs to track BLOB, TEXT, JSON, or GEOMETRY columns: a consumer has no way to distinguish a large object column that was genuinely unchanged from one that was simply left out of the event, because both cases look identical on the wire.

What makes this dangerous in production is that none of it raises an error. The pipeline keeps running. Rows keep landing in the destination table. The failure becomes visible only when someone queries the specific columns that were silently dropped from an event weeks or months earlier, by which point the gap between the source table and the downstream copy may be extensive and difficult to trace back to a single configuration variable.

The objection that MINIMAL is MySQL's own documented, supported configuration for replication is correct, and this is where the reasoning has to be separated. A replica's storage engine supplies the correctness guarantee for native replication, not the binlog event itself, so the event only has to carry a delta because the replica already has everything else. A CDC consumer has no storage engine standing behind it to fill that gap. If you built one, a cache mirroring the source database's current row state so you could reconcile incomplete events against it, you would just be reconstructing the lookup capability a MySQL replica already has built in. At that point, the CDC tool has built a second, parallel implementation of MySQL replication.

The additional configuration settings that interact with row image mode

If you set binlog_row_image to FULL, you remove the structural gap described above, but you still don't get a reliable CDC pipeline guaranteed. Several adjacent server settings can undermine an otherwise correctly configured stream, so you need to put each one on a configuration checklist before a pipeline goes into production.

binlog_row_value_options controls whether JSON columns are logged as complete values or as partial diffs. If you set it to PARTIAL_JSON, MySQL writes only the portion of a JSON document that changed instead of the full document, and that breaks any CDC consumer expecting a complete JSON payload in every event. So leave this setting at its default for any pipeline that reads JSON columns through CDC.

binlog_expire_logs_seconds governs how long binlog files are retained before MySQL purges them. Retention has to be long enough to survive any realistic pipeline outage, because if a binlog file is purged before a CDC consumer has processed the events inside it, those events are gone permanently, and the only way forward is a full resnapshot of the affected tables.

binlog_row_metadata, set to FULL, causes MySQL to include column names and related schema metadata directly inside binlog events. So a CDC consumer can resolve what a given column position represents without querying the information schema separately as often, which simplifies its schema-tracking logic.

GTID mode, while optional, is recommended for any CDC deployment. Global transaction identifiers give each transaction a logical, globally unique identifier that holds steady across binlog file rotations and primary failovers, which plain file-and-offset coordinates do not. A consumer tracking position by GTID set can reconnect to any suitable server in the topology after a failover and resume from exactly where it left off; a consumer tracking by file and offset typically needs a full resync or a manual recalculation of its position once the primary changes.

Session timeout settings deserve attention as well. Extending them prevents the replication connection a CDC consumer holds open from being dropped during periods of low write traffic, an interruption that otherwise forces an unnecessary reconnect and resync cycle.

On managed platforms, these same settings are exposed through different interfaces rather than through my.cnf directly: parameter groups on AWS RDS and Aurora, server parameters on Azure Database for MySQL, and database flags on Google Cloud SQL. The mechanism for setting each value differs by platform, but the required values themselves do not change.

How a CDC client parses binlog events

A CDC consumer connects to MySQL using the same binary replication protocol a MySQL replica uses, registering itself with the primary as a replica identified by a server ID. Once connected, it receives a stream of typed binary events in the exact order the primary committed them, and the shape of what it does with that stream depends entirely on the row image mode the server was configured with upstream. ClickPipes, the data integration component built into ClickHouse Cloud, implements MySQL CDC along these lines, and its architecture illustrates the pieces a working pipeline needs.

Three event types carry row data. An INSERT event carries only an after image, the full set of column values for the new row. A DELETE event carries only a before image, the column values needed to identify and remove the row. An UPDATE event carries both a before image, identifying the row being changed, and an after image, giving the new values, and the completeness of each is governed directly by whatever binlog_row_image mode the server is running. Separately, schema change events arrive as DDL statements, ALTER, CREATE, DROP, and the consumer has to parse these and update its own internal understanding of the table's structure before it can correctly interpret any row event that follows. That internal structure is the schema registry: a record, maintained by the consumer itself, of each table's column definitions and primary keys. It matters because raw binlog row events reference columns by ordinal position, not by name, so without a schema registry mapping positions back to column names, a consumer has no way to know what any given value in an event actually represents.

Position tracking in the stream follows one of two models. File-and-offset tracking identifies a consumer's place in the log by binlog filename and a byte offset inside that file, and it is the original, more fragile approach. It works reliably as long as the topology stays static, but a failover breaks it: if the primary fails and a replica is promoted in its place, the new primary has its own binlog files with their own offsets, and a bookmark recorded in the old file-and-offset scheme has no meaning there. GTID tracking avoids this, because a GTID is unique across the entire topology rather than tied to a single server's file layout, so a consumer can record the last GTID set it committed and resume from any suitable server after a failover without recalculating anything by hand.

Checkpointing ties the whole mechanism together. The consumer periodically persists its current position, either a GTID set or a file-and-offset pair, so that after a crash or a planned restart it can resume exactly where it left off rather than reprocessing events already delivered downstream.

The bootstrapping problem: merging an initial snapshot with a live binlog stream

A binlog stream, however it is configured, only ever carries changes from the moment a consumer starts reading it. It says nothing about the rows that already existed in the table beforehand. A new CDC pipeline has to acquire that existing state by some other means before the binlog stream alone becomes a complete and trustworthy source of truth. That initial state capture, generally performed as a snapshot query against the live table, has to be reconciled with the binlog stream so that no row is captured twice and no row is missed in the gap between when the snapshot was taken and when streaming began. Getting that handoff wrong produces exactly the kind of silent gap the earlier sections describe: a pipeline that appears to be running correctly while quietly missing or duplicating rows from the seam between its snapshot and its stream.