Linux -> Running low on Disk Space -> Mysql Data Binaries

Mysql data binary logs

Mysql binary logs contains records of changesthat affects the database and its  actual data.
It stores delete, update operations, where binary logs allow mysql to recreate events in future.

Since the binary logs contain all data changing events, it is possible to recover changes which occurred after a database dump was created.
 
Issue with these logs

Unfortunately, binary logs do not have a default expiration in mysql.
This means that binary logs are constantly generated, but never removed. This is of concern because the binary logs are not small, and will soon take up a significant amount of space on disk.
The first indication of this is usually " low disk space ".

 Default linux location of these binaries : /usr/local/mysql/data

What i performed to fix the issue is :

• Took dump of mysql database
• Cleared the binaries
• Created the new mysql user, database again and sourced the sql

What actually need to be performed is

 --> Purge old, unneeded binary logs

Steps to perform

1) Type in console : 

 mysql> SHOW SLAVE STATUS \G;

This would show an output like follows :

************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.132.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000074
          Read_Master_Log_Pos: 1003370983
                        etc.

 2) If same as here if Slave_IO_state is still in reading state,it is safe to remove any logs created prior to the oldest one being read by replication slaves.

On the Replication master :

mysql> PURGE BINARY LOGS TO 'mysql-bin.000074';

Alternatively, logs prior to a certain date can be removed:

mysql> PURGE BINARY LOGS BEFORE '2011-01-01 00:00:00';

Note
This only takes care of the immediate excess of binary logs.
It does not prevent them from accumulating and resulting in low disk space again. To prevent this, the mysql global variable "expire log days" should be set. This variable determines how many days the binary logs are kept before being automatically purged.
By default it will be set to "0"

3)set expire log days

mysql> SET GLOBAL VARIABLE expire_logs_days=10;

 And also set mysql conf file : /etc/my.cnf
 

[mysqld]  expire_logs_days = 10

Setting this variable will cause MySQL to automatically purge binary logs older than the number of days specified in the variable.


No we can check  the list of binary logs maintained by mysql by running

mysql> SHOW BINARY LOGS;

If the log files are not listed here,then successfully purged.

Reference -->
http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html