The binary log is the server-level log in MySQL and MariaDB that records every committed change to data and schema as a sequence of events, in commit order. In the backup and recovery of relational databases, it is the component that allows a database to be restored not only to the moment of the last backup but to any later point in time.
The same log also feeds replication: replicas read binary log events from the primary and apply them. Because it serves both purposes, its format, retention and durability settings directly decide how much data can be recovered after a failure.
How the binary log records changes
When a transaction commits, the server writes its events to the current binary log file, such as binlog.000123, and lists every file in an index file. When a file reaches max_binlog_size, typically 1 GB, or the server restarts, a new numbered file begins. Each event carries a position and, when enabled, a global transaction ID (GTID).
The binary log is distinct from the storage engine’s redo log. The redo log protects InnoDB pages for crash recovery inside one server, while the binary log is a logical, engine-independent record used to rebuild or copy changes elsewhere. The server coordinates both through an internal two-phase commit so they never disagree about which transactions committed.
Binary log formats
The binlog_format setting decides what an event contains: the SQL statement, the changed rows, or a mix. The choice affects log size, replication safety and how useful the log is for auditing or recovery.
| Format | What is logged | Advantages | Drawbacks |
|---|---|---|---|
| STATEMENT | The SQL statement text | Small log for bulk updates | Non-deterministic statements replay differently |
| ROW | Before and after images of changed rows | Exact and safe replay | Large log for mass updates |
| MIXED | Statements, switching to rows when unsafe | Balance of size and safety | Harder to predict log content |
ROW format is the recommended default in current releases because it replays exactly the same data regardless of functions such as NOW() or UUID(). Setting binlog_row_image = MINIMAL reduces its size by logging only the key and changed columns.
The binary log in point-in-time recovery
A full backup captures the database at one moment; the binary log covers everything after it. Point-in-time recovery restores the latest backup and then replays binary log events up to a chosen time or position, usually just before a destructive statement.
- Restore the most recent database dump or snapshot taken with its binary log coordinates.
- Identify the stop point, for example the position of an accidental
DROP TABLE, by inspecting the log. - Replay events from the backup coordinates to the stop point with
mysqlbinlogormariadb-binlog. - Optionally skip the harmful event and continue replaying later events.
This only works if every binary log file since the backup is still available. A recovery chain is as long as its shortest retained file, so retention must exceed the interval between full backups, with margin.
| Component | What it captures | Role in recovery |
|---|---|---|
| Database dump | Logical copy at one moment | Starting point for replay |
| Database snapshot | Storage-level image at one moment | Fast starting point for large databases |
| Binary log | All committed changes since then | Roll forward to a chosen point |
| InnoDB redo log | Recent page changes on one server | Crash recovery only, not restore |
Settings that control durability and retention
A binary log protects data only if it is flushed reliably and kept long enough. The table lists the settings that matter most.
| Setting | Purpose | Common choice |
|---|---|---|
| log_bin | Enables the binary log | On for any server needing recovery |
| sync_binlog | Flush to disk every N commits | 1 for full durability |
| binlog_expire_logs_seconds | Automatic purge age | 7-14 days, longer than backup cycle |
| max_binlog_size | Size before rotating to a new file | Default of 1 GB |
| gtid_mode or gtid_domain_id | Transaction identifiers | Enabled for simpler failover |
With sync_binlog = 0 or a value above 1, a power failure can lose the last binary log events even when InnoDB kept the transactions, leaving replicas and point-in-time recovery out of step with the data.
Common mistakes
- Deleting binary log files manually from disk instead of using
PURGE BINARY LOGS, which corrupts the index. - Keeping the logs only on the database server, so a disk failure destroys both the data and the recovery chain.
- Choosing STATEMENT format and later finding that replicas or restores produce different data.
- Setting a retention period shorter than the backup interval, which breaks point-in-time recovery silently.
- Forgetting that the log contains every changed value, including personal data, and leaving it unencrypted.
The idea in brief
The binary log is like a diary in which the database notes every change it makes, in the exact order it happened. A backup is a photograph of the database on one day; the diary covers everything that happened afterward.
If something goes wrong, an administrator can restore the photograph and then replay the diary up to the minute before the mistake. The same diary is also what copies of the database read to stay in sync.
A practical scenario
A company takes a nightly logical backup at 02:00 of its MySQL order database and keeps binary logs in ROW format for 14 days, copied every five minutes to separate storage. At 15:42, a developer runs a DELETE without a WHERE clause on the customer table.
The administrator restores the 02:00 backup to a recovery server, reads the binary log with mysqlbinlog to find the exact position of the harmful DELETE, and replays all events up to that position. The customer table is recovered with every change made between 02:00 and 15:41, and the restored rows are copied back to production. Data loss is limited to seconds instead of a full working day.