Falling Leaves: the Ardes blog

Archives filed under "mysql"

Fixing Local MySQL Problems

Ray Drainville

Wow! It’s been quiet here. That’s for a lot of reasons, but partly because a) I’ve been getting over a long-term illness that had really had an effect on my work (which is now gone, thankfully) and b) my Mac suffered a nasty failure. It experienced weird symptoms, which were fixed when the graphics card & RAM riser boards were replaced. Good thing I got AppleCare for the machine.

In any event, while the machine was in the process of breaking, I realised that my local development databases weren’t backed up. Why? Because Apple’s Time Machine would want to back up hulking single-file monstrosities every time you modified it slightly. In my haste (and panic), I made an imperfect backup. When the databases were restored onto the machine, they wouldn’t work. I could see that they were there, the databases were listed, but attempts to access them inevitably resulted in failure.

It turns out to have been a permissions issue (Errno 13 in MySQLspeak: I now know that gzipped tarballs won’t preserve permissions). It took some hunting to get to the root of the problem, but if anyone else experiences this problem, the answer is that the MySQL data directory is likely to be owned by root, when it needs to be owned by MySQL. In my example, I’m assuming that the data directory is the one specified in a MacPorts installation:

    sudo chown -R mysql:mysql /opt/local/var/db/mysql5

Restart your MySQL server, and everything should work!

OS X Developer Tip: Slim Time Machine Backups

Ray Drainville

If you’re using Mac OS X and you’re a developer and you’re too busy working to think about backing up your work, then Time Machine is fantastic & a lifesaver. Time Machine will automatically back up your work on an hourly basis. Coupled with a Time Capsule—which provides both the storage for those backups and your wireless connection—backing up is seamless, which is as it should be: if it isn’t seamless, you’re not going to back up nearly as often as you should.

However, Time Machine is not without its faults. I found it frustrating to set up initially, as I was setting up individual accounts (with passwords) & the setup for this is not clearly laid out. Worse, however, is that you can’t set up default rules—like ignore any directory named logs (those developing in Rails will know the files in logs can get huge), although you can set directories (or files) to be ignored manually.

Worst is the lack of any idea of what Time Machine is backing up. When you’re a developer, this can be very frustrating, as periodically you’ll find that you’re suddenly backing up gigabytes of data without knowing why. This is bad: when Time Machine runs out of space, it removes the oldest backups, so you can’t go “back in time” as far as you may need to. For instance, my last backup is 4 October when it should be something back in June, when I first bought the Time Capsule. Something was eating up space.

You could adopt a Zen attitude to this: let it do its thing, accept this state of affairs & doubtless you’ll be happier—until your Time Capsule is filled, that is. Or, you could be obsessive-compulsive & make yourself miserable trying to figure out wtf is going on. TimeDog helps you see something of what’s been backed up, but I found it unhelpful as it wasn’t letting me know about the latest backups—in my case at least, it only appears to give you information about the initial backup. Well, that’s not helpful.

Last night, however, the right chain of events occurred that provided the solution—for web developers, at least. I was performing a MySQL sync for one of my clients—in this instance, I also sync the database locally so I can check my work against the client’s actual data. Time Machine started up just after I started syncing locally. At first it was backing up about 2.5MB. Suddenly, however, it was backing up over 2GB worth of files. Strange, I thought—I had excluded the db folder on the Rails project, so the synced YML files shouldn’t be backing up.

And then it hit me: Time Machine was backing up the actual updated MySQL databases. Obvious, really, when you think about it. For Mac developers, your MySQL databases will be in one of two places:

  • If you’re using the default OS X setup, /var/db/mysql;
  • If you’re using MacPorts, /opt/local/var/db/mysql.

All you have to do to remove these from backups is to go to Time Machine preferences, click “Options…” & exclude the above directories.

You may want to prune more selectively, however: for me, the db sync only really affects one particular client. You can go into the mysql directory & merely prune out the database that’s filling up your Time Machine. Please note that this database won’t be backed up, however—you may want to back it up manually at specific intervals.