How cleanup MySQL binary logs

What is up will all this binary-log in my MySQL database ? They are taking to much space and i need ot get rid of them ! Not so fast ! First you need to know what are the binary logs and for what they are used: What are they ? The binary log contains “events” that describe database changes such as table creation operations or changes to table data. What do they serve for ? Replication - the binary log on a master replication server provides a record of the data changes 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. Recovery Operations -require the use of the binary logs. After a backup 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. Cons form enabling binary log in MySQL Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement. So if you are on production MySQL database binary logs are pretty important. There is allot to talk about the binary logs but now let's see how can we safely clean old logs. We will start by listing our binary logs: 

mysql SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| bih001.000075 | 105819784 |
| bih001.000076 | 104881533 |
| bih001.000077 | 105052269 |
| bih001.000078 | 104876835 |
| bih001.000079 | 105226559 |
| bih001.000080 | 104857777 |
| bih001.000081 | 104857924 |
| bih001.000082 | 105398762 |
| bih001.000083 | 104990674 |
| bih001.000084 | 105127042 |
| bih001.000085 | 105057967 |
| bih001.000086 | 109000834 |
+---------------+-----------+
26 rows in set (0.04 sec)
You can choose to cleanup until one binary log only by passing the name if it: This will erase all binary logs before bih001.000075, it will not cleanup the actual binary log that you have passed. 
mysql PURGE BINARY LOGS TO 'bih001.000075';
Query OK, 0 rows affected (0.00 sec)
Or you can use a timestamp predicate type: 
mysql PURGE BINARY LOGS BEFORE '2015-01-30 22:46:26';
Query OK, 0 rows affected (0.88 sec)
Another way is to setup a log purge policy. This is possible by setting up the variable expire-logs-days. This variable will setup the value of days that the log will automatically expire. Example: 
mysql SET GLOBAL expire_logs_days = 5;

--Make sure you also added to your conf file(my.cnf) so next time the instance restarts it won't loose the configured parameter.
[mysqld]
expire-logs-days=5
  • is important to know that if you have Replication enabled you need to consider the lag time that you have between your Master Slaves.
  • also make sure you have your backups up to date before you start cleaning up binary logs.

Copyright 2020. All Rights Reserved