MySQL vs MariaDB: binlog disk explosion—how to keep it under control

Binlogs: why they explode (and why you wanted them)

The MySQL/MariaDB binary log is an append-only record of changes. Depending on configuration it stores statements, row images, or both-ish (MIXED). It feeds replication and it enables point-in-time recovery. It also grows by definition: it’s a journal. If you don’t explicitly manage it, it will keep writing until the filesystem forces a life lesson.

Most “binlog disk explosion” incidents have a surprisingly small set of root causes:

• Retention was never configured, so logs are effectively infinite.
• Retention exists but cannot take effect, because a replica (or backup tool) still needs old logs.
• Write amplification from ROW format, large transactions, or bulk updates creates huge binlogs.
• Replica downtime forces the primary to retain an ever-growing backlog.
• Misplaced assumptions about what “purge” does, what GTID changes, or what your backup tool reads.

One operational quote you should treat like a law of physics:

“Hope is not a strategy.” — Gene Kranz

Binlog management is not “set it and forget it.” It’s “set it and monitor it,” with a retention policy that matches business recovery goals and the reality of your replication topology.

Interesting facts and a little history (because context prevents mistakes)

• Fact 1: MySQL’s binlog predates modern “CDC” marketing. It was a pragmatic replication mechanism long before “event streams” were cool.
• Fact 2: Statement-based replication was the original default; it was smaller but vulnerable to non-determinism (think NOW(), RAND(), and “depends on data” queries).
• Fact 3: Row-based logging became the practical default in many environments because it’s deterministic, but it can balloon in size on mass updates.
• Fact 4: GTID (Global Transaction ID) was introduced to make replication failover saner; it didn’t magically remove the need for binlog retention discipline.
• Fact 5: MariaDB diverged from MySQL and added its own GTID implementation; the operational semantics aren’t identical, especially around naming and state variables.
• Fact 6: “Expire logs” options have changed names over the years in MySQL, and older blog posts frequently mislead people running newer versions.
• Fact 7: Many backup systems that claim “no-lock” backups still rely on binlogs to get consistency across tables; deleting logs out from under them is a classic self-own.
• Fact 8: Relay logs on replicas can also explode; people blame the primary’s binlogs while the replica quietly hoards its own backlog.

Joke #1: Binlogs are like receipts. You don’t need them forever, but you’ll regret shredding them five minutes before an audit.

MySQL vs MariaDB: the differences that matter for binlog growth

At a distance, binlog management looks the same: set retention, make sure purging is safe, watch replication. Up close, MySQL and MariaDB have enough differences to ruin your day if you copy-paste advice across them.

1) Retention knobs: similar intent, different names and edge cases

MySQL historically used expire_logs_days. Newer MySQL favors binlog_expire_logs_seconds. Many systems still have both in configs because nobody likes deleting old lines.

MariaDB supports expire_logs_days as well, and also supports binlog_expire_logs_seconds in newer releases. But the effective behavior can vary with version and with how you run replication and backups. Don’t guess; verify via SHOW VARIABLES and by observing actual purges.

2) GTID behavior: the concept overlaps; the operational details don’t

MySQL GTID is integrated tightly with replication state (gtid_executed, gtid_purged) and generally tries to make failover tooling consistent. MariaDB’s GTID is similar in spirit but differs in representation and some failover workflows.

For binlog retention, the key point is blunt: GTIDs do not reduce disk usage by themselves. They reduce the cognitive load of “what did we apply where,” which helps you safely remove logs, but you still need a retention policy and safe purge procedures.

3) Default settings and “safe by default” varies by distro

Packaged configs in enterprise environments sometimes ship with binlogging enabled and no expiration configured because “replication might be needed later.” That’s not caution; it’s deferred failure.

4) Binlog compression and encryption: good features, but not free

Depending on versions, you may have options for binlog encryption and compression. Encryption increases CPU overhead; compression trades CPU for disk and I/O reduction. Both are worth considering, but only after you have fundamentals (retention + replication health) under control.

Fast diagnosis playbook: find the bottleneck in minutes

This is the “you have 10 minutes before the disk fills” playbook. Don’t debate architecture while the server is paging.

