Flush and Reset MySQL Binary Logs

Submitted by jbreland on Sun, 06/29/2008 - 05:12
Tips
Linux

I had an issue with free disk space (or, more appropriately, a lack there of) on my server a while back. After some investigation, I discovered that my MySQL databases had ballooned in size to nearly 10 GB. Actually, figuring out that the /var/lib/mysql directory was taking up so much space wasn't that hard, but understanding why and what to do about it took a while (yes, I'm sometimes slow about such things).

It turns out I had two issues. The first is that MySQL configuration, by default, maintains binary logs. These logs "contain all statements that update data or potentially could have updated it (for example, a DELETE which matched no rows). Statements are stored in the form of 'events' that describe the modifications. The binary log also contains information about how long each statement took that updated data."[1] This is fine and all, but (again by default) these log files are never deleted. There is a (configurable) max file size for each log, but MySQL simply rolls over to a new log when it's reached. Additionally, MySQL rolls over to a new log file on every (re)start. After a few months of operation, it's easy to see how this can take up a lot of space, and my server had been running for nearly four years.

Complicating matters somewhat was the fact that the default name of the binary logs changed at some point (and, according to the current docs, now appears to have changed back. As a result, I have several gigabytes worth of logs using the old naming convention, as well as several gigabytes worth of logs using the newer convention. Yay.

Like I said, recognizing that MySQL was taking up a lot of space is not hard, but I'm paranoid about my data and didn't want to risk losing anything. So, I kept putting it off until I was literally running out of space on a near daily basis. At that point I began doing research and figured out all of the above information. I also found a quick an easy way to fix the problem.

Note: This is meant for a standalone MySQL server. I'm not sure how it may affect replication, so please do not follow these instructions on a replicated server without additional research.

First of all, the binary logs typically reside in /var/lib/mysql/. You can check to see how much space they're currently taking up with this one-liner: du -hcs /var/lib/mysql/*bin.* | tail -n 1. If it's more than a few hundred megabytes, you may want to continue on.

Next, check to see if you were affected by the name switch like I was. This is unlikely unless you've been running the server for at least a year or so, but it definitely doesn't hurt to check. Look at all *bin.* files. If they're all named the same, such as mysqld-bin.000001, then you're fine. If you see some with a different name, such as both mysqld-bin.000001 and hostname-bin.000001, then you have an outdated set of logs doing nothing but taking up space. Look at the timestamps of the .index file for each set. One should be very recent (such as today), the other not. Once you've identified the older set, go ahead and delete all of them; they're no longer being used.

Finally, for the current set, login to MySQL as an admin user (eg., mysql -u root -p). You'll want to run the following two commands:
mysql> FLUSH LOGS;
mysql> RESET MASTER;

That's it. Depending on the size and number of your logs, those two commands may take a while to run, but the end result is that any unsaved transactions will be flushed to the database, all older logs will be dropped, and the log index will be reset to 1. In my case, these two steps dropped my from 9.6 GB down to about 5 MB. Good stuff.

Of course, this is simply a workaround to the problem, not a proper solution. What I'd really like to do is either automate this process so that I don't have to worry about the logs getting out of control, or even better configure MySQL to automatically flush its own logs after some period of time or it reaches a certain total file size. I haven't found any way to do this just yet, though I admittedly haven't looked too hard. I'd appreciate any recommendations, though.