Clearing out MySQL binary logs
Over the past few weeks WHM has been sending me emails that my server space was getting full. I guess 80% is the threshold for those emails. But it was creeping up so slowly that over the course of 8 or 10 weeks it had only gone from 80% to 82% which I figure is normal with log files growing and somewhat regular posting to the half dozen blogs on my little VPS. This morning the email told me 87% which seemed like a big jump.
After poking around in SSH trying to find the culprit, I found that my MySQL data directory was about 1.5gb (I’ve only got 6gb with my package) and most of that wasn’t the databases. There were a series of files called ServerName- bin with a 6 digit extension. Turns out these are binary logs which I don’t fully understand the point of, but I didn’t want to just delete them. But I wanted the 1.3gb of space back that they were taking.
MySQL has a command to clear out old binary logs called PURGE MASTER LOGS but it takes a date to purge before. I found a little trick to make it easier to purge x number of days ago.
-
PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);
This will purge all logs older than 7 days. Note that it’s “7 DAY” and not “7 DAYS” - that’s caught me a few times. If you want to purge older than a month just replace 7 DAY with 31 DAY.
The full syntax is available on the MySQL page for PURGE MASTER LOGS.
And I’m back down to 71% usage and the databases still seem to be working, so it looks like it worked.
Question, Comments...
Do you have more questions. Please either leave a comment below or join us in our new forum.