MySQL Bin Files Eating Lots of Disk Space

MySQL Bin Files Eating Lots of Disk Space

I get a large amount of bin files in the MySQL data directory called “server-bin.n”
or mysql-bin.00000n, where n is a number that increments.

[root@ENMDB1 mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 8.2G 1.1G 89% /var


We need to periodically RESET MASTER or PURGE MASTER LOGS to clear out the old logs.

For Safe side I taken backup of the main log files:
[root@ENMDB1 mysql]# cp slow-queries.log /mysqldb/DBBACKUP/slow-queries.log_bak
[root@ENMDB1 mysql]# cp mysql-query.log /mysqldb/DBBACKUP/mysql-query.log_bak

Purge BINARY LOGS Logs (leaving December BINARY LOGS)
Note: For retaing current month log files purge to mysql-bin.000469 (Nov 30 05:08 mysql-bin.000469).

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000469’;
Query OK, 0 rows affected (40.85 sec)

[root@ENMDB1 mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 888M 8.3G 10% /var

The binary log has two important purposes:

Data Recovery : It may be used for data recovery operations.
After a backup file has been restored, the events in the binary
log that were recorded after the backup was made are re-executed.
These events bring databases up to date from the point of the backup.

High availability / replication : The binary log is used on master
replication servers as a record of the statements to be sent
to slave servers. The master server sends the events contained
in its binary log to its slaves, which execute those events to
make the same data changes that were made on the master.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.