How to rotate MySQL binary logs manually July 23, 2025

MySQL binary logs record all changes to the database and are important for replication and point-in-time recovery. Over time, these logs can grow large and consume disk space. While MySQL can rotate these logs automatically, there are situations where you may need to rotate them manually by forcing MySQL to stop writing to the current log and start a new one. This guide shows you how to rotate MySQL binary logs using a simple SQL command manually.

MySQL offers two primary methods for manually managing binary logs: flushing to rotate logs and purging to remove old logs. Here’s how both work:

1. Flushing Binary Logs
   You can manually rotate the current binary log using either the SQL command or the “mysqladmin” utility:
   1. SQL Method:
      This command closes the existing binary log file and opens a new one with the next consecutive number.

      FLUSH BINARY LOGS;

   2. mysqladmin Method:
      This achieves the same result through the command line:

      mysqladmin -u root -p flush-logs binary

      Replace “root” with a user who has the RELOAD privilege. You will be prompted to enter the password.

   3. To flush all logs (error, relay, binary, etc.), use:

      FLUSH LOGS;

      or

      mysqladmin flush-logs

      Note: Flushing logs requires the RELOAD privilege.

2. Purging Binary Logs
   To free up space by deleting old binary logs, use the PURGE BINARY LOGS statement. There are two options:
   1. Purge by filename:

      PURGE BINARY LOGS TO ‘mysql-bin.000142’;

      This deletes all binary logs before the specified file.

   2. Purge by date:

      PURGE BINARY LOGS BEFORE ‘2025-07-28 15:00:00’;

      This removes all binary logs created before the given date and time.
      Important: Never remove binary logs needed by replication slaves or required for point-in-time recovery. Always back up logs before removing.

Important note:
Before rotating or deleting MySQL binary logs, confirm that all replicas have processed the logs to avoid replication issues. Also, verify that backups are complete for recovery purposes. Additionally, use manual rotation to quickly free up disk space when logs grow large.

You can also set up automatic binary log rotation and purging using system variables like max_binlog_size, expire_logs_days, or binlog_expire_logs_seconds.

We hope you found this article helpful. For further assistance, feel free to reach out to our support team anytime.

Clearing DNS issues? Learn How to flush the local DNS cache in linux server