How to purge binary logs in MySQL or MariaDB

Binary logs can quietly consume disk space on busy MySQL and MariaDB servers, eventually triggering write failures or service interruptions when the filesystem fills. Purging old binary logs reclaims space while keeping replication and recovery features usable.

When binary logging is enabled (log_bin), each transaction is appended to a sequence of binary log files (for example, binlog.000001, binlog.000002). The server tracks them through a binary log index file and uses them for replication and point-in-time recovery. Purging via SQL removes files safely and keeps the index consistent.

Binary log deletion is irreversible and can break replication or backup restore plans if required logs are removed. Confirm replicas have processed the logs slated for removal and keep a retention window aligned with recovery requirements. Avoid deleting binary log files directly from the filesystem, since that can desynchronize the server from its index.

1. Open a mysql client session using an account allowed to manage binary logs.

   $ mysql --user=root --password
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   mysql>

   MySQL 8.0 commonly accepts BINLOG_ADMIN (or legacy SUPER) for purging, while SHOW BINARY LOGS often requires REPLICATION CLIENT.

2. Confirm binary logging is enabled on the server.

   mysql> SHOW VARIABLES LIKE 'log_bin';
   +---------------+-------+
   | Variable_name | VALUE |
   +---------------+-------+
   | log_bin       | ON    |
   +---------------+-------+
   1 ROW IN SET (0.00 sec)

3. Record the binary log file prefix used for new log files.

   mysql> SHOW VARIABLES LIKE 'log_bin_basename';
   +------------------+-----------------------+
   | Variable_name    | VALUE                 |
   +------------------+-----------------------+
   | log_bin_basename | /var/lib/mysql/binlog |
   +------------------+-----------------------+
   1 ROW IN SET (0.00 sec)

   The log files typically share the same prefix as log_bin_basename, plus a dot and sequence number like binlog.000123.

4. Record the binary log index file path used to track existing log files.

   mysql> SHOW VARIABLES LIKE 'log_bin_index';
   +---------------+----------------------------+
   | Variable_name | VALUE                      |
   +---------------+----------------------------+
   | log_bin_index | /var/lib/mysql/binlog.index |
   +---------------+----------------------------+
   1 ROW IN SET (0.00 sec)

   Manual deletion of binary logs from the filesystem can leave log_bin_index pointing at missing files.

5. List the current binary log files and sizes.

   mysql> SHOW BINARY LOGS;
   +---------------+-----------+
   | Log_name      | File_size |
   +---------------+-----------+
   | binlog.000121 |  10485760 |
   | binlog.000122 |  10485760 |
   | binlog.000123 |   2097152 |
   +---------------+-----------+
   3 ROWS IN SET (0.00 sec)

6. Capture the active binary log file currently receiving writes.

   mysql> SHOW MASTER STATUS;
   +---------------+----------+--------------+------------------+-------------------+
   | File          | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +---------------+----------+--------------+------------------+-------------------+
   | binlog.000123 |  2097365 |              |                  |                   |
   +---------------+----------+--------------+------------------+-------------------+
   1 ROW IN SET (0.00 sec)

   The File value is a convenient purge boundary, since PURGE BINARY LOGS keeps the named file and removes only older files.

7. Rotate to a new binary log file to create a clean purge boundary.

   mysql> FLUSH BINARY LOGS;
   Query OK, 0 ROWS affected (0.01 sec)

   Rotation is useful when the active log file should be removed during the purge.

8. Capture the new active binary log file after rotation.

   mysql> SHOW MASTER STATUS;
   +---------------+----------+--------------+------------------+-------------------+
   | File          | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +---------------+----------+--------------+------------------+-------------------+
   | binlog.000124 |      157 |              |                  |                   |
   +---------------+----------+--------------+------------------+-------------------+
   1 ROW IN SET (0.00 sec)

9. Check the replication execution position on each replica.

   mysql> SHOW REPLICA STATUS\G
   *************************** 1. ROW ***************************
   Replica_IO_Running: Yes
   Replica_SQL_Running: Yes
   Relay_Master_Log_File: binlog.000124
   Exec_Master_Log_Pos: 157
   ##### snipped #####

   MariaDB and older MySQL commonly use SHOW SLAVE STATUS\G with Slave_IO_Running and Slave_SQL_Running fields.

   Relay_Master_Log_File (or Master_Log_File on some versions) must be at least the purge boundary file on every replica before older logs are removed.

10. Purge all binary logs older than the chosen purge boundary file.

    mysql> PURGE BINARY LOGS TO 'binlog.000124';
    Query OK, 0 ROWS affected (0.02 sec)

    Purging logs still required for replication or point-in-time recovery can break restores and stop replicas from catching up.

11. Verify the binary log list starts at the expected file after the purge.

    mysql> SHOW BINARY LOGS;
    +---------------+-----------+
    | Log_name      | File_size |
    +---------------+-----------+
    | binlog.000124 |       157 |
    +---------------+-----------+
    1 ROW IN SET (0.00 sec)

12. Configure automatic binary log expiration for future log files.

    [mysqld]
    binlog_expire_logs_seconds = 604800

    Older MySQL or MariaDB installations may use expire_logs_days = 7 instead of binlog_expire_logs_seconds.

13. Confirm the configured expiration variables match the intended retention policy.

    mysql> SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
    +----------------------------+--------+
    | Variable_name              | VALUE  |
    +----------------------------+--------+
    | binlog_expire_logs_seconds | 604800 |
    +----------------------------+--------+
    1 ROW IN SET (0.00 sec)
     
    mysql> SHOW VARIABLES LIKE 'expire_logs_days';
    +-----------------+-------+
    | Variable_name   | VALUE |
    +-----------------+-------+
    | expire_logs_days| 0     |
    +-----------------+-------+
    1 ROW IN SET (0.00 sec)

    Only one retention mechanism should be used at a time when both variables exist.

14. Reset all binary logs only in non-replicated environments where a full binlog history is not required.

    mysql> RESET MASTER;
    Query OK, 0 ROWS affected (0.03 sec)

    RESET MASTER removes all existing binary logs and resets numbering, which breaks replication and invalidates backup workflows that rely on prior binary logs.