2

Let's say a server is on MySQL 5.6.1234 (yes I know that number's made up) and gets updated to 5.6.1235 (so an increment in revision / patch number). How safe would it be roll back MySQL to 5.6.1234 - would the data / index / log / table definition / etc. files still work on the prior release?

The scenario I'm envisioning is a backwards compatible change is introduced and we need to buy time to fix all the websites affected by it.

This happened before with MySQL 5.0.x on CentOS 5 due to some change which MySQL didn't even officially label as a backwards incompatibility in the changelog. In MySQL 5.0.84 they changed max_allowed_packet from being able to be set as a SESSION variable but it having no effect to being readonly and we got a nice surprise when Red Hat bumped MySQL up from 5.0.77 to 5.0.95 (see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet vs. the "oh by the way" at the end of http://dev.mysql.com/doc/relnotes/mysql/5.0/en/news-5-0-84.html). That case was easy enough to fix, but I'm trying to plan for a more difficult situation and want to know my options.

3

As long as you are dealing with two GA releases in the same series (5.6.y to 5.6.x), you should be okay, because that is not an unsupported operation.

To downgrade between General Availability (GA) status versions within the same release series, typically you just install the new binaries on top of the old ones and do not make any changes to the databases.

https://dev.mysql.com/doc/refman/5.6/en/downgrading.html

Or, better (I think) than just copying the new (old version) binaries over the old (new version), use the mechanism I use, where /usr/local/mysql is a symlink to the directory where the live version binaries are extracted, and data, inside that directory, is a symlink to the datadir.

This is a perfectly valid configuration and it allows both sets of binaries coexist on the same machine. I use the "Linux generic" binary tarball, which extracts the files into a directory named after the release and OS... as a DBA, I don't let the distro's package manager anywhere near my MySQL. Nobody decides when we upgrade or what version we run, other than me, so upgrades occur when planned and after testing.

With this setup, it's a quick operation to stop the service, move one symlink, and start the service, and I'm running the new version... and similarly quick to switch it back, if you have to. I still do it exactly this way, though I am a lot less edgy about the possibility of needing to roll back.

You also need to run mysql_upgrade to finalize the upgrade process, but you don't typically have to do it immediately, during a GA to GA release upgrade. You can do it after the server is back up, and, in fact, the server has to be running before you can do that part.

Of course, during any upgrade, it's critical to have a mysqldump of your data ready to reload in an emergency... but within the same release series, with GA versions, you should be able to pull this off... but also, you shouldn't need to. The biggest jump I recall doing in one shot was 5.1.34 to 5.1.69, with no ill effects, and of the dozens of machines I manage, I don't recall a 5.5 or 5.6 upgrade that I had any reason to roll back.

Another suggestion for before you upgrade is to sweep the entire server for cobwebs with mysqlcheck --check-upgrade --all-databases which confirms that the tables are fully structurally compatible with the current version you're running... and then mysqlcheck --optimize --all-databases. The latter is recommended (by me) not because of what it does, but because it's likely to find sketchy structural issues that could cause problems that appear to be upgrade-related, but were in fact latent corruption just waiting to surface. Optimize is time-consuming and will lock each table while running, then unlock it and move to the next, so it's potentially disruptive, but with InnoDB, it also defragments each table, so you might reclaim some disk space in the process. Skip the optimize if you can't tolerate the potentially long locks it needs while it's running.

  • Great answer!! One tip I'd add on --optimize to try to reduce disruption due to locking is that you can get the OS to cache DB files into memory first by running cat /path/to/database/* > /dev/null so the operation completes faster. Of course it'd make sense to automate this with the mysqlcheck command so it does one DB after another. I've used this trick to speed up mysqldump operations. – user101203 Jul 28 '15 at 4:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.