Skip to content

CONCEPT Cited by 2 sources

Binlog replication

Definition

Binlog replication is MySQL's native change-data-capture mechanism: every committed transaction is appended, in order, to the server's binary log (binlog), and any replica — or any external system speaking the MySQL replication protocol — can tail that log to see every committed change in commit order. The replay format can be statement-based (STATEMENT), row-based (ROW), or mixed; row-based is the default and the one that permits general-purpose logical replication across non-identical servers.

The MySQL replication protocol exposes the binlog to clients via two command families:

  • Legacy file/offset-based — COM_BINLOG_DUMP + binlog filename + offset. Brittle across primary failover and binlog rotation.
  • Modern GTID-based — COM_BINLOG_DUMP_GTID
  • a GTID set. The server streams every transaction the client is missing from that GTID set forward. Portable across failovers and topology changes.

The retention / throughput dependency

Binlogs are finite on disk. MySQL purges them after a retention threshold (binlog_expire_logs_seconds / expire_logs_days). Any consumer that falls behind the retention horizon cannot catch up — the binlog events it needs are gone — and has to restart from a fresh snapshot.

This forces a coupling between source-side retention and consumer-side throughput: if a long-running copy or a downstream replication can't keep current enough, it has to pause, catch up on what's already been applied, and resume before the binlog horizon laps it. This is exactly why VReplication's copy-phase cycle duration is a tunable knob — the interleaving of copy work and binlog catch-up is what keeps petabyte-scale migrations inside the retention horizon.

