max_binlog_files on MySQL Server?

I read recently about a setting in Percona Server called max_binlog_files which restricts the total number of binlog files. This is exactly what I need. My binlog_expire_logs_seconds is currently set to 3 days, which works great 99% of the time. However, I'm currently engaged in project which runs a massive amount of queries and the binlogs grew to consume over 70GB in just a few hours. In this case, I really need to limit the number of files.

Is there any native setting with MySQL to achieve this? What is the best way to restrict total disk space consumed by binlogs?

No, the upstream MySQL implementation does not have a feature equivalent to max_binlog_files. So you can't really prevent a short-term surge in binary log growth.

Well, the option log_bin=OFF would definitely prevent unlimited binary log growth, but that's the only solution provided by stock MySQL Server.

You could implement a scheduled job outside of MySQL (e.g. a cron job) to periodically check SHOW BINARY LOGS and if they are using more space than you want, then PURGE BINARY LOGS to reduce the space.

The risk is that your aggressive purging might delete some logs that are still needed for replication, point-in-time recovery, or CDC tools, if you use those features.

At my last job, we occasionally had binary logs on a given MySQL grow to 500GB. The developers were not aware of the impact of doing huge bulk data loads, and they would do so anytime they felt like it, without notifying the DBA team. We were using Percona Server so we set a max_binlog_files=500 so they wouldn't use more than 500GB, but we weren't at liberty to be more restrictive.

Ultimately, we just had to make sure there was enough storage space free on each database server for these kinds of surges.

StackExchange Database Administrators Q#332430, answer score: 3