Upgrade MySQL to 8.0? Yes, but Avoid Disaster!

Upgrading to MySQL version 8.0 is a hot topic since version 5.7 is approaching the official end of life very soon. MySQL 5.7 EOL is set for the end of October 2023.

If you feel unprepared for the upgrade, consider post-EOL support from Percona. But it would be the worst if you proceeded with the upgrade in haste.

• Long database service downtime
• Some queries become slow
• Applications stop working due to incompatibilities
• Data loss

This is what can happen if MySQL’s major upgrade to 8.0 turns out not as you hoped for. Yes, it can be that serious, so do consider the major upgrade as an important project, not just a routine maintenance task! From my experience as a Support Engineer, I think the most frequent problem is query performance after an upgrade. This single problem can potentially render your site down if the execution plan changes for worse for a frequent enough query.

Although there are tools and methods to prepare and test the upgrade before going live, often, not all problems can be anticipated. For example, while it is possible to test read workload, it is pretty difficult to test things write-related. Often QA/staging environments do not resemble production 100%, especially regarding scale, and surprises are very possible.

Therefore, you may find yourself in a situation where the only quick rescue after an unsuccessful MySQL upgrade is to downgrade back to the previous version.

If it becomes clear that you have to revert the upgrade immediately after it’s done, the solution should be simple – reinstall the 5.7 version packages and restore the full backup taken right before the upgrade (in fact, it’s the only officially supported downgrade method!).

It becomes much more difficult if the revert decision has to be taken later when new data has already been added. As MySQL does not support downgrades from 8.0, you are left with more challenging and not officially supported alternatives if you can’t just discard all the new writes that happened under version 8.0.

Let’s take a closer look at the following possible downgrade options:

• Logical data dump from 8.0 and restore to 5.7
• Utilize 5.7 replica
• Restore the last pre-upgrade backup and apply missing data from binlogs created on 8.0

A logical dump/restore downgrade

While logical downgrade was officially supported from MySQL 5.7 to 5.6:
https://dev.mysql.com/doc/refman/5.7/en/downgrade-paths.html

It is no longer the case for 8.0 to 5.7:
https://dev.mysql.com/doc/refman/8.0/en/downgrading.html

The entire “Downgrading MySQL” section of the documentation was basically reduced to a statement that it is not supported; therefore, you can try it at your own risk.

Now, the first issue with the logical dump restore approach, as somewhat totally expected though, is with system tables. You cannot load a dump of the MySQL system database from MySQL 8.0 and load it back to MySQL 5.7. It will break it completely!

So, if you follow a similar procedure as suggested for the 5.7->5.6 downgrade:
https://dev.mysql.com/doc/refman/5.7/en/downgrade-binary-package.html#downgrade-procedure-logical

You will see tons of import errors like these:

And the target 5.7 instance gets broken, no longer being capable of operating as missing crucial tables. Even the usual upgrade won’t fix it:

Therefore, you must dump user tables only while getting the system tables from the last backup before the upgrade. Any changes in system tables made since the 8.0 upgrade may be a problem, as many of the basic commands have changed. For example, you can’t dump and restore user definition:

That means if you want to be prepared for the downgrade path, you need to keep track of every new system property change, like users, passwords, events, stored procedures, etc, and save notes accordingly, with syntax working on 5.7. If you find it a problem, you may vote for https://jira.percona.com/browse/PT-1698.

Worth mentioning is that the logical dump may be done using a much faster and more robust tool, like MySQL Shell or mydumper. For example, the util.dumpInstance() from MySQL Shell, by default, does not backup the system tables for a full backup. Also, util.loadDump() allows you to exclude additional tables if needed. It will warn you about the unsupported action first:

Forcing is possible, though:

Using a 5.7 replica as a backup downgrade path

Having replicas seems like a great opportunity to leave one on version 5.7 for some time after the upgrade to MySQL 8.0. However, again, this is not officially supported:
https://dev.mysql.com/doc/refman/8.0/en/replication-compatibility.html

And although replication from an 8.0 source to a 5.7 replica can work with some effort:
https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/

There is one major problem with it, even if you avoid new features, MySQL 8.0 has more charset collations than 5.7, and one of them is made default!

While using utf8 as the default character set for the [client] section in the configuration file works for regular MySQL clients, it won’t help for other applications that are using MySQL driver or C library from MySQL 8.0:

https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html

A simple sysbench against the 8.0 version source will break the 5.7 replica with something like this:

The binary log with the corresponding position has the following SET line:

Unfortunately, I found no way to convince the applications to use utf8 or collation existing in 5.7 (like utf8mb4_general_ci) other than change it from within the application itself.

That means replication may work only if your apps use a driver/connector library from MySQL 5.7 or when you have control over it to include SET NAMES accordingly.

So, if you are lucky enough to deal with the compatibility issue, you may want to upgrade one replica first, as in this example diagram:

Then, place another 5.7 replica (or create a new one) under the upgraded one:

And finally, once the 8.0 replica proves to work with your apps, as well as the replication does not break, upgrade the source/primary to 8.0 as well:

Note that while these screenshots were made from Orchestrator, which is a tool that makes it extremely easy to change the replication topologies, in this case, I had to move the 5.7 replica under the 8.0 source manually, as, again, this is not supported action:

As long as it works for you, the topology above allows you to run applications with both the source and replica using MySQL 8.0 while having a recovery/backup replica running with MySQL 5.7. In case of an emergency need for database downgrade, it would involve minimal downtime.

Downgrade by restoring the 5.7 backup and applying new binlogs (PITR)

This combines a straightforward backup restore step and another not officially supported one – applying binary logs produced by version 8.0 to version 5.7. Potential problems are very similar to replication – some information written to the binary log may not be acceptable in 5.7 instances:

To allow applying these binlog events made with charset 255 and collation 255 for their sessions, we can try to convert them to the closest similar ones that exist in MySQL 5.7.

In MySQL 8.0, the culprit SET commands will do:

For MySQL 5.7, we can switch to this instead:

Therefore, let’s modify the culprit lines in the binary log on the fly before applying:

This worked well for me for a simple sysbench OLTP workload, but there is no guarantee that it will work for yours. There are more incompatibility issues than just the charset.

Upgrade advice

To summarize this post, I highly recommend considering the below points for the MySQL upgrade to 8.0:

Do all the recommended pre-upgrade checks:

Read the upgrade-related posts and documentation:

Test as much as possible – make sure there will be no need for reverting the upgrade:

• Enable binary logs if not enabled
• Make a verified full backup before upgrading
• Test the downgrade options as discussed above

And do not hesitate to contact Percona Experts for help preparing and performing the upgrade!

A better way to move to MySQL 8.0

Subscribe

Connect with

Login

Label

0 Comments