How to remove/purge binary logs in mysql? binlogs

By default, MySQL 5.x and above enables MySQL Binary Log. Keeping MySQL Binary Log will 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;

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 used 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 based 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-11-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
Free disk space by purging mysql binary logs.
Are old mysql binary logs important ?
Can I remove old binary logs on mysql server?
Why my mysql server is creating these many binary logs ? How to remove them ?

Leave a Reply

Your email address will not be published. Required fields are marked *