The short version: it’s a bit bloody hairy! I’d avoid it if I were you.
The long version:
I was running MySQL 4.0, and trying to get Django to work.
Running ./manage.py syncdb, it said “server is too old to set charset”
It seemed to need 4.1 to get some Django stuff working, so thought I’d upgrade. I’m running Drupal 4.7 on the same server, which is a Mac running 10.3.9. I was (fairly) confident that Drupal was backed up ok, so decided to just download the DMG for MySQL 4.1 and give it a whirl.
So, after installing and restarting, I tried to access my website - hello? anyone there? Oh bollocks… no website.
Looking at the /usr/local/mysql/data/ folder I was horrified to discover that all my databases had disappeared. Brown trousers time…
At this point I should point out that I am an idiot. If this happens to you, don’t panic. The installer has not eaten your databases, which is what I thought it had done!
If you look in /usr/local/ you will discover that mysql is aliased to the actual installation, for example mysql-max-4.1.22-apple-darwin7.9.0-powerpc. I didn’t realise this, and I had the directory aliased from somewhere else too, so I thought my databases were gone.
I was remarkably calm. Oh well, I said, time to discover if the backup script actually works.
I am using mysqlhotcopy to back my databases up, and I’d never had to actually do a restore (I can’t believe I’m saying that - I hadn’t tested my backups - but then testing them isn’t that easy when you have no time and no spare server). Anyway, I had no idea how to turn the backup back into a working database.
The answer, in case you are wondering, is remarkably simple - copy it back into the right place. Wow - it’s almost like using a Mac (apologies to the hardened unix geeks out there, who must be wondering what kind of an idiot I am. The answer is, I am a very technical idiot who, due to a career spent programming macs and the occasional pc, has never had to deal with any of this shit before). I love that the Mac is based on unix now, I really do. I just wish unix would catch up sometimes…
Anyway, I copied the backup back into the right place, and as luck would have it, it seemed to work.
Of course, I had to spend a while fixing up all the sql users and passwords and privileges and all of that malarky. I always forget my MySQL root password for some reason, which really doesn’t help. Lucky I wrote it down ;)
So, I had the databases back, and everything should be hunky dory. Hit reload in the web browser, and… Drupal was complaining that it didn’t have access. To be precise, it was saying “Client does not support authentication protocol requested by server; consider upgrading MySQL client”. Eh?
I rummaged around a bit, and started thinking, uh-oh, maybe Drupal 4.7 doesn’t work with MySQL 4.1. After a bit of searching, I got the vague impression that there were some problems, but there seemed no clear solutions.
Right, I figured. In for a penny… lets upgrade Drupal to the very latest 4.7 variant, that’ll fix it.
So I did, and it didn’t.
Oh so it, maybe Drupal 5.1 will fix it.
It didn’t.
Oh, I’ve found a note saying you have to go to 5.1 via 5.0. I’ll install that.
It didn’t help.
At this point I was wishing that I hadn’t started. However, a bit more googling finally gave me this: http://drupal.org/node/13977. Aha! Nothing to do with Drupal at all.
Turns out that the password format has somehow changed and you have to do a bit of mucking around to fix it. Ok, fine, I can do that. So try again, and thank something or other, it worked. The web site came back.
Whoohooo.
This is probably where I should have stopped… except that when I tried to log in to the site to blog all of this, I couldn’t. It just silently failed to log me in, and I ended up back on the log in page. What???
I had a vague notion that a similar passwordy sort of problem was perhaps occuring, this time with Drupal’s own user table, but I couldn’t get it to work and I didn’t know how to change Drupal’s own password entries with SQL. I had gone back to 4.7 by this time, but I still couldn’t log in.
I tried running the various Drupal update scripts for various different versions of Drupal, but no joy. In fact, they didn’t work because of various other permissions problems. Some of them turned out to be the fact that I hadn’t got the permissions for the database files right when I restored them.
Ah well, thought I. I’m in a right pickle now, but at least the site sort of works. What the hell, maybe MySQL 5.0 will fix it. You can tell I was feeling optimistic tonight.
So, another DMG install later, and no further on. Actually, things were worse. Drupal 4.7 wasn’t talking to the database again, at all.
Luckily it was around this point that I realised that the multiple MySQL installations were happily residing in different directories on my server, along with my original databases. This is exactly how I’d expected it to work in the first place, I’d just got a bit confused.
So, realising that I still had my original installs of MySQL, Drupal, and the original data files, I figured that I could probably just get things working as they originally were.
I did. It works.
Interesting evening that.
The only problem is, I still can’t get Django to talk to MySQL. Arse.