First: confirm it’s binlogs and quantify growth rate

• How much disk is left?
• How big is the binlog directory?
• Are new binlog files being created rapidly, or is one file growing fast?

Second: check whether purging is blocked by replication or tooling

• Is any replica lagging badly or offline?
• Is a backup process reading old binlogs?
• Is the server configured with retention, and is it actually purging?

Third: decide the least-worst emergency action

• If replication is healthy, purge safely based on replica position/GTID.
• If replication is unhealthy, either fix the replica quickly, or accept you’ll break it and plan a rebuild.
• If you’re out of disk now, buy time: expand filesystem, move binlogs, or temporarily stop writes (application throttle) while you regain control.

The tactical priority is always the same: avoid a crash that corrupts tables and turns a manageable incident into a restoration event.

Practical tasks (with commands): measure, decide, fix

You can’t manage what you don’t measure, and you can’t safely purge what you don’t understand. Here are concrete tasks you can run on real servers. Each task includes: command, sample output, what it means, and the decision you make.

Task 1: Check disk headroom and which filesystem is at risk

cr0x@server:~$ df -hT
Filesystem     Type   Size  Used Avail Use% Mounted on
/dev/nvme0n1p2 ext4   450G  430G   20G  96% /
/dev/nvme1n1p1 xfs    1.8T  1.1T  700G  62% /var/lib/mysql

Meaning: You’re close to full on / but MySQL lives on a different mount with headroom. If binlogs are on /, you’re minutes from pain.

Decision: Confirm MySQL’s datadir and where binlogs are written; move them off the root filesystem if needed.

Task 2: Find where binlogs actually live (don’t assume)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'log_bin%'; SHOW VARIABLES LIKE 'datadir';"
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_bin       | ON                        |
| log_bin_basename | /var/lib/mysql/binlog   |
| log_bin_index | /var/lib/mysql/binlog.index |
+---------------+---------------------------+
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| datadir       | /var/lib/mysql/    |
+---------------+--------------------+

Meaning: Binlogs are under /var/lib/mysql. Good. If you saw something like /var/log or a small root mount, you’d have your smoking gun.

Decision: If binlog path is on a constrained filesystem, plan a relocation using config and a restart (or reconfigure if supported), not a midnight symlink stunt unless it’s life-or-death.

Task 3: Identify binlog directory size and biggest files

cr0x@server:~$ sudo du -sh /var/lib/mysql/binlog* 2>/dev/null | sort -h | tail -n 5
1.0G    /var/lib/mysql/binlog.000812
1.0G    /var/lib/mysql/binlog.000813
1.0G    /var/lib/mysql/binlog.000814
1.0G    /var/lib/mysql/binlog.000815
812G    /var/lib/mysql/binlog.index

Meaning: The binlog.index line in this output is misleading because du counts referenced blocks weirdly for sparse-ish patterns and metadata; the real story is that many binlog files exist and each is ~1G.

Decision: Count files and compute total size with a more precise tool (next task). If you’re generating many 1G files quickly, rotation is happening; growth is still real.

Task 4: Count binlog files and total bytes precisely

cr0x@server:~$ sudo find /var/lib/mysql -maxdepth 1 -type f -name 'binlog.*' -printf '%s\n' | awk '{sum+=$1} END{printf "files=%d total=%.2fG\n", NR, sum/1024/1024/1024}'
files=815 total=812.45G

Meaning: You’re holding ~812G of binlogs. That’s not “a little backlog,” that’s an organizational decision that nobody made on purpose.

Decision: Immediately determine whether replicas or backup workflows require that history. If not, purge safely. If yes, fix the blocker or accept a replica rebuild.

Task 5: Confirm binlog retention variables (what the server thinks)

cr0x@server:~$ mysql -e "SHOW VARIABLES WHERE Variable_name IN ('expire_logs_days','binlog_expire_logs_seconds','sync_binlog','binlog_format','binlog_row_image');"
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| binlog_expire_logs_seconds| 0         |
| binlog_format             | ROW       |
| binlog_row_image          | FULL      |
| expire_logs_days          | 0         |
| sync_binlog               | 1         |
+---------------------------+-----------+

