Step 0 - Is the upgrade for you?
Do you use InnoDB? This is where most of the improvements have been made, and it could be the main reason why you may want to be an early adopter. For a complete reference of the improvements check the New Features of InnoDB 1.1 section of the official MySQL Documentation, Sheeri Cabral's and Ronald Bradford's blogs.Step 1 - Read the manual
Ok, I'm the first one to admit that I usually skip this step, but after a couple of false starts I decided that in this case it might be a good idea to go through the documentation, in particular Upgrading from MySQL 5.1 to 5.5. The Incompatible change entries are quite important in this release, and as you will find out, make the upgrade process a little more complex than usual. You mileage may vary, but make sure you're review them carefully.Step 2 - Take a backup
I usually trust (since we test them) the overnight backups, in this case, I'm a little more paranoid since you may not be able to do a straight upgrade and will need to remove the old version before you install the new one(see next step). Taking a new backup or double checking your last backup is a very good idea. Make sure you include your current configuration file(s) with the backup.Step 3 - The upgrade process
If you have been using the InnoDB plugin, you need to disable the plugin options in your configuration file before you start the upgrade. The details are in the Upgrading from MySQL page I quoted in Step 1, it's the first Incompatible change.Although it wasn't listed in the documentation, when tried to run an RPM upgrade (rpm -Uvh MySQL-server-5.5.7_rc-1.rhel5.x86_64.rpm) it failed with a message telling me that I needed to remove the previous version and run a regular install (rpm -i) instead. I usually get a little bit anxious with this kind of recommendations, but fortunately both steps ran smoothly.
Keep in mind that the first time you'll have to start the service with the --skip-grant option to run mysql_upgrade. If you're upgrading a production server, it highly recommended you add the --skip-networking otherwise you may get connections from your application(s), which you it's not a good idea until at this point. This is the 3rd Incompatible change. Once you're done, shutdown the MySQL service (ie: using mysqladmin shutdown, no need for username/password in this case) and restart it as you normally would (ie: sudo /etc/init.d/mysql start)
Done
At this point your installation should be ready for testing. As I mentioned before, make sure you double check the Incompatible change notes to make sure your database configuration and/or application doesn't need any further adjustments. I would also recommend following closely the posts in Planet MySQL for reviews and articles from the community.
So you didn't mention TESTING your application in a non-production environment, AT ALL?
ReplyDeleteThis is, frankly, the single biggest piece of working involved in a migration. Actually putting a new version of MySQL is a trivial task.
Testing that your sprawling legacy application will work 100% compatibly with the new version is not.
I should imagine that a couple of developer-months work will see it done fairly adequately for medium-sized applications, but it really depends on a) How much unit test / auto test coverage you have and b) How much risk you are willing to accept.
Testing is the big one. Nothing else is at all significant.
"Take a backup" - well, yes, but really, if any problems occur, they will be because of incompatible behaviour in the application. This may cause the application to subsequently write bad data, and you can't then restore your backup, because good changes will then be gone too.
Mark,
ReplyDeleteYou are right, but in my experience, it has happened before that you shut down MySQL (or any other software) and when you start the new version, it won't or you will find messages like 'table table_name is corrupted' and so on. With significant changes like the ones in MySQL 5.5 this is a certain risk. It took us a few iterations to come up with a 'clean' upgrade recipe.
In regards of the applications, as DBAs we don't always have full visibility and control over what's going on, so we collaborate with the development teams with their testing. As soon as I find any issues in that area, be assured I'll post a new article about it.
Thanks for your feedback.
Daniel Nichter and I wrote mk-upgrade (in Maatkit) for the purpose of testing application compatibility during upgrades. Those who used it found a lot of bugs in 5.1. I think the 5.5 release is great, but it will certainly have bugs too, and finding them before upgrading is a good idea.
ReplyDelete