Why Did MySQL Binlog Balloon to 4 GB? A Deep Dive into Replication Failures

1. Introduction

A production MySQL project encountered a replication failure that halted project acceptance; the master‑slave binlog had grown to 4 GB, far exceeding the expected 300 MB limit, prompting a detailed investigation.

2. Replication Principle

The master writes all SQL statements to the binary log. The slave starts an I/O thread that requests the binary log from the master, receives new entries, and stores them in its relay log. A separate SQL thread then reads the relay log and replays the statements to keep data consistent.

3. Binlog Write Mechanism

During a transaction MySQL first writes to a binlog cache; when the transaction commits the cache is flushed to the binlog file. Each thread gets a dedicated memory block for the cache, and the entire transaction is written atomically.

"write" stores data in the page cache, which is fast because it is not yet persisted to disk.

"fsync" forces the data to be flushed to disk, guaranteeing durability.

MySQL’s default max_binlog_size is 250 MB, but large transactions can cause a single binlog file to exceed this limit.

4. Inspecting Binlog

The mysqlbinlog utility is used to examine binlog contents. Example commands:

./mysqlbinlog --no-defaults --base64-output=decode-rows -vv /mysqldata/mysql/binlog/mysql-bin.004816|more

To list the byte size of each transaction:

./mysqlbinlog --no-defaults --base64-output=decode-rows -vv /mysqldata/mysql/binlog/mysql-bin.004816 \
| grep GTID -B1 | grep '^# at' | awk '{print $3}' \
| awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp, tmp, $1); tmp=$1}' \
| sort -n -r | more

The largest transaction occupied 32 MB; over 200 transactions exceeded 10 MB, together approaching 4 GB.

./mysqlbinlog --no-defaults --base64-output=decode-rows --start-position='xxxx' --stop-position='xxxxx' -vv /mysqldata/mysql/binlog/mysql-bin.004816 \
| grep '^# at' | awk '{print $3}' \
| awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp, tmp, $1); tmp=$1}' \
| sort -n -r | more

Finally, to view the offending SQL statements:

./mysqlbinlog --no-defaults --base64-output=decode-rows --start-position='xxxx' --stop-position='xxxxx' -vv /mysqldata/mysql/binlog/mysql-bin.004816 \
| grep '^# atxxxx' -C5 | grep -v '###' | more

The analysis revealed a table with a large BLOB column; a massive import transaction repeatedly updated the row’s timestamp, causing the row‑based binlog to record the entire BLOB each time.

5. Binlog Modes

MySQL supports three binlog formats:

STATEMENT – logs only the SQL statement. Advantages: smaller logs, less I/O. Disadvantages: cannot replicate certain functions or statements that depend on row‑level context.

ROW – logs the actual row changes. Advantages: precise replication, works with triggers, functions, and stored procedures. Disadvantages: generates large logs because every column of the modified row is recorded.

MIXED – combines both; uses STATEMENT when safe, otherwise falls back to ROW (available from MySQL 5.1.8).

6. Conclusion

The root cause was a large transaction that updated a row containing a 3 GB BLOB field while using row‑based replication, inflating the binlog to 4 GB. The problem was solved by refactoring the schema: moving the BLOB to a separate table and avoiding massive transactions. The lesson is to design databases with smaller, isolated transactions and consider BLOB placement to keep binlog size manageable.