Meaning: Retention is effectively disabled. Also, ROW + FULL can be huge for wide tables and update-heavy workloads. sync_binlog=1 is durable but can amplify I/O.

Decision: Set a retention value that matches your recovery objectives and replication reality. Consider binlog_row_image=MINIMAL where safe and supported, but validate application and replication requirements first.

Task 6: See what binlogs exist from MySQL’s perspective

cr0x@server:~$ mysql -e "SHOW BINARY LOGS;" | tail -n 6
| binlog.000810 | 1073741961 |
| binlog.000811 | 1073741982 |
| binlog.000812 | 1073741991 |
| binlog.000813 | 1073742005 |
| binlog.000814 | 1073742011 |
| binlog.000815 |  932145331 |

Meaning: The server is tracking binlogs and their sizes. This output is what you purge against—don’t delete files by hand and expect MySQL to clap.

Decision: If you need to purge, do it via SQL (PURGE BINARY LOGS) or via mysqlbinlog-aware workflows. Hand deletion is a last resort and usually followed by “why won’t it start?”

Task 7: Check replication status on a replica (classic file/position)

cr0x@server:~$ mysql -e "SHOW REPLICA STATUS\G" | egrep 'Source_Log_File|Read_Source_Log_Pos|Relay_Source_Log_File|Exec_Source_Log_Pos|Seconds_Behind_Source|Replica_IO_Running|Replica_SQL_Running' -n
12:Source_Log_File: binlog.000702
13:Read_Source_Log_Pos: 98433122
28:Relay_Source_Log_File: binlog.000702
29:Exec_Source_Log_Pos: 98433122
34:Seconds_Behind_Source: 0
40:Replica_IO_Running: Yes
41:Replica_SQL_Running: Yes

Meaning: This replica is caught up and executing current logs. If all replicas look like this, you can usually purge old binlogs safely up to the oldest Source_Log_File still needed.

Decision: Collect this from every replica. The “oldest needed binlog” across replicas is your purge boundary.

Task 8: Check replication status on a replica (GTID flavor)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'gtid_mode'; SHOW MASTER STATUS\G; SHOW REPLICA STATUS\G" | egrep 'gtid_mode|Executed_Gtid_Set|Retrieved_Gtid_Set|File:|Position:|Seconds_Behind_Source' -n
1:gtid_mode	ON
7:File: binlog.000815
8:Position: 932145331
19:Retrieved_Gtid_Set: 2f1d3c0a-2f9d-11ee-8f6a-0242ac120002:1-93381221
20:Executed_Gtid_Set: 2f1d3c0a-2f9d-11ee-8f6a-0242ac120002:1-93381221
26:Seconds_Behind_Source: 0

Meaning: GTID is enabled and the replica is in sync. Purging is still based on what replicas need; GTID just gives you cleaner state to reason about.

Decision: Use GTID sets to validate that replicas have executed what you plan to purge. If a replica is missing parts of the set, you can’t purge those logs without accepting a rebuild.

Task 9: Identify a replica that is blocking purges (offline or lagging)

cr0x@server:~$ mysql -e "SHOW REPLICA HOSTS;"
+-----------+------------------+------+-----------+--------------------------------------+
| Server_id | Host             | Port | Rpl_recovery_rank | Master_id                    |
+-----------+------------------+------+-----------+--------------------------------------+
| 102       | db-replica-a     | 3306 | 0         | 101                                  |
| 103       | db-replica-b     | 3306 | 0         | 101                                  |
| 104       | db-replica-c     | 3306 | 0         | 101                                  |
+-----------+------------------+------+-----------+--------------------------------------+

Meaning: You have three replicas registered. “Registered” doesn’t mean “healthy.”

Decision: Log into each replica and run Task 7/8. The worst one dictates your retention floor unless you’re willing to drop it and rebuild.

Task 10: Check relay log growth on a replica (the other disk bomb)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'relay_log%';"
+-------------------+-----------------------------------+
| Variable_name     | Value                             |
+-------------------+-----------------------------------+
| relay_log         | /var/lib/mysql/relaylog           |
| relay_log_index   | /var/lib/mysql/relaylog.index     |
| relay_log_info_file | relay-log.info                  |
+-------------------+-----------------------------------+

