MySQL OFF.000001 files filling my disk: what they are and how to remove them

Disk usage on one of my Ubuntu 24.04 production VMs (RunCloud, MySQL 8) kept creeping up. The culprit was /var/lib/mysql, full of large files with names:

OFF.000014
OFF.000015
...
OFF.000037
OFF.index

Most were about 1 GB each, around 17 GB together. I had seen the same thing on another Ubuntu 24 server a week earlier, so this was not a one-off.

These are MySQL binary logs, and they are named OFF because of a config line that was supposed to turn binary logging off.

Why the files are called OFF

Somewhere in the MySQL config there is this:

log_bin = OFF

It looks like a switch, but in MySQL 8 log_bin does not take on/off. It takes a file basename for the binary logs. So log_bin = OFF does the opposite of what it says: it enables binary logging and names the files OFF.000001, OFF.000002 and so on, with OFF.index tracking them.

Two things make it worse:

Binary logging has been on by default since MySQL 8.0. Whoever added the line was trying to turn it off and only renamed the files.
Each file grows to about 1 GB (the default max_binlog_size), and the default retention is 30 days. A busy database piles up tens of gigabytes easily.

On my server the line came from the control panel’s own config:

/etc/mysql/conf.d/runcloud.cnf:10:log_bin=OFF

Confirm what you are looking at

sudo mysql -e "SHOW VARIABLES LIKE 'log_bin%'; SHOW BINARY LOGS;"
grep -Rni "log.bin\|binlog" /etc/mysql/

What I got:

+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_basename                | /var/lib/mysql/OFF       |
| log_bin_index                   | /var/lib/mysql/OFF.index |
| log_bin_trust_function_creators | OFF                      |
+---------------------------------+--------------------------+
+------------+------------+-----------+
| Log_name   | File_size  | Encrypted |
+------------+------------+-----------+
| OFF.000014 | 1073820816 | No        |
| OFF.000015 | 1054613351 | No        |
| OFF.000016 |  542767485 | No        |
| OFF.000017 |   51853980 | No        |
| OFF.000018 |  571391776 | No        |
| OFF.000019 |  991917797 | No        |
| OFF.000020 | 1073742198 | No        |
| OFF.000021 |  832249096 | No        |
| OFF.000022 | 1073747203 | No        |
| OFF.000023 | 1073741951 | No        |
| OFF.000024 | 1073742710 | No        |
| OFF.000025 |  540882604 | No        |
| OFF.000026 |  968077561 | No        |
| OFF.000027 |  402248396 | No        |
| OFF.000028 | 1073749052 | No        |
| OFF.000029 | 1073780998 | No        |
| OFF.000030 | 1073751224 | No        |
| OFF.000031 | 1073752290 | No        |
| OFF.000032 | 1073742495 | No        |
| OFF.000033 | 1073741945 | No        |
| OFF.000034 |  248204340 | No        |
| OFF.000035 |        181 | No        |
| OFF.000036 |  128731910 | No        |
| OFF.000037 |     878748 | No        |
+------------+------------+-----------+

/etc/mysql/conf.d/runcloud.cnf:10:log_bin=OFF

log_bin is ON even though the config says OFF, the basename is /var/lib/mysql/OFF, and 24 files are tracked, 18145130107 bytes in total.

Do not rm them

The obvious move is rm /var/lib/mysql/OFF.*. Don’t! MySQL tracks every binary log in OFF.index, and deleting files behind its back leaves the index pointing at files that are gone. Expect errors, broken replication, or a failed restart.

While binary logging is active, logs are removed from inside MySQL with PURGE BINARY LOGS. Only once logging is disabled are leftover files safe to delete by hand.

Do you need binary logs at all?

They matter in two cases:

Replication. Replicas read from these logs. Purging a file a replica still needs, breaks the replica.
Point-in-time recovery. Some backup strategies restore a full dump and then replay binlogs up to a given moment. If yours does, keep the logs newer than your last good full backup.

If neither applies, which is the typical single web server with nightly dumps (what RunCloud’s own backups use), purge them and turn binary logging off. That is what I did, and what the procedure below does. Keeping them with a sane name and retention is covered at the end.

The cleanup

Run everything as root, and only move on when the previous step looks right.

Step 0: Pre-checks

# Free space, and total database size so the backup will fit
df -h /var/lib/mysql /root
mysql -e "SELECT SUM(data_length+index_length)/1024/1024/1024 AS total_gb FROM information_schema.tables;"

# Both should return nothing on a server that is not part of replication
mysql -e "SHOW REPLICAS; SHOW REPLICA STATUS\G"

If either returns rows, stop and find out which binlog each replica still needs (Source_Log_File in SHOW REPLICA STATUS\G on the replica). Never purge past that file.

Step 1: Back up all databases

