How to Solve Increasing Size of MySQL Binlog Files Problem?

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.

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)

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.

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

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.