Categories

ID #1320

How do I purge/expire my 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 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;

 

Tip:

 

If you don't have the mysql root password, you can reset it as described in 

 

/faq/index.php?action=artikel&id=321

Tags: -

Related entries:

You cannot comment on this entry