In the many years we have used MySQL, we got accustomed to the fact that upgrades from MySQL 5.7.11 to 5.7.12 was a minor event. This meant that if something was going wrong, we could roll back the binaries and be happy again.
From MySQL 8, this is no longer true. Any upgrade, even minor, is seen as irreversible. (This is valid for Percona Server for MySQL as well.)
Say we have MySQL 8.0.17 and we upgrade to 8.0.18. In our MySQL log at the start, we will have this:
[System] [MY-010116] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld (mysqld 8.0.18) starting as process 13242
[System] [MY-013381] [Server] Server upgrade from '80017' to '80018' started.
[System] [MY-013381] [Server] Server upgrade from '80017' to '80018' completed.
[System] [MY-010931] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld: ready for connections. Version: '8.0.18'
All good we made it!
But then we realized that the just-upgraded instance has something that doesn’t work as expected. At this point, we may want to roll back our installation, and put back the 8.0.17 binaries.
But once we have done that and we start MySQL again, our instance will fail to start and our mysql log shows:
[Server] /opt/mysql_templates/mysql-8P/bin/mysqld (mysqld 8.0.17) starting as process 26663
[ERROR] [MY-013171] [InnoDB] Cannot boot server version 80017 on data directory built by version 80018. Downgrade is not supported
[System] [MY-010910] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld: Shutdown complete (mysqld 8.0.17) MySQL Community Server - GPL.
We cannot roll back
This is clearly stated in the MySQL documentation. You may not like it, but that is how it is now.
Here are some things to think about. Upgrading a minor MySQL version in the past was a trivial activity, and very often automation was doing that silently. That was not the best practice but it was the reality. At the same time, testing was very often superficial, if done at all.
That attitude must change.
- You CANNOT do an upgrade to a MINOR VERSION without:
- Full Backup/restore testing (backup must be a logical one, like: mysqldump or mydumper or any other tool that allow you to export the data)
- Severe functional/application testing IN A STAGING environment
- You should disable ANY automation that performs silent/untested upgrade in production
Also keep in mind that IF you take a backup, if you have terabytes or even maybe just a GB of data, the rollback will not be immediate at all.
Given that, you should:
- Plan carefully, use a proper platform for the tests (like a full populated staging).
- Use as much real load as possible from your application.
- Upgrade from the last server of the slave chain up.
- Do not rush trying to catch/implement the latest shiny feature.
One last but NOT SUPPORTED approach (see documentation), is to rely on replication as described in Replicating from MySQL 8.0 to MySQL 5.7. But remember, this is done at your own risk, given it is not fully supported by MySQL/Oracle.
These are the minimal steps needed to perform minor version upgrades and to be safe. Great MySQL to everybody!
Bug #95216 – Opened some time ago by our Ceri Williams.