Binlog Replication

MySQL is the most deployed relational database in the world. Facebook runs one of the largest MySQL fleets on earth, over 1,000 primary instances serving billions of queries per day. Uber, Shopify, Airbnb, Pinterest, LinkedIn, they all run massive MySQL deployments. And every single one depends on a single feature for replication, failover, and data pipelines: the binary log.

The binlog (binary log) is MySQL's record of every data-modifying operation. Every INSERT, UPDATE, DELETE, and DDL statement gets logged to the binlog before the transaction is committed. Replicas read from the binlog to stay in sync with the primary. CDC tools like Debezium read from the binlog to produce change events for Kafka. Point-in-time recovery reads from the binlog to replay operations after restoring a backup.

It is the single most important file in a MySQL deployment. If you lose the data files but have the binlog, you can rebuild. If you lose the binlog, your replicas cannot sync and your CDC pipelines stop. Understanding how it works is non-negotiable for anyone operating MySQL at scale.

The binlog is a sequential log of events, written to disk in a series of numbered files (mysql-bin.000001, mysql-bin.000002, etc.). When one file reaches max_binlog_size (default 1GB), MySQL rotates to a new file. The files are append-only during normal operation.

There are three binlog formats, and choosing the right one affects everything from replication correctness to CDC pipeline design.

Statement-based replication (SBR). Logs the actual SQL statements. UPDATE users SET email = 'new@email.com' WHERE id = 42 is logged as-is. The replica re-executes the same statement. Compact, but dangerous: non-deterministic functions like NOW(), RAND(), UUID(), or SYSDATE() produce different results on the replica. MySQL 5.1 defaulted to this and shops learned the hard way when replica data diverged silently.

Row-based replication (RBR). Logs the actual row changes. Instead of the SQL statement, it logs "row with id=42 changed from {old values} to {new values}." Deterministic and safe, but produces more data. An UPDATE ... WHERE status = 'old' that touches 100,000 rows generates 100,000 row-change events. GitHub's 2012 incident involved massive binlog volume from a single ALTER TABLE that produced terabytes of row events.

Mixed-mode replication. MySQL chooses SBR for simple deterministic statements and switches to RBR for non-deterministic ones. The "best of both worlds" approach, and the default in MySQL 8.0.

For CDC, row-based format is required. Debezium, Maxwell, and Canal all need the actual before/after values of each row to produce structured change events. Statement-based format just gives them SQL text, which they would have to re-execute to know what changed. That is impractical and defeats the entire purpose of CDC.