cr0x@server:~$ sudo find /var/lib/mysql -maxdepth 1 -type f -name 'relaylog.*' -printf '%s\n' | awk '{sum+=$1} END{printf "relay files=%d total=%.2fG\n", NR, sum/1024/1024/1024}'
relay files=390 total=412.77G

Meaning: The replica is hoarding relay logs. This happens when SQL thread can’t keep up, or is stopped, or crashes repeatedly. People often “fix” primary binlog retention while the replica is the one filling disks.

Decision: Fix the replica apply rate (indexes, SQL thread errors, parallel replication) or rebuild it. Also confirm relay log purge behavior is enabled and functioning.

Task 11: Check for long transactions that create giant binlog events

cr0x@server:~$ mysql -e "SELECT trx_id, trx_started, TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) AS age_s, trx_rows_modified FROM information_schema.innodb_trx ORDER BY age_s DESC LIMIT 5;"
+--------+---------------------+-------+-------------------+
| trx_id | trx_started         | age_s | trx_rows_modified |
+--------+---------------------+-------+-------------------+
| 987655 | 2025-12-31 09:11:02 | 18420 | 2289341           |
| 987654 | 2025-12-31 13:55:10 |   122 | 0                 |
+--------+---------------------+-------+-------------------+

Meaning: A 5-hour transaction modifying millions of rows is a binlog factory. Even if it’s “legitimate,” it’s an operational hazard: it stresses disk, replication, and crash recovery.

Decision: Coordinate with application owners: chunk large updates, avoid monster transactions, and consider throttling mechanisms.

Task 12: Measure current binlog write throughput (is this a sudden spike?)

cr0x@server:~$ sudo iostat -dx 1 3 | egrep 'Device|nvme1n1'
Device            r/s     w/s   rMB/s   wMB/s avgrq-sz avgqu-sz await  svctm  %util
nvme1n1          12.0   980.0    0.4    86.2    176.2     4.12   4.1   0.7   72.0
nvme1n1          10.0  1100.0    0.3    92.5    172.0     6.88   6.2   0.8   89.0
nvme1n1          11.0  1205.0    0.3   101.1    171.4     8.40   7.1   0.9   96.0

Meaning: Heavy sustained writes. If this correlates with binlog growth, you may be in a write storm (batch job, schema migration, retry loop).

Decision: Identify the top writers (next task) and decide whether to pause, throttle, or optimize them.

Task 13: Identify top write queries (performance_schema digest)

cr0x@server:~$ mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, ROUND(SUM_TIMER_WAIT/1e12,1) AS total_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_AFFECTED DESC LIMIT 3\G"
*************************** 1. row ***************************
DIGEST_TEXT: UPDATE orders SET status = ? WHERE created_at < ? AND status = ?
COUNT_STAR: 412
SUM_ROWS_AFFECTED: 18922341
total_s: 980.2
*************************** 2. row ***************************
DIGEST_TEXT: DELETE FROM sessions WHERE expires_at < ?
COUNT_STAR: 8122
SUM_ROWS_AFFECTED: 4491122
total_s: 122.8

Meaning: The workload is doing large updates and deletes. In ROW format, that’s binlog payload, not just “database work.”

Decision: Add indexes to reduce touched rows, chunk operations, or schedule heavy maintenance jobs off-peak. If this is a one-off migration, plan extra disk headroom and a purge boundary.

Task 14: Check whether automatic purge is working (binlog expiry status)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Binlog%';"
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| Binlog_cache_disk_use     | 22       |
| Binlog_cache_use          | 98121    |
| Binlog_stmt_cache_disk_use| 0        |
| Binlog_stmt_cache_use     | 0        |
+---------------------------+----------+

Meaning: Not directly “purge status,” but it tells you binlogging is active and caching behavior. For purge behavior, you validate by observing whether old binlog files disappear over time and by checking retention variables.

Decision: If retention is set and files aren’t aging out, replication/backups are likely blocking purge or you have a version-specific behavior mismatch.

Task 15: Safely purge binlogs up to a boundary (file-based)

cr0x@server:~$ mysql -e "PURGE BINARY LOGS TO 'binlog.000780';"

