How to limit mariadb logs?

RolandiXor

4/17/24, 2:22 PM

I have a nextcloud server which is using Mariadb for the database, but running into issues due to Mariadb spamming log files (namely: mariadb-bin.0000** in /var/log/mysql.

This seems to be due to how many files/transactions are happening on the server, not due to any errors, so nothing to fix there. I just need to know how to limit the number of log files that are preserved in the folder, so it doesn't keep filing up the limited space the VPS has available.

Is there a way to limit the number of these files, so that they're automatically deleted or limited, so that mariadb-bin.index is not affected?

1 + 3

server

mysql

log

mariadb

RolandiXor

4/19/24, 3:37 AM

For some reason I didn't get a notification for this comment. I'll check these out! Thanks!

RolandiXor

4/19/24, 4:52 AM

Perhaps I'm tired, but I'm not finding the solution in these links (I think I'd just prefer the steps spelled out in this case). How do I limit the number of 'mariadb-bin.0000**` files that are being created? That's the main issue.

Rinzwind

4/19/24, 8:20 AM

Made an answer now I had a bit more time. Seems there is no direct method :-( and all other methods have limits that you might not like.

Score:2

Ubuntu

Rinzwind

4/19/24, 8:19 AM

I did not find a way to limit the amount of files without the help of a script (Percona has a max_binlog_files but it never got back into MySQL/MariaDB).

The workaround is to use either one of these two:

System setting expire_log_days or binlog_expire_logs_seconds (the last one needs MariaDB 10.6.1)

Number of days after which the binary log can be automatically removed. 0 is no automatic removal and the default.
--binlog-expire-logs-seconds=N
where N is the amount of seconds.

You can also purge logs based on date using a command:

  PURGE BINARY LOGS BEFORE '2023-04-01';

Those all do not limit it to the amount of files directly but if you know how many files are created within N seconds it will come close.

Here is a logrotate example (based on the log files set to log in /var/log/mysql/) where error_log, general_query_log, and slow_query_log are split into different logfiles. That might be useful too so you can keep the important logs and discard the normal notices. Problem with logrotate: looks like the minimum amount of days is 7 before files are purged.

If you really want an actual amount of files you will need a script.

+ 3

RolandiXor

4/27/24, 12:59 AM

Looks like I need to upgrade the server to get a newer version of mariadb first, but giving this a shot.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How to limit mariadb logs?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.