How to Solve Increasing Size of MySQL Binlog Files Problem?

Question

MySQL Binlog files count is increasing day by day and their size is increasing in gigabytes. How can I stop the increasing size of binlog files? They are fulling the disc space.

Solution 1

If you do not need to keep those binary logs, you can “trim” them within MySQL like this:

mysql> PURGE BINARY LOGS BEFORE '2021-01-01 00:00:00';

Of course, you can change the date to whenever you would like. If you would like to trim to a specific binary log file, you can do it like this:

mysql> PURGE BINARY LOGS TO 'mysql-bin.12345';

Be sure to specify the actual file you are trimming to.

IMPORTANT: Do not simply delete the files from the file system. MySQL will complain at some time in the future in a terrible fashion. Save yourself the unnecessary downtime by using PURGE BINARY LOGS command from within MySQL.

Solution 2

With MySQL8, they have turned on binary logging by default and the default purge (expiry/deletion) of binary logs is set to 30days.

Once you are in your SSH and in mysql, you can use the below commands

To show binary logs

mysql> SHOW BINARY LOGS;

To Purge binary logs manually until some point

mysql> PURGE BINARY LOGS TO 'binlog.000142';

Change automatic default purge expiry from 30days (deafault) to 3days

mysql> SET GLOBAL binlog_expire_logs_seconds = (60*60*24*3);
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST binlog_expire_logs_seconds = (60*60*24*3);
Query OK, 0 rows affected (0.01 sec)

The above value is in seconds, i.e. 3 days in seconds = (60 seconds x 60 minutes x 24 hours x 3 days)

Solution 3

Binlogs are needed for Replication and certain forms of backup. It sounds like you are not doing either.

For changing settings, I like to add a new file, such as (where "rj" is my initials):

/etc/mysql/mysql.conf.d/rj.cnf

and putting overrides, such as below. in it. After making changes,

sudo systemctl restart mysql

log_bin controls the creation of binlogs.

To keep them purged, add binlog_expire_logs_seconds = 86400 (or some other number).

Another setting controls the max size of each binlog file; that won't affect the total disk space used, except that the last file will stick around until it "expires". A new binlog is started after any reboot (or restart of mysql), so you might see a lot of tiny files.

so, my rj.cnf file might have some of these:

# Added by Rick James
[mysqld]
log_bin =                           # turn off
binlog_expire_logs_seconds = 86400  # 1 day
max_binlog_size = 104857600         # 100M

Solution 4

I faced this issue today:

Safest solution: To solve it safely update your script that is taking db backup and place the purge command in that script right after db backup is done.

It is the safest to use purge right after a complete backup.

Diagnosis: If the log spiked up suddenly and if you are wondering (as i am for my case) then the first place that i have my fingers on is the following:

• Under certain conditions some of the screens in FE are going in indefinite loop, resulting in rapid execution of db queries.
• One of the background service is in loop and firing rapid sql queries.

View more solutions

Comments

[TarabydaVllasıCafcaflıAtArabsı]

thank you, but I need a automatic solution. So maybe I will not delete the logs with that command and they will be increasing for a year. Is there any automatic solution to make logs deleted like "delete logs which are old than 7 days if the binlog size is greater than 5gb". I think I can create a cron script to check the size of the binlogs and delete the files with command you gave me, but is tis a best practise or can I trust to that script?

[Admin]

"Best practice" would be treating an important database like something that's important. If the logs are not needed ever, then you may be able to auto-truncate them with a SQL query that is part of a cron job. If the logs may be needed later, then it would be better to look at implementing some form of log shipping and trimming, which can be done in an automatic fashion with some paid offerings from Percona and others 🤐

[TarabydaVllasıCafcaflıAtArabsı]

when do we need to read the binlog logs? I saw they all were executed queries.

[Admin]

Binary logs can be used for auditing purposes, rebuilding databases to a point in time, examining scope of an update/delete, and things like that. Binary logs are also used with replication, as it's this log that gets sent to the subscribing instances. You can learn more about the binary log on this MySQL page.

[TarabydaVllasıCafcaflıAtArabsı]

I am using MariaDB Galera replication, does it deletes the unnecessary binlogs when they are not needed?

[TarabydaVllasıCafcaflıAtArabsı]

I am using Galera replication on MariaDB database services, so I think I need these files. Is it safe to use binlog_expire_logs_seconds in a replication?

[Rick James]

I think Galera uses its "gcache" instead of the binlog. Why would using ..expire.. be any less safe than purge logs?

[Rick James]

And... dba.stackexchange is a better place to ask about this level of detail into MariaDB+Galera.