Meaning: MySQL/MariaDB will remove binlog files strictly before binlog.000780 (not including it), updating the index and internal state.

Decision: Only do this after confirming all replicas and any consumers (backup/CDC) have moved past that boundary. If one replica still needs binlog.000702, purging to 780 will break it.

Task 16: Emergency: rotate to a new binlog file (buy small clarity)

cr0x@server:~$ mysql -e "FLUSH BINARY LOGS;"

Meaning: Forces a binlog rotation. This doesn’t reduce disk use, but it can help operationally by making “what’s current” obvious and by creating a clean boundary for later purge decisions.

Decision: Use during incident response when you want a known “starting point” for analysis and to isolate current activity from backlog.

Retention and purge: safe defaults and sharp edges

Binlogs are not “trash.” They are “temporary safety.” Your job is to decide how much safety you can afford, then enforce it ruthlessly.

Pick a retention window based on recovery objectives, not vibes

If you do daily full backups and you want point-in-time recovery within that day, you typically need binlogs from the time of the backup onward. Many orgs choose 2–7 days to cover operational slop: delayed detection, bad deploys, weekend staffing, replica rebuild time.

But: retention is constrained by the slowest consumer. If you have a replica that is frequently offline, a CDC connector that pauses, or a backup system that “streams binlogs when it feels like it,” your primary will retain more than your policy unless you cut that dependency.

Set retention explicitly (and confirm which variable your version uses)

On modern MySQL, binlog_expire_logs_seconds is the clearer knob. On older installations, expire_logs_days may be the only one that matters. MariaDB varies by version, and some environments carry both settings. You want one authoritative setting and proof it’s working.

Operational advice: if you inherit a server with no retention, don’t set “30 days” as a “safe” first move. That’s how you keep the bomb under your desk. Start with a realistic window—often 3–7 days—then increase only if you’ve proven you can store it and you genuinely need it.

Don’t purge by deleting files

Yes, you can rm /var/lib/mysql/binlog.000123. And yes, sometimes you can get away with it. But this is how you get:

• replicas stuck asking for binlogs that no longer exist
• the server confused about its binlog index
• backup/PITR workflows broken in subtle ways

Use PURGE BINARY LOGS unless the server is down and you’re doing disaster surgery. Even then, plan a restart and expect replication fallout.

Replication lag: the most common reason purging “doesn’t work”

When people say “I set retention but binlogs keep growing,” nine times out of ten they’re running replication, and something is behind. The primary keeps logs because replicas need them. That’s not the server disobeying you; that’s you asking for mutually exclusive things: “keep less history” and “support a replica that’s weeks behind.”

How replication blocks purge in practice

Common blockers:

• A replica is offline for maintenance, network issues, or “someone stopped it” and forgot.
• Replica SQL thread is stopped due to an error (duplicate key, missing table after a botched schema change, etc.).
• Replica is I/O bound applying ROW events and can’t keep up.
• Replica has huge relay logs and is stuck in slow apply.
• Non-replica consumers (CDC tools, audit pipelines, backup agents) are reading binlogs and require history.

Be decisive about unhealthy replicas

If a replica has been offline for long enough that its backlog is eating your primary’s disk, you have to decide:

• Is the replica critical? If yes, fix it now: restore network, resolve SQL errors, increase apply parallelism if appropriate, or rebuild it from a fresh snapshot.
• Is it “nice to have”? If no, break it on purpose: purge binlogs to save the primary, then rebuild the replica later.

What you should not do is keep the primary in a near-full state to preserve a neglected replica. That’s how you turn one broken thing into two.

PITR and backups: how to keep recovery without infinite logs

If you want point-in-time recovery, you need two ingredients:

1. a consistent base backup (full or incremental chain)
2. binlogs from the time of that base backup up to the recovery point

The failure mode is obvious: you keep binlogs “just in case,” but you don’t actually have a valid base backup chain. Then you’re spending disk for the illusion of safety. Validate your restore process. Practice it. Otherwise binlogs are just very expensive comfort.

Practical retention logic that works

