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