MySQL Binary Logs: Purging and Retention Settings

2018-01-10 · mysql, binlog, replication, disk

I've been paged at 3 a.m. because a data partition filled up, and the culprit was binary logs more than once. Once log_bin is on, the server writes every change to a numbered file and never deletes anything unless you tell it to. One bulk import or runaway job can produce tens of gigabytes of logs in an hour.

Here's how I see what I'm holding, pick a retention period that still lets me recover, purge without breaking a slave, and automate it. This applies to MySQL 5.6 and 5.7 and MariaDB 10.1 and 10.2.

How much are you holding?

SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'max_binlog_size';

SHOW BINARY LOGS lists every file with its size in bytes. From the shell:

du -sh /var/lib/mysql/mysql-bin.*
ls -la /var/lib/mysql/mysql-bin.index

The .index file is a plain text list of the log files the server believes exist. If it disagrees with the directory, purging commands will fail, which we will return to below.

What you need to keep

Retention is a trade-off between two things:

Replication. Every slave must be able to find the log file it is currently reading. If a slave is offline for maintenance for six hours and the master purges the file it needs, that slave must be rebuilt from a fresh snapshot.
Point-in-time recovery. To roll forward from a backup to a moment just before a mistake, you need every binlog from the backup's coordinates up to that moment. Retention must therefore cover at least the interval between full backups, plus the time it takes you to notice a problem.

With nightly backups I keep three days of logs at minimum, and seven where disk allows. With weekly backups, seven is the floor.

Automatic expiry

[mysqld]
log_bin          = /var/lib/mysql/mysql-bin
expire_logs_days = 7
max_binlog_size  = 512M

expire_logs_days deletes files older than the given number of days, but only when the server rotates to a new log (on restart, on FLUSH LOGS, or when the current file reaches max_binlog_size). On a quiet server that means expiry may not run for a long time. More on that below.

The value can be changed at runtime too:

SET GLOBAL expire_logs_days = 7;
FLUSH BINARY LOGS;

FLUSH BINARY LOGS rotates and triggers the expiry check immediately.

Purging by hand

Before removing anything on a master, find out what the slaves still need. On each slave:

SHOW SLAVE STATUS\G

Note Master_Log_File and, more importantly, Relay_Master_Log_File, which is the file the SQL thread is applying. The earliest of those across all slaves is the oldest file you must keep. Then on the master:

PURGE BINARY LOGS TO 'mysql-bin.000412';

This removes all files before the one named, and keeps 000412 itself. You can purge by time instead:

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

Both forms update the .index file and are safe on a running server. I never use rm on binlog files. Cleaning up the index afterwards is no fun.

Making expiry actually run

Since expire_logs_days only fires on rotation, a low-traffic server can carry months of logs despite the setting. A daily FLUSH BINARY LOGS fixes that. Use a credentials file rather than a password on the command line:

sudo tee /root/.my-flush.cnf <<'EOF'
[client]
user     = flushlogs
password = another-strong-password
EOF
sudo chmod 600 /root/.my-flush.cnf

Create the user with only the privilege it needs:

CREATE USER 'flushlogs'@'localhost' IDENTIFIED BY 'another-strong-password';
GRANT RELOAD ON *.* TO 'flushlogs'@'localhost';

Then in root's crontab:

15 3 * * * /usr/bin/mysql --defaults-extra-file=/root/.my-flush.cnf -e "FLUSH BINARY LOGS"

On MySQL 5.7 FLUSH BINARY LOGS requires the RELOAD privilege, which this grants.

Checking the result

After the first rotation, confirm the oldest file matches your policy:

SHOW BINARY LOGS;

Compare the first file's modification time on disk with today's date:

ls -l --time-style=long-iso /var/lib/mysql/mysql-bin.* | head -3

Then confirm every slave is still happy:

SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes, Last_IO_Errno: 0

Finally, prove point-in-time recovery still works in principle by reading a recent log with mysqlbinlog:

mysqlbinlog --start-datetime="2018-01-09 00:00:00" /var/lib/mysql/mysql-bin.000415 | head -50

Things that went wrong for me

ERROR 1373 (HY000): Target log not found in binlog index on PURGE BINARY LOGS TO. The file name has a typo, or the .index file is out of sync because someone deleted files by hand. Compare cat mysql-bin.index with ls, remove the lines for missing files from the index, and run FLUSH BINARY LOGS.

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' on a slave. The master purged a log the slave still needed. There is no way to reconstruct it; re-seed the slave from a backup with --master-data.

Disk still full after purging. The mysqld process may still hold a deleted file open, or the space is actually in relay logs on a slave. Check lsof | grep deleted and du -sh /var/lib/mysql/relay-bin.*. Relay logs are purged automatically after being applied when relay_log_purge = 1, which is the default.

expire_logs_days seems ignored. It only applies at rotation. Run FLUSH BINARY LOGS and check again; if the value shows as 0 in SHOW VARIABLES, the config line is in the wrong section or the wrong file.

If I could only remember one thing from all this, it's to check every slave's position before purging by hand.