Purpose

Customers often run into disk space issues due to MySQL binary logs;
“I have unfeasibly large mysql .bin logs filling up my /var partition! How do I free up the space they are taking up? Can I prevent the buildup in the future?”

For example, this issue may show up in some cases as:

# du -sh /var/lib/mysql/* 
...
1.1G /var/lib/mysql/mysql-bin.000021
1.1G /var/lib/mysql/mysql-bin.000022
1.1G /var/lib/mysql/mysql-bin.000023
1.1G /var/lib/mysql/mysql-bin.000024
1.1G /var/lib/mysql/mysql-bin.000025
1.1G /var/lib/mysql/mysql-bin.000026
1.1G /var/lib/mysql/mysql-bin.000027
1.1G /var/lib/mysql/mysql-bin.000028
1.1G /var/lib/mysql/mysql-bin.000029
1.1G /var/lib/mysql/mysql-bin.000030

Steps to Clean up

The binary logs can be listed and deleted from the MySQL command line.

1. First, log in to MySQL as root by running this command on the head node:

   # mysql -u root -p

2. To show which files are being used to store binary logs, you can run this command on the MySQL command line:

   > SHOW BINARY LOGS;

3. To delete (purge) old binary logs, you can use the “PURGE BINARY LOGS” statement. The following command would, for example, purge the logs from last week:

   > PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY) + INTERVAL 0 SECOND;

The MySQL documentation covers this usage in further detail: https://dev.mysql.com/doc/refman/8.4/en/purge-binary-logs.html

Expiring

In addition to purging the logs, it is possible to configure MySQL to purge the files after a given number of days, as described in: https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds

For example, if you want the binary logs to expire after 7 days, add the following line to /etc/my.cnf (if on a Red Hat or Red Hat variant) or /etc/mysql/my.cnf (if on an Ubuntu system):

binlog_expire_logs_seconds=241,920

NOTE: This value is calculated by multiplying the number of days, by hours, by minutes, and by seconds (7*24*60*60 seconds) The default binary log expiration period is 2592000 seconds, which equals 30 days (30*24*60*60 seconds).

You will also need to log into MySQL as root and run this command (if you don’t want to restart MySQL) to make the new setting active:

> SET GLOBAL expire_logs_days = 2592000;

In the same way, you can also configure my.conf to set the maximum binary log size to the minimum (4 GB) for example:

max_binlog_size = 4096
> SET GLOBAL max_binlog_size = 4096;

Log rotation can then be tuned to remove logs when there are too many. The details of how to do that depend on the operating system and database version.

TIP: If you don’t have the mysql root password, you can reset it as described in https://kb.brightcomputing.com/knowledge-base/ive-forgotten-my-mysql-root-password-how-do-i-get-it-back/