17

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.

Improve this question

5 Answers 5

Reset to default
19

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 = 259200;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST binlog_expire_logs_seconds = 259200;
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) = 259200

Improve this answer
16

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.

Improve this answer
5
  • 1
    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?
    – TarabydaVllasıCafcaflıAtArabsı
    Mar 9, 2021 at 5:34
  • "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 🤐
    – user1091774
    Mar 9, 2021 at 6:00
  • when do we need to read the binlog logs? I saw they all were executed queries.
    – TarabydaVllasıCafcaflıAtArabsı
    Mar 9, 2021 at 7:33
  • 1
    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.
    – user1091774
    Mar 9, 2021 at 8:08
  • 1
    I am using MariaDB Galera replication, does it deletes the unnecessary binlogs when they are not needed?
    – TarabydaVllasıCafcaflıAtArabsı
    Mar 9, 2021 at 8:16
10

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
Improve this answer
3
  • 2
    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?
    – TarabydaVllasıCafcaflıAtArabsı
    Mar 16, 2021 at 10:35
  • 1
    I think Galera uses its "gcache" instead of the binlog. Why would using ..expire.. be any less safe than purge logs?
    – Rick James
    Mar 16, 2021 at 18:17
  • 1
    And... dba.stackexchange is a better place to ask about this level of detail into MariaDB+Galera.
    – Rick James
    Mar 16, 2021 at 18:19
2

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.
Improve this answer
0

You could use the script available on https://github.com/Thibs/monitor_mysql_binary_logging/blob/main/monitor_mysql_binary_logging.sh

to manage the size/number of your binary log files

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .