How to Remove MySQL Binary Log

Written by · in Systems

By default, MySQL 5.0 and above enables MySQL Binary Log. Keeping MySQL Binary Log might take up a lot of disk space for long run. Older MySQL Binary log can be removed in order to keep your hard disk space free.

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;

  • 1. Replication between master and slave server, statement that has been made on Master server will later send it to slave server.
  • 2. Recovery, certain recovery job required data stored in MySQL Binary Log.

MySQL Binary Log is located at the database’s root folder with the naming convention of mysql-bin.. Sample of the binary files as shown below;

mysql-binary-log

Can I Remove MySQL Binary Log

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, manually means running the remove command.

Remove MySQL Binary Log with RESET MASTER Statement

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


shell> mysql -u username -p
mysql> RESET MASTER;

To clean up Binary Log on Slave Server


mysql -u username -p
mysql> RESET SLAVE;

Remove MySQL Binary Log with PURGE BINARY LOGS Statement

PURGE BINARY LOGS statement can remove Binary Log base on date or up to a Binary Log sequence number

Base on the binary logs example shown above, I would like to remove binary up to mysql-bin.000015


shell> mysql -u username -p
mysql>PURGE BINARY LOGS TO 'mysql-bin.000015';

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


shell> mysql -u username -p
mysql> PURGE BINARY LOGS BEFORE '2009-05-01 00:00:00';

Remove MySQL Binary Log with mysqladmin flush-logs Command

Another method is running mysqladmin flush-logs command, it will remove binary logs more than 3 days old. 


shell> mysqladmin -u username -p flush-logs

Keep MySQL Binary Log for X Days

All of the methods above required monitoring on disk usage, to “rotate” and keep the binary logs for x number of day. The option below can be configured on MySQL’s config file, my.cnf


expire_logs_days = 7

Consider turning off MySQL Binary Log if MySQL Replication is not deploy on the database server and recovery is not the main concern.

I have additional 15GB if disk space now 🙂