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;
|
Reference -->
http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
No comments:
Post a Comment