MySQL is a database built on underlying files. What happens if you look into your MySQL data directory and realize that huge BIN extension files keep getting added in, taking up dangerous amounts of disk space?
What Are MySQL Bin Files?
There are all sorts of files involved with MySQL. In my setup, I typically have a folder for each schema and within that a file for each table. It’s easy for me to glance at the tables and know that everything is running smoothly. All the files are within a reasonable size range.
In the main /MySQL Server/ Data directory, there are also overall MySQL files which control its general operation. It’s in that DATA directory that you might spot the BIN files.
These files traditionally have names along the lines of:
SERVERNAME-bin.000123
SERVERNAME-bin.000124
SERVERNAME-bin.000125
and so on. The “servername” will be whatever the defined name of your server is.
These BIN files are binary log files. They’re used to keep track of changes to your database to help with recovery or replication situations.
If you’re not running in a replication situation, and don’t need them for recovery after a backup, then you can stop creating them.
How To Stop Creating Infinite BIN Files in MySQL
It’s all about the my.ini or my.cnf, of course. That magical file controls most of what your MySQL software is doing.
Open up that file in your favorite text editor and look for the line something like this:
log-bin=”SERVERNAME-bin”
Add a pound sign before the line so it reads like this:
#log-bin=”SERVERNAME-bin”
Then restart the server. MySQL should now run without creating those BIN files.
Note that this will NOT delete any existing BIN files.
Alternatively, leave the log files generating, but also add a line so they auto-clear on a regular basis. So beneath the log-bin line, add in:
expire_logs_days = 14
How Do You Delete MySQL Bin Files?
To safely delete BIN files from MySQL, don’t just delete the actual files in the data directory. Instead, go into MySQL workbench and issue the command:
PURGE BINARY LOGS BEFORE ‘dateyouwant’;
That will properly cleanse out the BIN files before whatever date you choose, as long as the binary logs are in healthy shape.
If for some reason the purge isn’t working, stop your MySQL Server. Then look at the SERVERNAME-bin.index file to make sure it has everything it should have in it. If it doesn’t, fix it. Then restart your MySQL Server and try again.
Leave a Reply