Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I've used PURGE BINARY LOGS as well as FLUSH LOGS, but the mysql directory still contains these files:
mysql-bin.000025
mysql-bin.000024
mysql-bin.000023
mysql-bin.000022
mysql-bin.000021
mysql-bin.000020
mysql-bin.000019
mysql-bin.index
Is there a reason why using the commands are not working? These files are taking a lot of space. I would like to get rid of them safely.
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 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.
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:
SHOW SLAVE STATUS\G on each Slave.Relay_Master_Log_File. This is the binary log whose latest statement was successfully executed in the Slave).SHOW SLAVE STATUS\G, determine which Relay_Master_Log_File is the oldest (For example, 'mysql-bin.00123').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.
CHANGE MASTER TO, it deletes all relay logs. If Relay_Master_Log_file is mysql-bin.00123, that's the oldest binary log on the Master that the Slave knows about. If mysql-bin.00123 no longer exists on the Master, you could lose the proper place to replicate from if you run any CHANGE MASTER TO on the Slave that does not reference newer logs. This can easily be overlooked and you end up manually breaking replication.
Nov 11, 2014 at 12:27
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.
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.
PURGE LOGS only works under the ideal setup: 1) when all binary logs are consecutive, 2) all binary logs are named in the mysql-bin.index file, 3) There are no extra logs not mentioned in mysql-bin.index. +1 !!!
Nov 11, 2014 at 12:44
This works for me: (MySQL server version: 5.6.14)
PURGE BINARY LOGS BEFORE NOW();
Removes all binary logs on my system.
You can easily delete ALL logs with:
PURGE BINARY LOGS BEFORE NOW();
Or replace func NOW() by any date in quotes:
PURGE BINARY LOGS BEFORE '2018-02-15 00:00:00';
Or you can automate this action with expire_logs_days = 10 in my.cnf. By default expire_logs_days is 0 = never delete logs.
(source)
