✏️ Explanatory Question
Level: Hard — The binlog underpins both replication and point-in-time recovery; the format choice has real correctness implications.
Scenario: Your database crashed at 3:15 PM. Your last full backup was at 2:00 AM. The interviewer asks: "How do you recover the data written between 2 AM and 3:15 PM that is not in the backup?" The answer is the binary log — and understanding it is essential for both recovery and replication.
The binary log (binlog) is an ordered record of every change that modifies data (INSERT, UPDATE, DELETE, DDL) — but not plain SELECTs. Each change is written as an event with a position. It serves two critical purposes:
-- Is binary logging on? (default ON in MySQL 8.0)
SHOW VARIABLES LIKE 'log_bin';
-- List binlog files
SHOW BINARY LOGS;
-- See the current position (used to set up replicas / PITR)
SHOW MASTER STATUS;
-- View events in a binlog file
SHOW BINLOG EVENTS IN 'mysql-bin.000042' LIMIT 10;# Convert binlog events back into readable SQL
mysqlbinlog mysql-bin.000042
# Extract only events in a time window (for point-in-time recovery)
mysqlbinlog --start-datetime="2026-08-02 02:00:00" \
--stop-datetime="2026-08-02 15:15:00" \
mysql-bin.000042 mysql-bin.000043 > recover.sql| Format | Logs | Pros / Cons |
|---|---|---|
| STATEMENT (SBR) | The actual SQL statement | Compact logs; but unsafe for non-deterministic functions |
| ROW (RBR) | The before/after row images | Accurate & safe (the default); larger logs |
| MIXED | Statement, switching to row when unsafe | Balance of size and safety |
Why STATEMENT can corrupt replicas: A statement like UPDATE users SET token = UUID() or ... WHERE created < NOW() is non-deterministic — it produces different results when replayed on the replica at a different time. ROW-based logging avoids this by logging the exact resulting row changes, which is why it is the modern default.
-- Check and set the binlog format
SELECT @@binlog_format; -- ROW (default in 8.0)
SET GLOBAL binlog_format = 'ROW'; -- persists in my.cnf for restart safety
-- Related durability setting for crash safety
SELECT @@sync_binlog; -- 1 = flush binlog to disk on every commit (safest)PURGE BINARY LOGS BEFORE '2026-07-01';binlog_expire_logs_seconds (e.g., 7 days). Interviewer follow-up: "ROW format makes binlogs huge on a big batch UPDATE — how do you manage that?" → Options: use binlog_row_image = minimal to log only changed columns + key (smaller), enable binlog compression (MySQL 8.0.20+), keep transactions/batches small, and purge/expire logs on a schedule. You trade some size for the correctness guarantees ROW provides — usually worth it.