6

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

Improve this question
11

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.

Improve this answer
3
  • 1
    Just wanted to mention there's a useful (recent) discussion of this including things to be aware of when purging logs here, might help you decide which way to go percona.com/blog/2018/03/28/… – greenweeds May 14 '18 at 8:39
  • Your link for binlog_expire_log_seconds is dead. I wanted to fix it but for some reasons there is no reference for this setting. Any idea what happened? – Peter VARGA Aug 22 '18 at 15:59
  • Apparently it was moved after my comment to a separate "binlog" options section. I have updated the link. You can see it mentioned here with the wrong link, too mysqlserverteam.com/… – jynus Aug 23 '18 at 8:22
4

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.