Server Administration & Management

How to Remove MySQL Binary Log

MySQL configuration, by default, maintains binary logs. These logs “contain all statements that update data or potentially could have updated it (for example, a DELETE which matched no rows). Statements are stored in the form of ‘events’ that describe the modifications. The binary log also contains information about how long each statement took that updated data.” This is fine and all, but (again by default) these log files are never deleted. There is a (configurable) max file size for each log, but MySQL simply rolls over to a new log when it’s reached. Additionally, MySQL rolls over to a new log file on every (re)start. After a few months of operation, it’s easy to see how this can take up a lot of space.

MySQL Binary Log stores query event such as add, delete and update in a very details way. The Binary Log is used for two main 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.

Yes, as long as the data is replicated to Slave server, it’s safe to remove the file. It’s recommend only remove MySQL Binary Log older than 1 month. Besides, if Recovery of data is the main concern, it’s recommend to archive MySQL Binary Log. There are several ways to remove or clean up MySQL Binary Log, it’s not recommend to clean up the file manually means running the remove command.

Finally, for the current set, login to MySQL as an admin user (eg., mysql -u root -p). You’ll want to run the following two commands:

Reset Master statement is uses for new database start up during replication for Master and Slave server. This statement can be used to remove all Binary Log.

To clean up Binary Log on Master Server

# mysql -u username -p

mysql> flush logs;

mysql> reset master;

To clean up Binary Log on Slave Server

# mysql -u username -p

mysql> flush logs;

mysql> reset slave;

Above command will empty the binary logs, but not remove them. That’s it. Depending on the size and number of your logs, those two commands may take a while to run, but the end result is that any unsaved transactions will be flushed to the database, all older logs will be dropped, and the log index will be reset to 1.

Here is how to purge or clean the MySQL binary logs (NEVER remove them from the filesystem manually). PURGE BINARY LOGS statement can remove Binary Log base on date or up to a Binary Log sequence number

mysql> purge binary logs to ‘mysql-bin-log.000015’;

Alternatively, you can remove the binary older than a specific date.

mysql> purge binary logs before ‘2012-02-02 22:46:26’;

The BEFORE variant’s datetime_expr argument should evaluate to a DATETIME value (a value in ‘YYYY-MM-DD hh:mm:ss’ format). Above commands will remove the binary logs from the disk.

mysql> show binary logs;

To obtain a listing of the binary logs on the master server with above command.

The above commands should not be used when/before binary logs are used for data integrity check or for replication. You can also add to /etc/my.cnf the following Code:

expire_logs_days = 5

in order to keep only the last 5 days binary logs.

February 10, 2012 Posted by | MySQL, Tips & Tricks | , , , | Leave a comment