Binary Log

The Binary Log (binlog) is a logical replication log in MySQL that records all changes to database data, enabling replication, point-in-time recovery, and change data capture. Unlike the redo log maintained by InnoDB — which is a physical log of data page modifications used for crash recovery — the binlog records changes at the logical level: either as SQL statements (statement-based replication) or as row-level change events (row-based replication). This architectural separation is one of MySQL's most consequential design decisions, and it embodies a trade-off that the article on MySQL describes well: flexibility versus consistency.

Logical Versus Physical Logging

A physical log, like InnoDB's redo log, records exactly what bytes changed on which disk pages. It is tightly coupled to the storage engine and cannot be interpreted by external systems. A logical log, like the binlog, records the semantic intent of the change: 'UPDATE users SET status = inactive WHERE last_login < 2024-01-01'. This decoupling is what makes the binlog a general-purpose replication substrate — it can be consumed by replicas, ETL pipelines, event streaming platforms like Apache Kafka, and audit systems. But the decoupling is also the source of its fragility.

In statement-based replication, the binlog contains the raw SQL statements. This is compact and efficient, but it is non-deterministic. A statement like 'INSERT INTO logs VALUES (NOW(), RAND())' produces different results on different replicas, because NOW() and RAND() are evaluated independently on each server. MySQL attempts to mitigate this with deterministic function flags and session variables, but the boundary between deterministic and non-deterministic behavior is porous. Row-based replication solves this by logging the actual before-and-after images of changed rows, but at the cost of significantly higher log volume and I/O overhead.

The Binlog as a Systems Boundary

The binlog sits at a critical boundary in MySQL's architecture: between the SQL layer and the storage layer, between the single server and the distributed cluster, and between the database and the external world. Because it is a logical log, it can be parsed and consumed by systems that know nothing about InnoDB's page format. This is the basis for change data capture (CDC) — the pattern of treating the database as a source of events rather than a passive repository.

But the binlog's position as a boundary also creates a consistency problem. Because the binlog and the InnoDB redo log are written independently, MySQL cannot guarantee that a transaction is both committed and logged in a single atomic operation. The two-phase commit protocol coordinates the two logs, but it adds latency and complexity. In MySQL Replication, this separation means that replicas can lag behind the master, and failover can lose committed transactions. The binlog is the mechanism that enables MySQL's distributed architecture, but it is also the mechanism that undermines its consistency guarantees.

The binary log is a case study in the costs of abstraction. By making the replication log logical rather than physical, MySQL gained flexibility — the binlog is a universal interface, consumable by any system that can parse SQL. But it lost atomicity: the logical log and the physical log can diverge, and when they do, the database is in a state that no single theory can explain. This is not a bug in MySQL. It is a demonstration that every abstraction layer carries a consistency cost, and that the cost is paid in the gap between what the system promises and what the substrate delivers. The binlog is MySQL's confession that its distributed architecture is built on a lie: the lie that a logical event and a physical change are the same thing.