• Keep binlogs for at least the maximum time you might need to detect a data issue (human detection latency is real).
• Keep binlogs for at least one full backup interval plus safety margin. If backups are daily, 2–7 days is common.
• Align purge boundaries with backup completion. Purging logs that a backup hasn’t “captured” (logically or physically) is how you lose PITR.

Joke #2: The only thing scarier than a disk full of binlogs is a disk empty of binlogs five minutes after you need them.

Binlog format choices: ROW vs MIXED vs STATEMENT

Binlog size is not just “traffic.” It’s also “how you represent traffic.” The format choices can change your binlog volume drastically.

ROW: deterministic, heavy, and usually the right default

ROW format logs the actual row changes. That’s gold for correctness and replication safety. It’s also expensive when you update many rows, or when rows are wide (lots of columns), or when your updates touch blobs/text.

If you’re on ROW and seeing binlog explosion, don’t immediately jump to STATEMENT. First, reduce the workload’s blast radius: chunk updates, index properly, and avoid touching columns unnecessarily.

binlog_row_image: FULL vs MINIMAL

Where supported and safe, binlog_row_image=MINIMAL can reduce binlog size by logging fewer columns. This can help a lot for wide tables where updates only touch a couple columns.

But: “safe” depends on version, tools, and whether downstream consumers require full images. Some CDC setups want FULL images. Some auditing pipelines depend on them. Know your consumers.

MIXED and STATEMENT: smaller, but sharp edges

Statement-based logging can be much smaller for bulk operations (a single statement rather than millions of row events). It can also be non-deterministic and break replication in weird ways. MIXED tries to be clever: it logs statements when safe, rows when not.

In modern production setups, ROW is the boring choice that keeps you out of postmortems. If you’re cost-optimizing binlog size by switching formats, do it only after you’ve tested replication correctness under your exact workload.

Storage reality: IOPS, fsync, compression, and where the bytes go

Binlog growth is a database problem and a storage problem. If your disks are slow, binlogs can become both the symptom and the cause: heavy writes fill the disk, and the I/O pressure slows replication apply, which forces longer retention, which fills more disk.

sync_binlog and durability trade-offs

sync_binlog=1 means the server fsyncs binlog writes frequently. It’s good for durability (fewer transactions lost on crash) and often required for strict recovery guarantees. It’s also more punishing on storage latency.

If your storage can’t handle it, don’t “optimize” by relaxing durability blindly. Fix the storage, spread the I/O, or scale out. Durability knobs are not performance knobs; they are risk knobs.

Split binlogs to a dedicated filesystem (sometimes)

Putting binlogs on their own mount can be a smart move:

• prevents binlogs from filling the datadir filesystem and bricking the server
• simplifies monitoring and alerting
• can align with faster media

But it can also create contention if you shove them onto slow, shared disks. The goal is isolation with adequate performance, not “move it somewhere else and hope.”

Compression: helpful when network/IO is the bottleneck

If your binlog payload is huge and your bottleneck is disk or network, compression can help. If your bottleneck is CPU already, it can backfire. Measure first.

Three corporate mini-stories from the trenches

Mini-story 1: The incident caused by a wrong assumption

They had a primary and two replicas. Straightforward. The team also had a compliance requirement to keep “enough history to investigate.” Someone interpreted that as “keep binlogs indefinitely,” because binlogs look like an audit trail if you squint hard enough.

The wrong assumption showed up months later during a routine kernel patch. One replica stayed down longer than expected due to a NIC firmware issue, so it missed a lot of changes. The primary dutifully kept binlogs so the replica could catch up. Nobody noticed because the primary still had plenty of disk. At first.

Then a batch job landed: a large backfill updating a wide table in ROW format. Binlogs surged. The primary filled its filesystem in the middle of business hours and stopped accepting writes. The outage didn’t begin with the batch job; it began with the assumption that “binlogs are an audit log” and “retention can be infinite.”

After the fact, they implemented two things: a real audit log pipeline (separate from binlogs), and an explicit binlog retention aligned to backup/PITR needs. The replica that stayed offline too long got rebuilt from a fresh snapshot. Nobody liked it, but everybody slept.

Mini-story 2: The optimization that backfired

