Understanding MySQL Binary Log: Functions, Cleanup Methods, and Switching

Binary logs in MySQL serve two main purposes: (1) replication, where the master’s binary log is sent to slaves to replay changes and achieve master‑slave synchronization; and (2) recovery, enabling point‑in‑time restoration beyond the moment captured by mysqldump or xtrabackup.

When binary logs grow rapidly due to large data volumes, they must be cleaned regularly. Four common methods are:

Method 1: Execute reset master; to delete all binary logs and restart numbering from 000001 .

Method 2: Use purge master logs to 'mysql-bin.000007'; to remove logs preceding the specified file.

Method 3: Run purge master logs before '2020-01-07 00:24:00'; to delete logs older than a given timestamp.

Method 4: Set the expire_logs_days parameter in the MySQL configuration file to automatically purge logs after a defined number of days.

Examples of file listings before and after purging illustrate the effect of these commands:

ls -lrt output showing mysql-bin.000001 through mysql-bin.000008 before purge, and only mysql-bin.000007 and mysql-bin.000008 after executing Method 2.

Switching binary logs can be done with flush logs or flush binary logs . After flushing, the current log file can be verified with show master status; , as demonstrated by the series of screenshots showing the log file name and position before and after each flush operation.

Overall, proper management of MySQL binary logs—understanding their purpose, regularly cleaning them, and correctly switching them—ensures efficient replication, reliable point‑in‑time recovery, and optimal storage usage.