Seen in

  • sources/2025-03-18-redpanda-3-powerful-connectors-for-real-time-change-data-capture — canonical wiki disclosure of binlog replication as the MySQL CDC substrate for Redpanda Connect's mysql_cdc input connector. Canonical verbatim operating procedure: "MySQL CDC uses binlog positions to track changes, requiring an external cache (Redis, a SQL database, or another datastore) to store binlog offsets." Plus: "For consistency, this connector gets a global read lock during initial snapshots, records the binlog position, and then releases the lock to stream data from that precise point forward." Canonical structural framing: MySQL's binlog, unlike Postgres's server-owned replication slot, pushes offset durability onto the CDC consumer — canonicalised as external-offset-store. Topology scope explicitly limited at 2025-03: "Currently supports standard MySQL setups and primary-replica configurations, with plans to extend support for high-availability clusters and Global Transaction ID (GTID) environments." No GTID support at publication — a significant gap vs Debezium's MySQL connector which is GTID-native. This is the canonical wiki datum that binlog CDC consumers face an engine-supplied choice about offset durability that slot-based Postgres CDC does not.

  • — Brian Morrison II (PlanetScale, 2023-11-15) canonicalises the separate-binlog-disk operational hygiene rule: "By default, these logs are stored on the same disk as the database. As you can imagine, busy databases can be bogged down when you consider the amount of throughput being processed by a single disk (manipulating the database + reading binary logs for replication). That said, the better approach would be to store binary logs on a separate disk than the database. This approach can also save you some money in cloud environments where free volumes have hard IOPS limits." The canonical wiki framing: binlog's pure sequential append I/O workload competes poorly with InnoDB's random read/write workload for a shared volume budget; separation gives each its own IOPS + throughput cap and avoids the [[concepts/linear-vs-superlinear-cost-scaling |gp3 → io1/io2 cloud-pricing cliff]] from Dicken's 2024-08-19 IOPS-cost post. Canonicalised as concepts/separate-binlog-disk as a general-purpose operational primitive. The post also canonicalises the rest of the binlog's operational surface that matters to replication operators at the best-practices altitude: binlog-position is fragile across crash + restore (motivating GTIDs); the binlog is the tail that replicas read, so any interruption of the reader (paused replica, fenced replica in unplanned-failover playbook step 1, or in-[[concepts/mysql-semi-sync-replication |semi-sync]] delays) delays downstream consumption but does not lose data as long as the binlog file is retained.

  • sources/2026-04-21-planetscale-behind-the-scenes-how-schema-reverts-work — the online-DDL application of binlog tailing. Guevara + Noach describe the forward flow (shadow table catches up to production through binlog events filtered through the DDL's column projection) and the inverse flow (post-cut-over, every commit on the new table is tailed from the binlog and applied back to the now-former- production table through the inverse projection, so the old-schema table stays current). Binlog replication is the substrate that keeps the shadow table synchronised in both phases — before cut-over for the forward-direction shadow-table schema change, and after cut-over for the inverse-replication schema revert.

  • — canonical wiki description of the two binlog-replication moments in a VReplication workflow: (1) copy-phase catch-up — the workflow interleaves row-copy cycles with binlog-event catch-up cycles (controlled by the vreplication_copy_phase_duration flag) specifically to stay inside the source's binlog retention horizon; "this regular catchup step is important to ensure that we don't complete the row copy only to then be unable to replicate from where we left off because the source MySQL instance no longer has binary log events that we need as they have been purged, in which case we would be forced to start the entire migration over again." (2) continuous-replication phase — after all rows are copied, each per-shard stream issues COM_BINLOG_DUMP_GTID to the source MySQL with the stream's GTID position, receives binlog events filtered by the destination shard's sharding scheme, applies them on the target PRIMARY tablet, and advances the persisted GTID position on every batch commit.

  • — canonical wiki framing of binlog replication as the public CDC API layer of a sharded MySQL fleet. The post extends binlog-replication from its wiki-canonical role as a "within-Vitess-workflow" substrate (2026-02-16 zero-downtime migrations) to its role as the substrate under a public CDC API surface: VStream's replication phase per shard is COM_BINLOG_DUMP_GTID to the source MySQL, and the gtid: field inside every VGTID entry is a literal MySQL GTID set. The wrapping adds cross-shard coordination but doesn't change the per-MySQL-server binlog contract. Canonical datum that "the sharding layer's CDC API is built on top of the engine-native replication protocol, not beside it."

  • — Brian Morrison II (PlanetScale, 2024-02-02). Canonical wiki framing of binlog replication as the sync substrate under Aurora blue/green deployments. Verbatim: "AWS will configure binlog replication between the two clusters to keep changes synchronized between them." Canonicalises a load-bearing constraint Morrison surfaces: binlog replication's schema-change envelope bounds what blue/green can apply on the green side — "schema changes should be limited to what is currently supported by binlog replication in MySQL." Adds-at-end of table, index creation / deletion replicate safely; mid-table column adds, column renames, type narrowing don't round-trip cleanly across binlog replication between a non-identical blue/green pair. The canonical wiki framing: binlog replication is both the enabler and the constraint of blue/green database deployments — it makes the two-environment sync possible but limits the shapes of changes that can safely use the pattern.

  • — Brian Morrison II (PlanetScale, 2024-01-24). Canonical wiki framing of binlog replication as the load-bearing substrate of traditional MySQL clusters (including PlanetScale) and as the substrate Aurora deliberately bypasses internally but preserves externally. Verbatim: "While Aurora does use the binary log for external replication, AWS has built a closed and proprietary replication system that deviates from the traditional MySQL replication configuration for replicating within an Aurora cluster." Aurora runs patterns/storage-forwarded-redo-log-replication internally (redo-log entries forwarded to distributed storage appliances, not a binlog); the binlog is still exposed for external CDC and cross-system integration. This is the canonical wiki instance of "binlog is the external API, not the internal substrate". Morrison also canonicalises the architectural consequence: traditional binlog replication enables rolling MySQL upgrades and restore-replay backup validation naturally; Aurora's substrate doesn't admit either without substantial extra mechanism.

  • sources/2026-04-21-planetscale-postgres-high-availability-with-cdc — canonical wiki framing of the MySQL binlog as an action log whose combination with per-transaction GTIDs and log_replica_updates=ON re-emission makes every replica a valid CDC resume point. Sam Lambert (PlanetScale CEO, 2025-09-12) verbatim: "MySQL's binary log is an action log. Every transaction carries a GTID. Replicas with log_replica_updates=ON re-emit transactions they apply into their own binlogs, preserving GTID continuity. A CDC connector records the last committed GTID set. On reconnect it tells any suitable server, 'resume from this GTID.' If the binlog containing that GTID still exists, streaming continues with no slot object and no eligibility gate." This is the canonical decoupler mechanism: by embedding consumer-progress metadata in the consumer (GTID cursor) rather than in a server-side object (Postgres slot), and by propagating the action log uniformly via log_replica_updates=ON, MySQL avoids the HA-CDC coupling that the state-log-plus-primary-local-catalog shape in Postgres creates. See patterns/action-log-vs-state-log-replication for the generalised design-space view. Canonical failover semantics: "Promote a replica. Point the connector at any replica and it resumes from it's GTID position." CDC availability is bounded by binlog retention, not by subscriber polling cadence.

Seen in — InnoDB's silent FK cascades as a structural binlog gap

  • — Shlomi Noach + Manan Gupta (PlanetScale, 2023-12-05) canonicalise the binlog is not authoritative for InnoDB-cascaded FK child changes. When a parent-side DELETE/UPDATE fires ON DELETE CASCADE / ON UPDATE CASCADE / SET NULL on child rows, InnoDB applies those child-side changes internally and "are never logged to the binary log." Any binlog consumer (replica with different FK config, CDC tool, snapshot-plus-catchup migration tool) is missing data for cascaded child events. Canonicalised as concepts/innodb-silent-cascade-in-binlog. The load-bearing consequence: Vitess reimplemented FK semantics above MySQL so that all cascaded child writes happen via explicit DMLs visible in the binlog — see patterns/application-level-cascade-orchestration.
Last updated · 542 distilled / 1,571 read