Lesson 0008 · Internals & Performance

Replication & the binary log

A second log — logical this time — ships every committed change to other servers. Whether a failover can lose your commit comes down to one word: async.

~11 minRetrieval quiz + hands-on inspectionCheat sheet: here

In Lesson 0007 the redo log made a commit survive a crash on one server. This capstone lesson scales that question out: how does a change reach other servers — read replicas, failover standbys? The answer is a different log for a different job: the binary log.

The one idea: the binary log is a logical change feed

Two logs, don't confuse them

The binary log (binlog) records every committed change as a logical event (the row change or statement), at the server layer — separate from InnoDB's redo log. Its jobs are replication (feed the same changes to other servers) and point-in-time recovery. Contrast the redo log from 0007: physical, InnoDB-internal, and only for crash recovery on the same machine. Redo heals one server; the binlog copies one server to another.

 redo log (0007)binary log (0008)
LayerInnoDB engineServer (all engines)
Contentphysical page changeslogical row/statement changes
Purposecrash recovery (one server)replication & PITR (across servers)
Lifetimecircular, reused after checkpointretained for replicas / backups

How a change reaches a replica

Replication is binary-log based: the replica pulls the source's binlog and replays it, through two threads. — MySQL Manual: replication is "based on replicating events from the source's binary log."

SOURCE REPLICA ┌──────────────┐ pull binlog ┌───────────────────────────┐ │ writes binlog│ ────────────────▶ │ I/O (receiver) thread │ └──────────────┘ │ → writes RELAY LOG │ │ SQL (applier) thread │ │ → applies to replica DB │ └───────────────────────────┘

The receiver (I/O) thread copies binlog events into the replica's relay log; the applier (SQL) thread executes them against the replica's data. Reads can then fan out across replicas while writes stay on the source. — MySQL Manual — Replication Threads

The trade-off that matters: async vs semisync

By default replication is asynchronous: the source commits and returns to the client without waiting for any replica. Fast — but it has a sharp edge. — MySQL Manual: "Replication is asynchronous by default."

The async data-loss window

Because the source doesn't wait, a transaction can be committed and acknowledged to your app, then the source crashes before the binlog reaches any replica. Promote a replica and that acknowledged commit is gone. Async replication means a failover can lose committed data — the distributed version of the durability question from 0007.

Semisynchronous replication narrows that window: a commit on the source blocks until at least one replica acknowledges it has received the transaction's events (written and flushed to that replica's relay log) — not that it has applied them. — MySQL Manual: "a thread that performs a transaction commit on the source blocks and waits until at least one semisynchronous replica acknowledges that it has received all events for the transaction… it requires only an acknowledgement from the replicas, not that the events have been fully executed."

The guarantee, stated precisely

Semisync guarantees that if the source crashes, every committed transaction has reached at least one replica — so a failover won't silently lose it. The cost is added commit latency (one network round-trip). Note the subtlety: "received" means in the relay log, not yet applied — so a replica may still be a moment behind on applying even though it has safely received the change.

Check yourself

Answer from memory — effortful recall is what builds retention. Feedback is immediate.

The binary log's two jobs are:

The binlog feeds replicas and enables point-in-time recovery. Crash recovery is the redo log's job (0007); rollback is the undo log's.

Compared with the binary log, the redo log is:

Redo is physical page changes inside InnoDB for same-server crash recovery. The binlog is logical, at the server layer, for cross-server replication and PITR.

MySQL replication is, by default:

By default the source commits and returns without waiting for any replica — asynchronous replication. Semisync must be explicitly enabled.

On the replica, the binlog is copied to the relay log by the:

The receiver (I/O) thread pulls binlog events into the relay log; the applier (SQL) thread then executes them against the replica's data.

With asynchronous replication, a source failover can:

If the source crashes before the binlog reaches a replica, an already-acknowledged commit is lost when a replica is promoted. That's the async data-loss window.

Semisync makes the source's commit wait until a replica has:

Semisync waits for acknowledgement of receipt (events written to the replica's relay log), not for the replica to apply them — bounding data loss at one round-trip of latency.

A committed change survives a single-server crash via the:

Same-server crash recovery is the redo log (0007). The binlog is for replication/PITR, not local crash recovery — don't conflate the two logs.

Hands-on: inspect the binary log

You can explore the binlog on your single server — no replica needed.

-- 1) Is binary logging on? (default ON in 8.0)
SHOW VARIABLES LIKE 'log_bin';

-- 2) The binlog files and the current position
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;   -- (SHOW MASTER STATUS on older 8.0.x)

-- 3) Make a change, then read the events it logged
UPDATE people SET city = 'BINLOG' WHERE id = 1;
SHOW BINLOG EVENTS IN 'binlog.000001' LIMIT 20;   -- use your filename from step 2

-- 4) The row-vs-statement format that shapes those events
SHOW VARIABLES LIKE 'binlog_format';   -- ROW (default), STATEMENT, or MIXED
What you should observe

Each committed transaction appears as a group of events in the binlog, tagged with the position a replica would resume from. With binlog_format = ROW (the safe default) you'll see the actual row images that changed — the logical feed a replica replays. On a real replica, SHOW REPLICA STATUS\G would show the receiver/applier threads and the replication lag. Bring me a SHOW BINARY LOG STATUS and we'll read the position.

Primary source — read this next

MySQL 8.0 Reference Manual — Replication, then Semisynchronous Replication for the async-vs-semisync trade-off, and The Binary Log for formats and PITR.

You've completed the core arc

Eight lessons, three pillars: indexing & query planning (0001–0004), concurrency (0005–0006), and durability & replication (0007–0008). You can now reason about a MySQL system from the clustered index up to a replica topology — the depth the mission set out to build. From here: bring real problems — a slow EXPLAIN, a deadlock, a replication lag graph — and we'll go deeper on whichever the job demands.

Cheat sheet → Glossary → ← Lesson 0007 All lessons
I'm your teacher — want to go deeper on GTID-based failover, read-scaling with replica lag, or point-in-time recovery from a binlog? Or practice against a real interview question? Just ask.