How to remove/purge MySQL binary logs

Enabling MySQL binary logging consumes disk space. Without suitable binary log expiry settings, you will eventually run out of space. If binary logs consume too much space and you need to delete some to free capacity, you must use MySQL commands. You cannot delete them from the operating system like ordinary files. Follow these steps to remove binary logs. Run the following command to list the system’s binary logs: SHOW BINARY LOGS;

mysql> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| mysqld-bin.002622 | 134217792 |
| mysqld-bin.002623 | 134222196 |
| mysqld-bin.002624 | 134217834 |
             ...
| mysqld-bin.002837 | 134217881 |
| mysqld-bin.002838 | 134217844 |
| mysqld-bin.002839 | 134217816 |
| mysqld-bin.002840 |   3008671 |
+-------------------+-----------+
219 rows in set (0.00 sec)

The command above shows many stored binary logs. To keep fewer, reduce expire_logs_days in MySQL. Before changing it, check its current value with:

mysql> show variables like "expire_logs%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 14    |
+------------------+-------+
1 row in set (0.00 sec)

The example shows that binary logs are retained for 14 days. Reduce the retention period with:

mysql> SET GLOBAL expire_logs_days=xx;

xx is the number of days to retain binary logs. For example, to keep binary logs for 4 days, run:

mysql> set global expire_logs_days=4;
Query OK, 0 rows affected (0.00 sec)

After setting the expiry period, flush the logs to remove binary logs older than that limit.

mysql> flush logs;
Query OK, 0 rows affected (0.35 sec)

Check the binary logs again. There should now be fewer files.

mysql> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| mysqld-bin.002712 | 134217773 |
| mysqld-bin.002713 | 134217864 |
| mysqld-bin.002714 | 134217840 |
| mysqld-bin.002715 | 134217819 |
| mysqld-bin.002716 | 134217941 |
           ...
| mysqld-bin.002837 | 134217881 |
| mysqld-bin.002838 | 134217844 |
| mysqld-bin.002839 | 134217816 |
| mysqld-bin.002840 |   8238887 |
| mysqld-bin.002841 |    244952 |
+-------------------+-----------+
130 rows in set (0.00 sec)

Remember to update my.cnf too, adding the parameter below to [mysqld]. Otherwise expire_logs_days returns to its previous value after restarting MySQL.

[mysqld]
...
expire_logs_days=xx
...

xx is the number of days to retain binary logs. Another way to delete binary logs does not require changing MySQL parameters. However, the expiry period stays the same, so the logs may consume just as much space again. Commands for deleting binary logs: Delete binary logs based on a binary log filename:

mysql> PURGE BINARY LOGS TO 'mysql-bin.0xx';

Delete binary logs based on a date and time:

mysql> PURGE BINARY LOGS BEFORE '20013-04-02 22:00:00';