Binary Logs: The Heartbeat of MySQL Replication and Recovery
Binary logs are one of the most important — and most overlooked — parts of MySQL.
They are the record of every change that has happened to the database. Without them, you have no replication. Without them, you have no point-in-time recovery.
If they fill the disk, MySQL stops writing. If they expire too fast, replicas fall behind and die silently.
This script gives you a fast operational picture of binary log health.
Loading…
Reading the Results
Step 1 — Variables
| Variable | What to Check |
|---|---|
log_bin | Must be ON — if OFF, there is no replication and no PITR |
binlog_format | Should be ROW for most modern setups |
expire_logs_days | Legacy retention setting (MySQL < 8.0) |
binlog_expire_logs_seconds | Modern retention setting (MySQL 8.0+) — e.g. 604800 = 7 days |
On MySQL 8.0+,
binlog_expire_logs_secondstakes precedence overexpire_logs_days.
Step 2 — SHOW MASTER STATUS
| Column | What It Tells You |
|---|---|
File | The active binary log file being written to |
Position | The byte offset within that file — grows with every write |
Binlog_Do_DB | Databases explicitly included (blank = all) |
Binlog_Ignore_DB | Databases explicitly excluded |
On a busy server, the position advances rapidly. On an idle server, it barely moves. If it never moves at all, nothing is being written — which may indicate a problem.
Step 3 — SHOW BINARY LOGS
Returns all binary log files on disk with their sizes. Use this to count how many log files exist, estimate total disk consumption, and spot unusually large files (may indicate a big bulk operation).
What to Watch For
| Signal | What It Means |
|---|---|
log_bin = OFF | Binary logging disabled — no replication, no PITR |
| Very high log file count | Retention too long, or server very busy |
| Single log file with enormous size | A large bulk load happened — normal, but worth noting |
| Position not advancing | Server idle, or writes being suppressed |
Very low expire_seconds (e.g. 3600) | Replicas could fall behind faster than logs are kept |
Gareth Winterman