Part 154: MySQL Binary Logging (Binlog) Formats & Replication
Understanding how MySQL replicates state across replicas and disaster recovery targets.
1. What is the MySQL Binlog?
Unlike the InnoDB Redo Log (which is an internal storage-engine crash recovery log), the Binary Log (Binlog) is managed at the MySQL server layer: - Records all DDL and DML statements that modify database data or structure. - Powers asynchronous and semi-synchronous replica nodes. - Serves as the event feed for Change Data Capture (CDC) systems like Debezium.
2. Binlog Formats: ROW vs STATEMENT vs MIXED
-- Check current binlog format
SHOW VARIABLES LIKE 'binlog_format';
| Format | Mechanism | Pros & Cons |
|---|---|---|
STATEMENT |
Records the raw SQL text (UPDATE users SET status = 'active') |
Minimal log volume. Unsafe for non-deterministic functions (NOW(), UUID(), RAND()). |
ROW (Modern Standard) |
Records the exact before and after row byte values | 100% deterministic and safe. Higher disk usage for large bulk updates. |
MIXED |
Uses STATEMENT by default; switches to ROW for non-deterministic queries |
Good compromise, but modern production favors pure ROW. |
3. Replication Data Flow
[ Primary Node ]
└─ Writes transaction to Binlog
│ (Binlog Dump Thread)
▼
[ Replica Node ]
├─ I/O Thread: reads Binlog stream -> writes to Relay Log
└─ SQL Thread: reads Relay Log -> executes changes locally
Key Takeaways
- Always configure
binlog_format = ROWin production for reliable replication and CDC integration. - Monitor
Seconds_Behind_Masteron replica nodes to catch replication lag early.
In Part 155, we explore SQL Server (T-SQL) In-Memory OLTP & Memory-Optimized Tables!