Home / server / Questions / 611416
Accepted
eComEvo
Asked: 2014-07-10 16:37:37 +0800 CST

Finding and removing old mysql-bin files

  • 772

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?

windows

1 Answers

  1. Best Answer

    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 !!!

    • 2