1

I am running a MySQL server on Windows 8. The MySQL directory requires 118 GB of space and has a lot of 1 GB mysql-bin files that haven't been modified for years according to the file system.

All of my databases combined are not large enough to warrant a 118 GB footprint so I suspect the server failed to delete some old bin files.

How can I only delete all of the mysql-bin files that are no longer in use by existing databases?

1

First thing you should check is if binary logging is still enabled

mysql> SHOW BINARY LOGS;
mysql> SHOW MASTER STATUS;

These two commands will give you the list of binary logs it sees and the then the current binary log;

I cannot tell you how to delete the logs by database usage, but I can tell you three other ways:

1) DELETE ALL LOGS

The command to hose every binary log is

mysql> RESET MASTER;

That's all.

2) DELETE LOGS BY BINLOG NAME

If you see 118 binary logs, you can select which log to delete up to. For example, if the binary logs have the form mysql-bin.000118, you can delete up to the 100th log using

mysql> PURGE BINARY LOGS TO 'mysql-bin.000100`';

3) DELETE LOGS BY DATE AND TIME

Suppose you want to delete all logs but keep everything from midnight 5 days ago. Run this:

PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND - INTERVAL 5 DAY;

or

PURGE BINARY LOGS BEFORE '2014-07-11 00:00:00';

This will let mysqld figure out which binary logs to delete.

GIVE IT A TRY !!!

1
  • Interestingly, I get the error ERROR 1381 (HY000): You are not using binary logging when I try to SHOW BINARY LOGS – eComEvo Jul 11 '14 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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