Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
Yes. I've tried this. Not working. –  lamp_scaler Jul 26 '13 at 8:45
    
PURGE BINARY LOGS TO 'mysql-bin.000025'; When you run this what is the error. –  Abdul Manaf Jul 26 '13 at 8:46
    
Yes. I've tried this. Not working. –  lamp_scaler Jul 26 '13 at 8:47
    
I have updated my answer.You can try RESET MASTER. –  Abdul Manaf Jul 26 '13 at 9:00
    
When I do that, it returns: "ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER". What to do? –  lamp_scaler Jul 26 '13 at 9:02
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.