How to set MySQL binlog retention in hours?

Question

I'm in a situation where the MySQL log partition is being filled in less than a day. I can't disable binlogs, since I need them for replication.

I know I can set log retention but that accepts days, and a search only showed me this https://serverfault.com/questions/179165/can-expire-logs-days-be-less-than-1-day-in-mysql question, which suggests doing it yourself via scripting.

Since that question is almost 8 years old, I was wondering whether it is possible now.

If it matters, I'm using Percona MySQL 5.7

Answer 1

Actually, you can now. Since MySQL 8.0.1, while expire_log_days still works, the canonical way to setup expiration is by changing binlog_expire_logs_seconds, which, as you may guess, has second resolution. The default value since is 8.0.11 is 2592000 (= 30 days), but can be configured as usual on the configuration file to any other period in seconds.

If you cannot use MySQL 8.0 yet, the way to fix this is to use the PURGE BINARY LOGS syntax, with BEFORE (PURGE BINARY LOGS BEFORE now() - INTERVAL 1 HOUR;' and setting the max-binlog_size to an appropiate value (you cannot delete currently-being-written binlog, although you can always force the rotation with FLUSH BINARY LOGS). You can setup an event to do that regularly, or externally, on a cron/programmed task.

Alternatively, forks of MySQL, like Percona, used to (and still does) allow to setup a max binlog size in bytes -by combining max_binlog_size and max_binlog_files-, in addition to the days limit.

Answer 2

Percona Server supports max_binlog_files, which used in conjunction with max_binlog_size can keep the number of files (and their aggregate size) within a target range.

Note that max_binlog_size is not a hard limit, and most files will overshoot it slightly unless you have very large transactions, which can cause it to overshoot more, since transactions aren't split across different binlogs. Each log is rotated after crossing the size threshold.