webchick.net

very helpful lioness

my.cnf: Beware of hard drive crushing binary logs

Wed, 02/03/2010 - 00:07 -- webchick

If you've tried to import a Drupal database dump with the cache table data still intact, you've probably at one point or another run into a "max_allowed_packet" error coming from MySQL. An easy way to address this is to increase the setting in my.cnf.

About a year ago I hit this issue, and came across this helpful blog post that explained how to copy one of the default my-XXX.cnf files into MAMP. I thought it was real cute how these files talk about servers with 64M-128M of RAM as being "medium" so I copied my-large.cnf or something in, tweaked the setting, and went on with my day.

Lately though, I've been getting out of disk space messages, which I've always found puzzling, since 99.9% of what I do on this machine is Drupal. And hey, I know jQuery UI is pretty massive, and I have a few Drupal 7 checkouts laying around, but what the heck? :P I started deleting things like old VMs I wasn't using anymore. But finally today I was down to about 200MB (?!?).

Little did I know, that if you copy in there anything other than my-small.cnf, this seemingly innocuous line is uncommented:


log-bin=mysql-bin

This turns on "binary logging" which means that MySQL will proceed to "helpfully" log every. single. insert/update/delete/create/etc. query you do. This is wonderful if you're running your own MySQL master/slave replication set-up, but not very useful if you're on a laptop running MAMP. :P

After taking a quick spin with Disk Inventory X, lo and behold I had accumulated 58 GB (!) of these mysql.bin.29837923 log files over the course of the past year.

The good news is that my laptop is screaming fast now. ;) But... yeah. Watch your my.cnf settings, folks! :)

Comments

Submitted by catch (not verified) on

I did this on my very first VPS, which had about 40gb of space and got filled up very, very quickly.

I've also heard of at least one case where it caught out a large-scale production site which had just been launched on Drupal (a couple of years back) with real sysadmins and everything, so this advice is good not only for localhost!

I have been hit with mysql binary logs filling up a drive too. Another good reason to keep your data on a different volume for prod servers at least.

Thanks for sharing!

Submitted by webchick on

These files come from upstream MySQL. I think it's safe to assume they would contain sensible defaults. And it's not like this:


# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

Says anything like "Oh, and by the way, if you don't want your hard drive to be completely consumed by dozens of 1GB files, you should probably run a script on cron that prunes these periodically. kthxbai."

Still though, I might contribute a patch upstream that makes this a bit more obvious to people. Sounds like I'm not the only one to get nailed with this.

But if you know they are there then they can become a big lifesaver. Just a couple of weeks ago I was doing some work on a Drupal module I'm developing for a client. It incorporates election/candidate data and the tables were pretty complex. Well in my lack of caffeine crazed mind one morning I accidentally dropped the table in phpmyadmin. Luckily my binary log came to the rescue and I didn't have to sit there and remember all the columns and indexes I came up with for the thing.

The key is to do a flush-logs every so often. I have cron running a shell script on my dev box at 3am everyday that does the flush-logs then checks for binlogs older than 3 months and deletes them. It's saved my butt a few times in the past, mostly from that whole lack of caffeine DOH moment.

Submitted by webchick on

Cos yeah, I can definitely see how these would be useful in "OOPS!!" circumstances like that.

Sure you need to ensure that all logs are cleaned out periodically

This is tickier with mysqlbinlog because you really need to take a coherant backup that matches the start of the binlog (you don't want a copy of node that is a second behind node_revisions)

As well as being required for replication mysqlbinlog has saved my job on one occasion.

One day I inadvertantly run an UPDATE query without the requisite WHERE clause - and to make it worse I didn't notice till I got complaints days later.

So I restored from backup - lost a few days data.

So I took the data from mysqlbinlog - ran all the updates from the backup point to the moment before the error - skipped that line - and carried on.

There were a few identifiable records that needed manual correction (few enough that I could contact users and ask them to check) - but basically it allowed me to undo a 3 day old mistake.

Don't turn the binlog off - set up a cron job to delete the old files occasionally.

So if you wanted to keep logging for emergency scenarios, ala the above replies, what's the best way to flush current logs? Can you safely run
rm -rf /Applications/MAMP/db/mysql/mysql-bin.*
Or is there a mysql/bash script for flushing logs?

Submitted by Andy Laken (not verified) on

Serendipity! A few days ago I was cleaning off the HD of my MacBook Pro, wondering where all my disk space had gone. Turns out I had 25GB of mysql.bin.123456 files, probably because a year or two ago I did exactly what you did, and changed my.cnf to avoid the max-allowed-packet error.

This post came in handy: http://edgedesigns.org/2009/03/24/mamp-tips/

I'm going to bookmark this thread for the helpful comments others have left about how to manage the binary log.

Submitted by Mark (not verified) on

Thanks Angie. I ran into this exact issue today and I thought immediately of this blog post.