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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those files like mysql-bin.000123, mysql-bin.000223 etc. I have checked the mysql replication by doing show master status and show slave status they are using some mysql-bin files at certain positions, but I guess all the other bin files are leftovers which will not be used anymore. In this case is it safe to delete all those mysql-bin files except the ones that replication is currently pointing at ?

If it is safe to delete, then is there anything I could do to automatically delete those files once they are not in use ?

share|improve this question
up vote 53 down vote accepted

Please do not just delete them in the OS.

You need to let mysqld do that for you. Here is how mysqld manages it:

The file mysql-bin.index keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.index are:

PURGE BINARY LOGS TO 'binlogname';

These will clear all binary logs before the binlog or timestamp you just specified.

For example, if you run

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

this will erase all binary logs before 'mysql-bin.000223'.

If you run


this will erase all binary logs before midnight 3 days ago.

If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:

mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf


and mysqld will delete them logs for you


This is critical. When you run SHOW SLAVE STATUS\G, you will see two binary logs from the Master:

  • Master_Log_File
  • Relay_Master_Log_File

When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File is, and go back to the Master and run

PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';

That way, replication is not interrupted.

Give it a Try !!!

share|improve this answer
Argh, you beat me! +1 of course, though I would remove the bit about RESET is dangerous, especially as you noted in a running replication scenario. – Derek Downey Apr 26 '13 at 22:00
Agreed, otherwise it is an open invitation to shoot yourself in both feet. RESET MASTER removed. – RolandoMySQLDBA Apr 26 '13 at 22:02
Your answers are detailed and clear as always, I idolize you to be DBA someday. – Jhilke Dai Apr 26 '13 at 22:40
+1 for expire-logs-days=3 I never seen that option before. – Babblo Apr 24 '14 at 18:14
Please note a typo - underscores, not dashes: [mysqld] expire_logs_days=3 (and you must include the [mysqld] section – changokun Aug 12 '14 at 17:23

This really depends on your backup strategy. One of the main reasons to keep the binary logs around is to restore your database to a 'point-in-time'. If your database crashes and requires restoration, you would restore the latest full backup, and then play back the binary logs starting with the position of the full backup.

So, if you do a full backup every day and you have 7 days worth of binary logs, it is likely that you can delete the past 4-6 days worth of binary logs. You can control how many days worth of binary logs are kept with the expire_logs_days setting.

You can delete the binary logs you don't need by first seeing what is the oldest log you want to keep:

ls -lh /path/to/binary/logs/mysql-bin.0*

and then in mysql:

mysql> PURGE BINARY LOGS TO 'mysql-bin.XXXXX';
share|improve this answer
+1 for add the backup scenario as an additional persepctive on binlogs. – RolandoMySQLDBA Apr 26 '13 at 22:03
Thank you for detailed info, I wish I had enough reputation to upvote. Yes we have nightly backup everyday, and, now it looks clear for me to remote those bin files safely. – Jhilke Dai Apr 26 '13 at 22:35
This DB is replicated so that latest data is in secondary (or slave) DB. In this replication environment, binary-logs are not necessary to keep backup-period, right? – Fumisky Wells May 18 '15 at 5:47

Your Answer


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.