Why do the MySQL bin log files still exist after a purge or flush?

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or timestamp. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.

I hope you have purged binary logs upto mysql-bin.000019 using command

PURGE BINARY LOGS TO 'mysql-bin.000019';

If you need to purge all logs do like

PURGE BINARY LOGS TO 'mysql-bin.000025';

This will remove binary logs upto mysql-bin.000025.

UPDATE

You can try

RESET MASTER;

RESET MASTER Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file

The effects of RESET MASTER differ from those of PURGE BINARY LOGS in 2 key ways:

  1. RESET MASTER removes all binary log files that are listed in the index file, leaving only a single, empty binary log file with a numeric suffix of .000001, whereas the numbering is not reset by PURGE BINARY LOGS.

  2. RESET MASTER was not intended to be used while any replication slaves are running. The behavior of RESET MASTER when used while slaves are running is undefined (and thus unsupported), whereas PURGE BINARY LOGS may be safely used while replication slaves are running.

CAVEAT by RolandoMySQLDBA

If you run RESET MASTER with Slaves connected and running, the IO Thread of each Slave will immediately lose its place. Replication is thus broken and you will have to spend time getting the data on all Slaves sync'd again. If you want to safely delete binary logs from a Master without breaking Replication integrity, here is what you do:

  • Run SHOW SLAVE STATUS\G on each Slave.
  • Take note of Relay_Master_Log_File. This is the binary log whose latest statement was successfully executed in the Slave).
  • From all displays of SHOW SLAVE STATUS\G, determine which Relay_Master_Log_File is the oldest (For example, 'mysql-bin.00123').
  • You can run PURGE BINARY LOGS TO 'mysql-bin.00123'; None of the Slaves will lose its place.

The overall effect? This will leave behind binary logs on the Master whose statements that have not been executed on all Slaves as of yet.


I'm not sure if this is what happened to you but in my case MySQL had stopped "cycling" logs and the mysql-bin.index file had become "corrupted" with invalid binlog file entries.

Specifically, the index file had started at mysql-bin.000001 and got to mysql-bin.000220 but then had somehow started again from 001. When I compared this to the files on my server I could see that I had files from 001 to 022.

At first I tried PURGE LOGS TO 'mysql-bin.000022'; but this didn't work.

In the end I stopped MySQL and manually edited the index file until it matched the files I had on my server. When I restarted MySQL it cleaned up the binlog files to respect the expire_logs_days setting and started working normally again.


In my case PURGE BINARY simply wasn't deleting anything.

I had my partition at 100% usage (I had to clean up my slowqueries log a bit so that there was enough space to mysql to be restarted), so the first thing I did was change /etc/my.cnf to comment the line log-bin=mysql-bin (it wasn't needed in this server anymore and I had forgotten to remove it), and then I restarted mysql (this was needed because there were queries queued that were preventing PURGE BINARY from being executed).

After that, I ran PURGE BINARY but nothing happened. So I read the manual and found out that:

This statement has no effect if the server was not started with the --log-bin option to enable binary logging.

So I reenabled log-bin=mysql-bin in my /etc/my.cnf, restarted, PURGED the files (with success now), commented the line again and then restarted. After this the files were removed and no longer created.