3

I am looking for a way to prevent MariaDB binary logs from taking a huge size when they grow faster than usual.

  • variable max_binlog_size set the maximum size of ONE binary logfile.
  • variable expire_log_days set the number of days after which the binary logs are removed.

But is there a way to set a "maximum overall size" of ALL binary log files? or, maybe simpler, a maximum number of binary log files, which would overload expire_log_days retention...

5

max_binlog_size

With regards to max_binlog_size, what you are asking is not possible. Why ?

On May 05, 2017, I answered the question MySQL - Binary Log File size growing more than the maximum limit. I mentioned the following from the MySQL Documentation on max_binlog_size:

A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

That fact that your binary logs are bigger than max_binlog_size just indicates that you are writing too many changes per single transaction.

expire_logs_days

With regards to expire_logs_days, the granularity is in days. There is nothing you can do with that.

If you wish to squeeze the number of binlogs down, you may have do one of two things:

SUGGESTION #1 : Scan in the binlog index file, find the last 10 binlogs, get the first binlog of those 10, and run something like:

PURGE BINARY LOGS TO 'whatever-binlog.002316';

SUGGESTION #2 : Write a cronjob that will keep the last 18 hours of binary logs:

PURGE BINARY LOGS BEFORE (NOW() - INTERVAL 18 HOUR);

GIVE IT A TRY !!!

  • Thanks. I think MySQL/MariaDB lacks an option like "max_binlog_files" that would delete the oldest binlog files when it exceeds this maximum. – Nicolas Payart Jan 4 '18 at 9:52
  • In MySql 8, don't use expire_logs_days, use expire_logs_seconds instead. dev.mysql.com/worklog/task/?id=10924 – Oliver Nybroe Apr 16 '19 at 12:13
2

I just noticed that Percona Server has implemented such a system variable "max_binlog_files":

https://www.percona.com/doc/percona-server/LATEST/flexibility/max_binlog_files.html

MariaDB has an opened issue for backporting it to MariaDB Server, in "open" status at this time:

https://jira.mariadb.org/browse/MDEV-13188

  • Now, that's cool !!! +1 for max_binlog_files !!! – RolandoMySQLDBA Jan 4 '18 at 15:41
0

Short answer: No.

Long answer(s):

Why do you want this? Running out of disk space? If so, then you are threatening to crash for other reasons. That is, we need to discuss the bigger picture.

Are you frequently UPDATEing all rows of a big table (with Row Based Replication)? That can quickly bloat the binlogs. Let's discuss alternatives to that form of UPDATE. (Need table and query specifics to continue this approach.) A big DELETE could also bloat the binlog(s) fast.

If you now have max_binlog_size set to 1G, changing it to 100M could shrink the disk usage by 900M in some situations. (I see no down-side to making the change.)

  • I am using Mixed Based Replication (=> leads to Stmt most of the time). My binlog size is 100MB. I encountered a situation where the application wrote a huge amount of data in just a few hours that exceeded 100GB of binlog storage... – Nicolas Payart Jan 4 '18 at 9:47
  • @NicolasPayart - Ouch! That's a good stunt. bugs.mysql.com accepts "feature requests". – Rick James Jan 4 '18 at 21:31

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.