Fixing Local MySQL Problems
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!