Long, long time ago in the galaxy not so far away I installed mySql 5.05 on my server. After some time I decided to upgrade to the current version, MySql 5.6. It turned out to be quite an ordeal.
First of all, there is no easy upgrade path between different major versions of MySQL. Official upgrade instructions are verbose and full of HTML pages pointing to each other. They talk a lot about what to do before and after the upgrade, but somehow avoid the subject of the actual upgrade steps.
This is not a coincidence. To “upgrade” your database, you need to convert your old database to an SQL script using
mysqldump, install new version of MySQL side by side with the old one, and run the script on the new instance. The installer will basically ignore the presence of the old MySQL version.
Furthermore, you cannot run SQL script generated by the 5.05 database on your 5.6 instance. If you try, 5.6 instance fails to start after shutdown, with no error message in the log. This proved to be fatal. Somehow, 5.05 instance also became corrupted, but thankfully I had a backup.
So, I had to do a step-by-step “upgrade”:
1. Do database dump using MySQL 5.0.5.
2. Install MySQL 5.1, run 5.05 dump on it, check that it works.
3. Do database dump using MySQL 5.1.
4. Install MySql 5.5, run the database dump from MySQL 5.1, check that it works.
5. Do database dump using MySQL 5.5.
6. Install MySQL 5.6. At this point that installer notices the 5.1 and 5.6 instances (but not 5.05), and even has the “upgrade” button, but when you press this button it tells you that instance cannot be upgraded.
7. Run the database dump from MySQL 5.5, check that it works.
And, voila – I am now a happy owner of 4 (four) virtually identical MySQL instances. Of course, only one of them can be active at a time, since they all listen on the same port. I will let the backup run tonight, and will probably delete all intermediate instances tomorrow, but wow, what a mess! Granted, MySQL 5.05 was long time ago (8 years?), but somehow SQL Server 2014 does not have a problem upgrading SQL Server 2005 (prooflink).
You always get what you’re paying for (or, if you prefer a Russian version, if they gave you a horse for free, don’t check its teeth”).