mkdir -p /root/mysql-backup && chmod 700 /root/mysql-backup
mysqldump --all-databases --single-transaction --routines --triggers --events \
  | gzip > /root/mysql-backup/all-dbs-$(date +%F-%H%M).sql.gz
echo "exit code: ${PIPESTATUS[0]}"

ls -lh /root/mysql-backup/
zcat /root/mysql-backup/all-dbs-*.sql.gz | tail -n 1

You want exit code: 0 and a last line reading -- Dump completed on .... If the last line is anything else, the dump was cut short; do not continue. $? would give you gzip’s exit code, ${PIPESTATUS[0]} gives you mysqldump’s, which is the one that matters.

--single-transaction takes a consistent InnoDB snapshot without locking the sites, and --routines --triggers --events brings stored procedures, triggers and scheduled events along. Copy the backup off the server too if you can.

Step 2: Purge the logs (no downtime)

mysql -e "FLUSH BINARY LOGS;"
mysql -e "SHOW BINARY LOGS;" | tail -n 1      # newest file, e.g. OFF.000038
mysql -e "PURGE BINARY LOGS TO 'OFF.000038';"  # use the name you just saw
mysql -e "SHOW BINARY LOGS;"
du -sh /var/lib/mysql

FLUSH BINARY LOGS closes the current log and opens a new one, so every older file becomes purgeable. PURGE BINARY LOGS TO 'X' deletes every log before X and updates the index; MySQL never deletes the file in use.

Sites keep working, and one small log is left. On my server the newest file after the flush was OFF.000038, and /var/lib/mysql went from 21G to:

+------------+-----------+-----------+
| Log_name   | File_size | Encrypted |
+------------+-----------+-----------+
| OFF.000038 |       157 | No        |
+------------+-----------+-----------+
4.1G  /var/lib/mysql

To purge by age instead:

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

Step 3: Turn binary logging off

Don’t edit runcloud.cnf. A file managed by a control panel can be rewritten on update, and the fix quietly disappears. Add an override that loads after it instead. MySQL reads conf.d in alphabetical order and the last value of an option wins, so a zz- prefix does it:

cat > /etc/mysql/conf.d/zz-disable-binlog.cnf <<'EOF'
[mysqld]
skip-log-bin
EOF

mysqld --print-defaults | tr ' ' '\n' | grep -i "log.bin"

--skip-log-bin must come after --log_bin=OFF:

--log_bin=OFF
--skip-log-bin

If the order is reversed, fix it before restarting. disable_log_bin is an equivalent spelling.

Step 4: Restart MySQL

Binary logging only turns off on restart. It takes a few seconds and the sites throw database errors meanwhile, so pick a quiet moment.

systemctl restart mysql
systemctl status mysql --no-pager | head -n 5
mysql -e "SHOW VARIABLES LIKE 'log_bin';"

You want active (running) and:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

The restart took about four seconds. If MySQL does not come back, roll back and check what went wrong:

rm /etc/mysql/conf.d/zz-disable-binlog.cnf
systemctl restart mysql
journalctl -u mysql -n 50

Step 5: Remove the leftovers

Only once step 4 shows log_bin = OFF. MySQL no longer tracks the files, so plain rm is safe now:

ls -lh /var/lib/mysql/OFF.*
rm /var/lib/mysql/OFF.0000* /var/lib/mysql/OFF.index

Keep the backup from step 1 for a few days, then delete it from /root/mysql-backup too.

Keeping binary logs instead

If you want them for replication or point-in-time recovery, fix the name and cap the retention rather than disabling them. Drop the log_bin = OFF line (or override it as in step 3) and use:

[mysqld]
log_bin = mysql-bin
binlog_expire_logs_seconds = 259200   # 3 days, default is 30

Retention applies immediately without a restart:

SET PERSIST binlog_expire_logs_seconds = 259200;

The new name takes effect after a restart. Purge the old OFF.* logs with PURGE BINARY LOGS first, as in step 2.

Quick reference

Task	Command
Is binary logging on, and what is the basename?	`SHOW VARIABLES LIKE 'log_bin%';`
List tracked logs and sizes	`SHOW BINARY LOGS;`
Find the setting in config	`grep -Rni "log.bin\|binlog" /etc/mysql/`
Start a new log file	`FLUSH BINARY LOGS;`
Delete all logs before a file	`PURGE BINARY LOGS TO 'OFF.000038';`
Delete logs older than 3 days	`PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;`
Disable binary logging	`skip-log-bin` under `[mysqld]`, then restart
Keep logs for 3 days only	`SET PERSIST binlog_expire_logs_seconds = 259200;`
Check effective startup options	`mysqld --print-defaults \| tr ' ' '\n' \| grep -i "log.bin"`

Takeaway

log_bin takes a file name, not a switch, so log_bin=OFF turns binary logging on and names the files OFF.*. Never rm them while logging is active, don’t edit config the control panel owns, and do it in order: back up, purge online, override, restart in a quiet window.