Mastering MySQL Binlog: Complete Guide to Replication, Recovery, and Auditing

Introduction: What is Binlog?

MySQL Binary Log (Binlog) is a logical log that records every change made to the database, including both DDL and DML events.

It resides in the server layer and does not depend on the storage engine.

Core roles :

Replication (master‑slave data sync)

Point‑in‑time recovery (PITR)

Audit / Change Data Capture (CDC)

One‑sentence summary : Without Binlog, MySQL cannot provide replication or precise recovery.

1. Core Functions of Binlog

1.1 Replication

Master writes Binlog → I/O thread on replica pulls → Relay Log → SQL thread replays.

Ensures data consistency between master and replicas.

1.2 Point‑in‑time Recovery (PITR)

Restore full backup first, then replay Binlog for the required time range.

Allows “time travel” to roll back accidental operations.

1.3 Audit & CDC

Tools such as mysqlbinlog, Canal, Debezium parse Binlog to track who did what and when.

In modern architectures Binlog often serves as a real‑time data source for Kafka, Elasticsearch, ClickHouse, etc.

2. Binlog Formats (binlog_format)

STATEMENT (SBR)

Records the original SQL statements.

Pros: small log size.

Cons: nondeterministic functions (NOW(), UUID()) can cause inconsistency.

Status: nearly deprecated.

ROW (RBR)

Records before/after images of each row change.

Pros: best consistency.

Cons: larger log volume.

Status: default in MySQL 5.7+ and recommended for production.

MIXED

Uses STATEMENT for deterministic statements and ROW for others.

Pros: balances size and safety.

Cons: behavior can be unpredictable.

Status: ROW is now more common.

3. Underlying Mechanisms & Key Parameters

sync_binlog=1 : forces a disk flush for every transaction commit.

innodb_flush_log_at_trx_commit=1 : forces Redo Log flush on each commit.

These “double‑1” settings sacrifice performance for zero data loss.

3.1 Two‑Phase Commit (2PC)

InnoDB writes Redo Log (Prepare).

Binlog is written and flushed.

Redo Log is marked Commit, guaranteeing consistency between Redo Log and Binlog after a crash.

3.2 GTID (Global Transaction Identifier)

Format: server_uuid:transaction_id.

Advantages: replication no longer relies on log_file + pos, avoids duplicate execution.

Strongly recommended to enable GTID on MySQL 5.7+.

3.3 Binlog Cleanup

Old versions use expire_logs_days; newer versions use binlog_expire_logs_seconds.

Manual cleanup example: PURGE BINARY LOGS TO 'mysql-bin.000010'; Never delete Binlog files with rm; always use SQL commands.

4. Advanced Applications

4.1 Relay Log vs. Binlog

Binlog : generated on the master.

Relay Log : local copy pulled by the replica; useful for diagnosing replication lag.

4.2 Large Transactions & Replication Lag

A massive DELETE (e.g., 10 million rows) creates a huge Binlog and can cause replica lag.

Recommendation: split into smaller transactions.

4.3 Delayed Replication

Replica applies Binlog with an N‑second delay.

Useful for protecting against accidental deletions.

4.4 DDL and Binlog

DDL statements are recorded as Statement Events.

MySQL 8.0’s atomic DDL greatly reduces replication risk.

4.5 Binlog Compression

MySQL 8.0.20 introduced Transaction Compression, significantly reducing log size for large transactions.

4.6 Binlog Encryption

Supported in MySQL 8.0+ via binlog_encryption=ON, suitable for compliance‑heavy environments (finance, healthcare).

4.7 Parallel Replication

MySQL 5.6: database‑level parallelism.

MySQL 5.7/8.0: transaction‑dependency based parallelism.

Configure with slave_parallel_workers > 0.

4.8 Group Replication & Binlog

Group Replication/InnoDB Cluster requires ROW mode + GTID.

Binlog participates in the group’s consistency protocol.

5. Operations & Troubleshooting

5.1 Common Monitoring Metrics

Binlog file size and growth rate. Seconds_Behind_Master (replication lag).

Performance schema counters: binlog_cache_use, binlog_cache_disk_use.

5.2 Typical Failure Scenarios

Replication lag caused by large transactions, network bottlenecks, or replica resource limits.

Replication breakage due to schema mismatch or nondeterministic SQL.

Disk exhaustion because Binlog is not cleaned up promptly.

5.3 Tool: mysqlbinlog

View a Binlog file: mysqlbinlog mysql-bin.000001 Parse to SQL with details: mysqlbinlog -vv /path/to/binlog Recover by time range:

mysqlbinlog --start-datetime="2025-09-18 09:00:00" \
    --stop-datetime="2025-09-18 10:00:00" \
    /path/to/binlog | mysql -u root -p

Recover by position:

mysqlbinlog --start-position=123 --stop-position=456 \
    /path/to/binlog | mysql -u root -p

6. Best‑Practice Summary

Use ROW format universally in production.

For financial‑grade consistency, enable the “double‑1” configuration.

Enable GTID for new environments.

Backup strategy: full backup + Binlog incremental recovery; practice restores regularly.

Operations: set Binlog expiration or schedule regular cleanup; monitor growth to avoid disk full.

Conclusion

Binlog is MySQL’s most critical “black box.” It underpins replication, precise recovery, auditing, and real‑time data pipelines. Mastering its formats, parameters, and tooling gives you full control over MySQL’s reliability and scalability.