How do I purge/expire my MySQL binary logs?

This article is being updated. Please be aware the content herein, not limited to version numbers and slight syntax changes, may not match the output from the most recent versions of Bright. This notation will be removed when the content has been updated

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 build up in future?

The problem may show up in some cases as something like:

# du -sh /var/lib/mysql/* 

92K /var/lib/mysql/aria_log.00000001
4.0K /var/lib/mysql/arria_log_control
6.7M /var/lib/mysql/cmdaemon
9.3G /var/lib/mysql/cmdaemon_mon
13G /var/lib/mysql/ibdata1
5.0M /var/lib/mysql/ib_logfile0
5.0M /var/lib/mysql/ib_logfile1
4.0K /var/lib/mysql/master.info
1008K /var/lib/mysql/mysql
3.1M /var/lib/mysql/mysql-bin.000001
7.7M /var/lib/mysql/mysql-bin.000002
14M /var/lib/mysql/mysql-bin.000003
3.9M /var/lib/mysql/mysql-bin.000004
1.2M /var/lib/mysql/mysql-bin.000005
[...]

12K /var/lib/mysql/mysql-bin.000018
1.9M /var/lib/mysql/mysql-bin.000019
2.6M /var/lib/mysql/mysql-bin.000020
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

...
Cleaning up

(tested on Bright 7.0)

The binary logs can be listed and deleted from the mysql command line.
First log into mysql as root by running this command on the head node:

# mysql -u root -p

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

> SHOW BINARY LOGS;

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.

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/5.7/en/purge-binary-logs.html

For example, if you want the binary logs to expire after 7 days, add the following line to /etc/my.cnf:

expire_logs_days=7

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

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

max_binlog_size = 100M
> SET GLOBAL max_binlog_size = 100M;

Log rotation can then tuned to remove logs when there are too many logs. The details of how to do that depends 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/