WOW what a cluster. Before I even go on, may I suggest that if you can possibly do this on a virtual server so that you can revert to a snapshot when you fubar it you'll be most pleased.
So I've had a web server at 5.0.34 for quite a while. Works great, but I've wanted to use some new features , specifically SIGNAL so that I can have true FK constraints on my databases. I've never had a problem upgrading MySQL before, pretty much it's always been a binary upgrade (executable), fire it up and off it goes. Troubles have all been with the compile, not the data.
Well that changed with this path. Reading a bunch of threads it seemed clear that I needed to upgrade through 5.1 to get to 5.5. Although a few people said they did it OK there seemed to be enough pitfall posts that I decided to do it serially.
The MySQL site says that when moving between 5.0 -> 5.1 and then again 5.1 -> 5.5 I need to do a dump/restore of the database rather than just a simple mysql_upgrade. This is because the shape of the data columns is significantly different enough that it can cause problems in the data. In some cases it can bomb the server entirely.
So the first trouble I had was doing a dump of the data in 5.0. The command is quite simple:
mysqldump -u(user) -p(pass) --all-databases > dump.sql
My databases had some corruptions and needed to be repaired. This is equally simple and I've done it a bunch of times:
mysqlcheck -r -A -u(user) -p(pass)
But still I was getting an error with the dump - that I couldn't lock a system table (sorry, can't remember what it was now). Adding --single-transaction to the dump fixed that:
mysqldump -u(user) -p(pass) --all-databases --single-transaction > dump.sql
So far so good. I then downloaded the 5.1 tarball, put it in my default install place (/usr/local/installs/) unpacked it and got to work. I did a normal configure/make/make install cycle to find that the default prefix changed between 5.0 and 5.1
(caveat: this database was original a 3.2 that has been upgraded a bunch of times - I'm not sure at all if the prefix changed at 5.0 or earlier, but for me it was different). Where my mysql was installed at /usr/local/mysql, the new prefix is /usr/local/ I don't like it, prefer the old - so I modified the configure with my normal procedure - a configure.me file that has whatever options I prefer. This is most handy with PHP and Apache installs where you might flip a lot of switches.
configure --prefix=/usr/local/mysql
Make, make install. Should go OK now.
So next step, importing the database into the new version. First off, get the new executable running. then go to the /bin directory where you created your SQL dump and import it:
./mysql -u(user) -p(pass) < dump.sql
Here's where it got pretty squirrely for me, probably won't for you: I use triggers. In version 5.0.34 the mysqldump routine put the instructions for triggers
before the instructions for the tables which causes a big problem, because triggers validate before they save. This was fixed in 5.0.67. But my version bombs instantly. There are two solutions I found: First, if it's a small install, you can VI the dump file and move all trigger statements AFTER the data statements. If it's 30Gs or so like mine, that's not really a viable option. There aren't any problems with procedures or functions, so you can dump the database without the triggers with:
mysqldump -u(user) -p(pass) --all-databases --single-transaction --skip-triggers > dump.sql
I didn't want to lose my triggers though. So I used phpMyIDE to load up all triggers before I killed the original database and have them handy. This proved to be a pretty good plan at the end of the day. Save this thought for later.
So now I'm back to importing the data. It gets a bunch of the way through and then I get an error about the block size being larger than max_packet_size. Hmph. So I go to my.cnf, edit max_packet_size to be not 1M but 16M. Restart the server and try again. No dice. Tried a few methods, didn't have any success. So I fired up another shell, connected to the mysql prompt and did:
set global max_allowed_packet=1000000000;
set global net_buffer_length=1000000;
.. then in the original shell ran the import again. That did it. No idea why the config file didn't, but doing it live to the daemon did the trick.
Once the data was imported I ran the upgrade successfully:
mysql_upgrade
Whew. Some success.
And again I dumped the database as above, but this time I did not need to --skip-triggers because there weren't any.
Next step: Downloaded and unpacked 5.5 in the same manner as 5.1. Went to configure ... ain't none. The new distro uses cmake not configure.
Don't have it. Go get it, do their process (./bootstrap, make make install) and go back to MySQL.
Before I did the cmake though I did my traditional thing - created a file configure.me which had:
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql .
and then ran it that way to make sure I installed to the same directories. Interesting trouble here, and I still don't know why - but the install wrote zero-length files over my executables. Then make install again did nothing because the mtime was in sync. So I moved mysql/bin to mysql/bin-OLD and moved dump.sql into the new bin directory. Then make install and the executables came over just right. Smaller file list as well.
Now here's a couple things you must do and I'm not even going to talk about what it took for me to figure this shit out:
- edit /etc/my.cnf. Change skip-locking to skip-external-locking. Some threads say you can simply comment out skip-locking, I chose the new syntax. This is a bugger. You'll get a syntax error, "unknown command skip-locking" because skip locking was removed from 5.5. It'll make weird shit happen during the upgrade and import and won't be immediately obvious how it's being called.
- Kill your existing .err file. Especially if it's huge. Many times if it's got stuff in it the new daemon will not start. You can safely just rm it, it'll get recreated.
- Dump your mysql-bin.0* files and trim your mysql-bin.index file to empty BEFORE starting the daemon
- And here's the big winner: 5.5 doesn't use the /var directory anymore it uses /data. This was a really weird one and I had a hard time tracking it down because I was tired. The symptom is that mysql won't start, terminating immediately saying it can't find mysql-bin.index. Sometimes this is accurately a permissions problem. In my case it was an old directory structure. So when one error went by I noticed that it was looking for /data rather than /var, so after trying rebuilding the databases, I moved the existing /data directory (which was essentially empty) to data-WTF and then moved /var to /data.
After those things I was able to start the daemon up correctly.
Now do the mysql_upgrade. You'll probably still get a few warnings because no-default columns have been added that mysql had to fill up. No worries.
At this point you should have MySQL up and running so you can restart Apache. Then start phpMyIDE in ANOTHER window - don't kill your original with your triggers. Unlike stored procs and funcs, phpMyIDE cannot simply store a trigger - it has to be created and there's a bit of baggage with it. So the easiest way here is to simply look at the list of all triggers in the old phpMyIDE, go to each table, click the Create button for the trigger you want, then copy the code from the old one into the new, save it and turn it on.
Yuck. Good luck and let this be a lesson to not let yourself get TOO behind in version numbers.
Reference points for other travelers:
http://dev.mysql.com/doc/refman/5.5/en/rebuilding-tables.htmlhttp://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.htmlhttp://forums.mysql.com/read.php?35,75794,162797#msg-162797http://www.ducea.com/2006/10/26/dumping-large-mysql-innodb-tables/And for those that came for SIGNALS...
http://dev.mysql.com/doc/refman/5.5/en/signal.html
