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:
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.RESET MASTER
was not intended to be used while any replication slaves are running. The behavior ofRESET MASTER
when used while slaves are running is undefined (and thus unsupported), whereasPURGE 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 whichRelay_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.