A different team was fighting replication lag and disk usage. Someone had a bright idea: reduce binlog size by switching from ROW to STATEMENT. It worked in the lab. It even worked for a week in production. Which is how these stories usually go.

Then a deployment introduced a query pattern that depended on non-deterministic behavior: a time-based update that used the current timestamp implicitly. On the primary it behaved one way; on a replica with slightly different execution timing it behaved another. The replicas diverged quietly—no crash, no loud alarm, just wrong data.

The backfire was expensive because it was subtle. They spent days reconciling tables, re-cloning replicas, and building detection checks. The binlog “optimization” saved disk and cost them trust.

They went back to ROW. The real fix was boring: chunked maintenance jobs, better indexes, and a retention policy that allowed rebuilding replicas quickly when needed. Disk is cheaper than correctness, until it isn’t.

Mini-story 3: The boring but correct practice that saved the day

This one is my favorite because it’s not heroic. It’s just competent. A company ran MySQL primaries with three replicas across two regions. They treated binlog retention as part of capacity planning, not an afterthought.

They had a runbook item: weekly verify that the oldest replica position was within retention; monthly perform a PITR drill in a sandbox; daily alert if binlog filesystem growth exceeded a threshold per hour. The alerts were quiet most of the time, which is the point.

One day a network partition isolated a replica for hours. Binlogs grew, but alerts fired early, and the on-call could see the growth rate and the retention window. They expanded the binlog filesystem (because they had a pre-approved procedure), brought the replica back, and purged old logs safely after it caught up.

No outage. No war room. Just a ticket, a graph, and a couple commands. The best incident is the one that never becomes a story anyone tells at a conference.

Common mistakes: symptom → root cause → fix

1) Disk fills even though you set retention

Symptom: Binlog files keep accumulating; the oldest file is weeks old; retention variables are set.

Root cause: A replica or binlog consumer is behind/offline, preventing purge (or you set the wrong variable for your version and it’s ignored).

Fix: Identify the oldest required binlog across replicas, fix or rebuild the lagging replica, verify the correct retention variable via SHOW VARIABLES, and validate files are aging out.

2) Purge breaks replication immediately

Symptom: Replica I/O thread errors: “Could not find first log file name in binary log index file.”

Root cause: You purged beyond what the replica had retrieved/executed, or deleted files manually.

Fix: Rebuild the replica from a fresh snapshot, or if possible re-point it to a source that still has required logs. Stop deleting binlogs by hand.

3) Replica disk fills but primary looks fine

Symptom: Replica runs out of disk; primary has plenty of space.

Root cause: Relay logs are accumulating because SQL thread is stopped/slow; or relay_log_purge behavior isn’t effective due to errors.

Fix: Fix the SQL thread error, improve apply throughput, confirm relay log purging works, or rebuild the replica.

4) Binlogs are enormous during backfills/migrations

Symptom: A single job creates hundreds of GB of binlogs; replicas lag for hours.

Root cause: Large transactions, ROW format, wide rows, and unchunked updates/deletes.

Fix: Chunk operations, add/verify indexes, avoid touching unnecessary columns, consider binlog_row_image=MINIMAL where safe, and schedule heavy jobs with capacity headroom.

5) “We need binlogs for audit” becomes “we lost a weekend”

Symptom: Endless retention justified by compliance; disk growth is constant; nobody can state a recovery or investigation procedure.

Root cause: Binlogs are being used as a substitute for an audit log strategy.

Fix: Build an audit/CDC pipeline designed for retention and querying; keep binlogs only as long as needed for replication/PITR.

Checklists / step-by-step plan

Step-by-step: get binlog growth under control (without breaking things)

1. Inventory consumers: list replicas, CDC tools, backup agents that read binlogs.
2. Measure current binlog footprint: total size, file count, growth rate per hour/day.
3. Verify retention settings: confirm which variable applies to your version; remove conflicting legacy settings.
4. Verify purge is possible: collect replica positions/GTID sets; find the oldest required boundary.
5. Fix lagging/offline replicas: either catch them up or decide to rebuild them; don’t let them hold your primary hostage.
6. Purge safely: use SQL purge commands to a boundary you’ve validated.
7. Set alerts: disk usage, binlog directory growth rate, replica lag, relay log growth on replicas.
8. Reduce write amplification: chunk jobs, index properly, reconsider row image settings where safe.
9. Align with backups: ensure you have a consistent backup baseline and documented PITR steps.
10. Practice recovery: periodic PITR drills. If you can’t restore, retention is theater.

