MySQLintermediate

MySQL Binary Logs

What the binary log contains, why row format is the only sensible choice, and how retention affects replication and recovery.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

MySQL MariaDB

The binary log records every change to the data, in commit order. It is the source for replication, for point-in-time recovery, and for change data capture.

It is distinct from InnoDB's redo log: redo is physical and internal to the storage engine, binlog is logical and lives at the server level. Coordinating both is why durability needs two settings.

Configuration

log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1
binlog_expire_logs_seconds = 604800     # 7 days
max_binlog_size = 1G

binlog_format = ROW logs the actual row images rather than the statement. Statement-based logging replicates non-deterministic statements incorrectly (NOW(), UUID(), LIMIT without ORDER BY) and is unsafe under READ COMMITTED. Row format is the default in MySQL 8.0 and the only format CDC consumers can use reliably.

binlog_row_image = FULL logs before and after images of every column. MINIMAL saves space but omits data most CDC consumers need — decide based on whether anything downstream reads the before-image.

sync_binlog = 1 fsyncs the binlog at commit. With 0, a host crash can lose transactions from the binlog that InnoDB has already committed, leaving replicas permanently behind the primary in a way replication cannot repair.

Retention

Retention determines two independent things:

How long a replica can be down and still catch up. Beyond that, it must be rebuilt from a backup.
How far back point-in-time recovery can reach from the last full backup.

Do not purge binary logs a replica still needs

Purging logs a replica has not consumed breaks replication irrecoverably for that replica — it must be rebuilt. Check replica positions before purging, and prefer expiry by time over manual purging.

SHOW BINARY LOGS;
SELECT * FROM performance_schema.replication_connection_status;
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

Balance against disk: binlogs on a busy server can grow by hundreds of gigabytes a day. Monitor their total size as its own metric, because filling the disk stops the server.

Reading the binlog

# Human-readable decode of row events.
mysqlbinlog --verbose --base64-output=DECODE-ROWS \
  /var/log/mysql/mysql-bin.000042 | less
 
# Extract a time range for point-in-time recovery.
mysqlbinlog --start-datetime="2026-07-30 14:00:00" \
            --stop-datetime="2026-07-30 14:22:00" \
            mysql-bin.0000{42,43} > /tmp/replay.sql

GTIDs

With gtid_mode = ON, every transaction carries a globally unique identifier. This is what makes failover practical: a replica repointed at a new primary determines what it has already applied from its GTID set, instead of needing an exact file-and-position. See GTID.