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.<\/p>\n
MySQL offers two primary methods for manually managing binary logs: flushing to rotate logs and purging to remove old logs. Here\u2019s how both work:<\/strong><\/p>\n Replace \u201croot\u201d with a user who has the RELOAD privilege. You will be prompted to enter the password.<\/li>\n or<\/p>\n Note: Flushing logs requires the RELOAD privilege.<\/li>\n<\/ol>\n<\/li>\n This deletes all binary logs before the specified file.<\/li>\n This removes all binary logs created before the given date and time. Important note:<\/strong> 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.<\/p>\n We hope you found this article helpful. For further assistance, feel free to reach out to our support team anytime.<\/p>\n\n
\nYou can manually rotate the current binary log using either the SQL command or the \u201cmysqladmin\u201d<\/strong> utility:<\/p>\n\n
\nThis command closes the existing binary log file and opens a new one with the next consecutive number.<\/p>\nFLUSH BINARY LOGS;<\/pre>\n<\/li>\n
\nThis achieves the same result through the command line:<\/p>\nmysqladmin -u root -p flush-logs binary<\/pre>\n
FLUSH LOGS;<\/pre>\n
mysqladmin flush-logs<\/pre>\n
\nTo free up space by deleting old binary logs, use the PURGE BINARY LOGS statement. There are two options:<\/p>\n\n
PURGE BINARY LOGS TO \u2018mysql-bin.000142\u2019;<\/pre>\n
PURGE BINARY LOGS BEFORE \u20182025-07-28 15:00:00\u2019;<\/pre>\n
\nImportant: Never remove binary logs needed by replication slaves or required for point-in-time recovery. Always back up logs before removing.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
\nBefore 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.<\/p>\n