Database servers

/
/
Restricting the number of binlog files

Restricting the number of binlog files

Why

MySQL or MariaDB database server binary logs can quickly grow to the point of taking up most of the free disk space.

This can be caused by a large volume of incoming database queries or problems with the database server.

Note

Before performing any of the actions below, it is highly recommended that you backup all databases or take a snapshot of the server state.

To back up all the databases, run the command:

mysqldump -u root -p --all-databases > all_databases_dump.sql

Instructions

First, you must delete the existing binary log files.

To do so, connect to the database server and run one of the following commands:

  1. To delete binary log files that were created more than X days ago:
    PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL X DAY) + INTERVAL 0 SECOND;
  2. To delete all binary log files up to a file named “binlog.X”, where “X” is the sequence number of the file in the directory “/var/lib/mysql*” or “/var/lib/mariadb*”:
    PURGE BINARY LOGS TO 'binlog.X';
  3. To delete files created before a certain time:
    PURGE BINARY LOGS BEFORE 'year-month-day hour:minute:second';

Restriction of logging

To limit the size of binary logs and the frequency of their deletion, use the following parameters:

  • binlog_expire_logs_seconds - the time a log file is stored, in seconds;
  • max_binlog_size - maximum size of the log file, by default in bytes.

For example, for binary logs to have a maximum size of 300 MBytes and be stored for 2 days, the following values should be specified for these parameters:

  • binlog_expire_logs_seconds = 172800;
  • max_binlog_size = 300М.

In older DBMS versions, the expire_logs_days parameter is used to specify the storage time, the values specified in days.

PLEASE NOTE!

The number of binary log files and the space they occupy is not known in advance and always depends on the intensity of database usage.

Parameter values are calculated for each case individually by the server administrator. 

Disabling of logging

To disable creation of binary logs, add the [mysqld] section and the skip-log-bin parameter at the end of the database server configuration file located at /etc/my.cnf (or at /etc/mysql/my.cnf for Debian and Ubuntu):

[mysqld]
skip-log-bin

To apply the changes, restart the DBMS service:

  • For Debian, Ubuntu, RockyLinux 8, and AlmaLinux 9:
    systemctl restart mysql
  • For Almalinux 8:
    systemctl restart mysqld
  • For CentOS 7:
    systemctl restart mariadb

To check if binary logging is disabled, run the mysql utility and execute the following request:

SHOW VARIABLES LIKE 'log_bin';

If logging is disabled, log_bin will be set to “OFF” in the command output:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+

Also, the absence of new files of the “binlog.000001” format in the directory /var/lib/mysql/ signifies that logging is disabled.

In this article