Upgrading to 5.x seems like a good idea, as the additional validation work will be the same as would have to be done anyway - so we could effectively upgrade our MySQL version "for free", i.e. no additional QA work would be required than would be needed anyway.
Upgrading at any other time would involve a repetition of the validation exercise, which is not trivial as we must test at least:
- All features of the complex application which use these databases still work without errors
- The behaviour of the new version is consistent with the old one (e.g. results returned)
- Performance needs to be measured to ensure that performance regressions haven't happened (or are acceptable)
- Soak / stress testing needs to be done to ensure that the new version can perform under sustained high load.
Migration and rollback
My initial plan was to leave the MyISAM table files exactly as they were - after all, 5.0.x can read them fine (apparently) - so that if we need to rollback, we simply move back to the old version and everything works as before (including data created while we were on the new version).
However, this looks tricky. Load testing confirms that 5.0.x has frequent crashes when dealing with "old" tables (i.e. those which have not been upgraded). The MySQL manual insists that mysql_upgrade is run to upgrade all existing MyISAM tables.
This is fine if your database is small and noncritical, but ours are large and important. So what to do?
It is possible to upgrade tables using CHECK TABLE FOR UPGRADE and REPAIR TABLE. However, NO ROLLBACK METHOD exists. This is a problem.
I've estimated (using performance data from a production-spec test system) that in production, migration can be performed in 2-3 hours. This is an acceptable level of planned downtime. The only way of rolling BACK the table versions is to mysqldump the database and reload it. I estimate that this could take 16 hours, which is not acceptable.
It is not acceptable to apply a change without a working (i.e. tested) rollback procedure. Neither is it particularly acceptable to have 16 hours of downtime to perform one.
This makes the upgrade tricky. We're working on it.
4 comments:
Why not buy a couple of cheap terabyte hdd and do a file level backup of the myisam data before the migration?
Mark,
I did the same upgrade about 2 months ago. We had a 300GB InnoDB database. Rollback with a dump and load was not an option.
What we did was setup a 5.0 slave and seeded with a mysqldump of the master and did an initial load into the 5.0 slave. (The only issues we ran into were utf8 data that had been encoded in latin1 tables on the 4.1 server. They were being double decoded in 5.0. I had to do a special import of those tables.)
During the actual upgrade it was a simple failover to the 5.0 as the master. The 4.1 former master was frozen in time as a rollback (You can't replicate from 5.0 to 4.1). As a final confirmation that our data was good, I wrote a couple of store procedures that performed a aggregated view of the data between the servers.
fat'n'happy: I don't understand how doing a backup of the files would help in any way.
We don't want to roll back to the data which existed before the upgrade, we want to be able to roll back to the old version of the software, preserving all data including stuff written following the deployment
Another thing I did for rollback, was setup another 5.0 slave, with a bin log setup to start at the moment we upgraded to 5.0. If we had to fail back, which we didn't, I'd replay that log against a 4.1 server, adjusting for 5.0-only-isms.
Post a Comment