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';

The next step uses the database server settings to limit how long a binary log is stored.

To do so, go to the “Databases” - “Database Servers” section, select the desired database server and click “Settings”, then find the “binlog-expire-logs-seconds” parameter (value in seconds) or the “expire-logs-days” parameter (value in days), if the former is missing, and change its value as desired.

You can also completely disable the creation of binary logs.

To do so, add the “[mysqld]” section and the “skip-log-bin” parameter to the end of the database server configuration file at “/etc/my.cnf” (Or for Debian and Ubuntu, “/etc/mysql/my.cnf”):

[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