MySQL – The Binary Log

The binary log records every change that modifies data, in order. It is what makes two things possible: replication, and point-in-time recovery — restoring last night's backup and then replaying everything up to the moment before the mistake.

Is it on?

SELECT @@log_bin, @@binlog_format, @@binlog_expire_logs_seconds, @@server_id;

On MySQL 8 the answers are typically 1, ROW, 2592000 (30 days) and 1. Binary logging is on by default in MySQL 8, which was not true of 5.7 — another place where older advice misleads.

SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'binlog.000003' LIMIT 10;

The output is a list of files with sizes. They can get very large — building the lab database for this track produced a single 1.1 GB binlog file — which brings us to expiry, below.

What it does and does not contain

Contains: every INSERT, UPDATE, DELETE and DDL statement that changed something, each with a timestamp and position, grouped by transaction and written at commit.

Does not contain: SELECT (nothing changed), statements that matched no rows, or rolled-back transactions. It is not an audit log of who looked at what — that is the general query log or an audit plugin.

The three formats

The trade is size against safety. A DELETE removing a million rows is one line in STATEMENT and a million row images in ROW. Take the size; correctness on the replica is not negotiable, and non-deterministic statements are commoner than they look.

Reading one

mysqlbinlog /var/lib/mysql/binlog.000004 | less

# ROW format is encoded, so decode it into readable pseudo-SQL
mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/binlog.000004

# just a window of time
mysqlbinlog --start-datetime="2026-08-24 14:00:00" \
            --stop-datetime="2026-08-24 15:00:00" \
            --base64-output=DECODE-ROWS --verbose binlog.000004

# from a container
docker exec pizza-mysql mysqlbinlog --base64-output=DECODE-ROWS --verbose \
    /var/lib/mysql/binlog.000004 | less

--base64-output=DECODE-ROWS --verbose is the incantation to remember. Without it, ROW-format events print as base64 blobs and the file looks useless. With it, each event becomes readable ### UPDATE / ### WHERE / ### SET lines showing the old and new values of every column.

That is how you answer "what exactly did that migration change" and "what did the row look like before someone broke it" — the old values are right there, so a single wrecked row can be repaired by hand from the log.

Point-in-time recovery

The reason the binlog earns its disk space. Someone runs a DELETE without a WHERE at 14:32.

# 1. restore the most recent full backup
mysql -u root pizza < /backups/pizza-2026-08-24.sql

# 2. find the moment. Look for the statement itself.
mysqlbinlog --base64-output=DECODE-ROWS --verbose binlog.000004 | grep -n -B5 'DELETE FROM'

# 3. replay everything from the backup up to just BEFORE it
mysqlbinlog --start-position=154 --stop-position=98237 binlog.000004 | mysql -u root pizza

Positions are exact and timestamps are not — several transactions can share a second — so use --stop-position for the real thing and --stop-datetime only to narrow the search. Get the start position from the backup: mysqldump --master-data=2 writes it into the dump as a comment, which is the whole reason to use that flag.

Two things that make this work on the day: the backup and the binlogs must be on different storage from the database (a lost disk otherwise takes both), and you must have rehearsed it. Point-in-time recovery is fiddly, and the first attempt should not be during an incident.

Disk, and the thing that fills it

SELECT @@binlog_expire_logs_seconds / 86400 AS expire_days;

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;   -- safe: respects replicas
rm /var/lib/mysql/binlog.000004                    # ← never do this

Never delete binlog files by hand. The server keeps an index file; removing a log behind its back leaves that index inconsistent, and any replica still reading that file breaks permanently and has to be rebuilt. PURGE BINARY LOGS does it properly.

Set the retention deliberately: it must cover the gap between full backups plus enough margin to notice a problem, and it must exceed how long a replica can be offline. 30 days is a common default and is a lot of disk on a busy server.

The other thing it is used for

Change data capture. Tools like Debezium read the binlog as a replica would and turn every row change into an event on a stream — feeding a search index, a cache, a warehouse, or an audit trail, without touching the write path. No triggers, no dual writes, no application changes. It is the modern answer to "keep this other system in step with the database", and it works precisely because the binlog is already a complete, ordered record of every change.

What to remember

• On by default in MySQL 8, in ROW format. Keep both.
• mysqlbinlog --base64-output=DECODE-ROWS --verbose, or the file looks like noise.
• Backup + binlog replay is point-in-time recovery. Rehearse it before you need it.
• Positions are exact; timestamps are not.
• PURGE BINARY LOGS, never rm.