0

I recently had my disk space completely filled with mysql binary log files (mysql-bin.000123, etc).

I was able to resolve short term by with PURGE BINARY LOGS TO (via this answer).

To try and keep it from happening going forward I set lower limits on expire_logs_days and max_binlog_size. Yet, I'm still worried that a huge number of log files could be generated in a short period without a limit on the total space used by logs or the total number of logs allowed.

Is there something I'm missing? Or do I just need to keep setting expire_logs_days lower or turn off binary logging all together if it keeps being a problem?

EDIT:

I understand it's important to diagnose what's causing the logs to fill up quickly and solve that issue. This question is not about how to diagnose what's making them fill quickly, but rather - how do I safeguard against the disk filling to 100% and crashing the server in the case that something is setup wrong or an unexpected circumstance starts to fill these logs.

1

max_binlog_size has little effect on keeping disk space down; expire_logs_days is the one to worry about.

How fast are the logs filling up?

One way binlogs can fill up extremely fast is when you accidentally have the same server_id on multiple servers in a Replication setup.

Which version? Which binlog_format? Lots of multi-row UPDATEs/DELETEs?

More

expire_logs_days is the best available builtin tool. There may be 3rd party monitoring tools (or you could write one) that watch for disk space.

In any system, you should have some kind of monitoring on disk space -- it is not just the binlog that can go berserk. The "general log" and the slowlog can, also. And your application can go berserk. And so forth.

  • I understand that it's important to go into the logs and analyze why they are filling up fast when there is an issue. That's something I can do after an issue occurs. What I'm looking for is the best way to safeguard against mysql logs filling the entire disk and crashing the server. I don't want that to happen ever no matter what else is setup wrong. My concern is that expire_logs_days is a cap on time, not on disk space. – squarecandy Jul 22 '17 at 22:57
  • Alas, no. I added to my answer. – Rick James Jul 22 '17 at 23:24
  • Thanks for your all your help Rick. Good to know there is no built in setting for it. I do have disk space monitoring going. I was just hoping there might be something similar to logrotate where you can set a cap on the log size and then also on the total number of logs, for a net result that it's difficult to fill the entire disk with log results. Sounds like there is not (without a custom script). – squarecandy Jul 23 '17 at 0:05

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.