MySQL Binary Log (binlog)

# MySQL Binary Log (binlog)

AI Summary

Purpose:

• Durable concept note on the MySQL binary log (binlog): what it is, its formats, and why it matters for replication, CDC, and the Labradorlabs BTS (Binlog Transfer System).

Key points:

• binlog records data/schema changes (DML, DDL) and is the basis for MySQL replication.
• Three formats: row-based, statement-based, mixed.
• DDL is always logged statement-based even under row-based format — which is the root of a real DDL-loss pitfall when filtering by --database.
• mysqlbinlog is the tool to read/replay binlogs; --base64-output=DECODE-ROWS and --verbose outputs are human-readable but comment-prefixed, so feeding them back into MySQL silently does nothing (treated as comments).
• At Labradorlabs, binlog is the data-distribution transport: DIST DBs emit binlogs that BTS transfers to customer on-premise DBs.

Relevant when:

• Working on BTS, CDC, replication, binlog scrambling, or debugging missing DDL/DML during binlog import.

Do not read full document unless:

• You need the exact mysqlbinlog flag behavior or the DDL --database filtering pitfall.

Linked documents:

• [[../people/career-timeline.md]]
• [[troubleshooting-playbook.md]]

Open Questions

• None. Content is grounded in the MySQL 8.4 replication-formats documentation and the author's hands-on binlog notes.

Details

Source: author's MySQL binlog tech note (Obsidian vault, 500. Wiki/02. Tech Docs/01. MySQL/01. binlog.md) + linked MySQL docs (dev.mysql.com/doc/refman/8.4/en/replication-formats.html).

What binlog is

• The binary log records changes to the database: DML (INSERT/UPDATE/DELETE) and DDL (ALTER/CREATE/...).
• It is the log used for replication (source → replica) and is also usable for point-in-time recovery and change-data-capture (CDC).
• Reading/replaying binlogs requires the mysqlbinlog tool/package.

Formats

• row-based: stores changes as row events — one event per changed row. Human-readable inspection shows which table changed (as a row event, not SQL).
• statement-based: stores the SQL statement that caused the change.
• mixed: a mix of both.
• Important: even under row-based format, DDL (ALTER, CREATE, ...) is recorded as statement-based, not as row events.

Reading binlogs with mysqlbinlog

• mysqlbinlog --base64-output=DECODE-ROWS {BINLOG}: renders row events in a human-readable way (you can see which table is affected as a string), but the output lines are comment-prefixed with #.
• mysqlbinlog --verbose {BINLOG}: renders the equivalent SQL so a human can see the queries, but it too is comment-prefixed with #.
• Pitfall: because both outputs are comment-prefixed, if you feed that output back into MySQL it is treated as comments and silently produces no error and no effect. These flags are for human inspection, not for re-applying changes.

DDL loss when filtering by --database

• mysqlbinlog --database labradordb filters events to the currently-connected DB context.
• statement-based DDL is only emitted when the current/connected DB matches the filter. If the source did not record USE labradordb; (or set the default DB to labradordb) before a statement like ALTER labradordb.tb_text1 ..., the event is judged unrelated to labradordb and dropped — even though the object is fully-qualified as labradordb.tb_text1.
• Net effect of --database + the row/statement difference: DML is not lost, but DDL can be lost. Mitigation: ensure USE labradordb; precedes DDL on the source, or set the default DB appropriately.

Relevance to CDC and BTS at Labradorlabs

• BTS (Binlog Transfer System) uses binlog as the transport for distributing data changes: the distribution DBs (DIST) emit binlogs which BTS transfers to customer on-premise databases, where an Updater applies them.
• Because the distribution DB was split into dist-filecomp / dist-other (later renamed source / main), each instance emits its own binlog and BTS pulls them in parallel.
• Binlog scrambling: BTS XOR-scrambles part of the binlog header so the transferred file content is not trivially readable in transit — a lightweight data-protection measure layered on top of the binlog transport.
• The DDL-loss-on---database pitfall above is directly relevant to any binlog-based sync: silent DDL drops can desynchronize customer schemas, so DDL handling must be explicit.