Emergency checklist: disk is at 95% and rising

1. Confirm which filesystem is full (df -hT).
2. Confirm binlogs are the dominant usage (du / find total size).
3. Identify the oldest replica position/GTID and whether any replica is offline.
4. If replicas are healthy: purge to the safe boundary.
5. If a replica is offline and non-critical: accept breaking it, purge to save the primary, rebuild later.
6. If you cannot purge safely fast enough: expand the filesystem or move binlogs to buy time, then do the correct fix.

FAQ

1) Can I just disable binlogging to stop disk growth?

You can, but you probably shouldn’t. Disabling binlogs breaks replication and eliminates PITR. If you truly don’t need either, turn it off deliberately and update operational assumptions. Otherwise, fix retention and replication health.

2) Why didn’t expire_logs_days delete anything?

Either it’s set to 0 (disabled), you’re on a version where another variable takes precedence, or something still needs the logs (replica/consumer). Verify via SHOW VARIABLES and then check replica status.

3) What’s the safest way to purge binlogs?

Use SQL: PURGE BINARY LOGS TO 'binlog.XXXXXX'; after confirming all replicas/consumers no longer need earlier logs. The safest “human process” is to compute the purge boundary from the oldest replica and document it in the incident ticket.

4) Are MariaDB and MySQL purge commands the same?

The core SQL commands are very similar. The differences that bite are usually around GTID implementation, version-specific variables, and what your tooling expects. Treat each fleet as its own ecosystem: verify behavior, don’t rely on memory.

5) How much binlog retention should I keep?

Keep enough to cover: (a) the time from your last valid base backup to now, plus (b) detection/response latency, plus (c) replica rebuild time if you rely on it. For many teams that’s 3–7 days. For some it’s 24 hours. For others it’s longer. If you can’t justify it with a recovery story, you don’t need it.

6) Do GTIDs reduce binlog size?

No. GTIDs help you reason about replication state and failover. Binlog size is dominated by workload and format (ROW vs STATEMENT), row images, and transaction patterns.

7) Why are binlogs huge when we run a DELETE?

In ROW format, a large delete logs row events for each row affected. The database is doing real work and recording it. The fix is to chunk deletes, ensure proper indexes, and schedule heavy maintenance with capacity headroom.

8) If a replica is weeks behind, should I keep binlogs until it catches up?

Usually no. If a replica is that far behind, it’s often faster and safer to rebuild it from a fresh snapshot. Keeping weeks of binlogs on the primary is how you risk the primary’s availability for a replica that is already failing you.

9) What about “binlog disk explosion” on a replica?

That’s often relay logs, not binlogs. Check the replica’s relay log directory size and whether SQL apply is stalled. Fix apply or rebuild the replica; don’t just crank up disk and call it solved.

10) Can I move binlogs to another disk without downtime?

Sometimes, depending on version and configuration, but plan for a maintenance window. The operationally safe move is: stop MySQL, move files, adjust config (log_bin_basename path), start MySQL, validate with SHOW BINARY LOGS. If you need “no downtime,” you should be designing around that requirement with topology (failover) rather than filesystem tricks.

Conclusion: next actions that actually prevent a repeat

If you remember one thing: binlogs don’t “get out of control.” They do exactly what you asked—record changes forever—until the disk makes you stop asking.

Practical next steps:

1. Set explicit retention (seconds/days) and confirm it matches your version and actually purges.
2. Make replication health a first-class SLO; a broken replica is not a harmless accessory, it’s a disk liability.
3. Align binlog retention with backups and run PITR drills so you know what you’re buying with those bytes.
4. Instrument growth rate and alert on “GB per hour” changes, not just “disk at 90%.”
5. Reduce write amplification in bulk jobs: chunk, index, schedule, and avoid giant transactions.

Do these, and binlogs go back to being what they were meant to be: a quiet, useful safety net—not a storage-themed horror anthology.