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

Solution:
========

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.

Advertisements

About shanojkumar

* Has 12 years of experience in Planning, Implementing, Managing and Monitoring the Oracle Databases as an (Oracle 10g & 11g OCP) Certified Oracle Database Administrator.
This entry was posted in MYSql and tagged , . Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s