Tips for Upgrading to from MySQL 5.7 to MySQL 8

Krzysztof Ksiazek

MySQL 8.0 has been with us already for quite some time and many MySQL users have already upgraded to this version. For those who are still using older MySQL versions, we would like to present this blog where we will share some tips and information that help in the upgrade process for MySQL 8.0.

Mind Your Version

Software versions are quite important in the upgrade process. For starters, only one major version difference is supported. You have to be running MySQL 5.7 before you can upgrade to MySQL 8.0. This is quite important to keep in mind given that MySQL 5.6 is approaching its End-of-Life and it won’t be supported anymore. For all of you who use MySQL 5.6 you have to make sure you upgrade it to MySQL 5.7 first and then, eventually, to MySQL 8.0.

What’s strongly recommended is that you upgrade to the latest version available for MySQL 5.7. At the time of writing this blog it was 5.7.31 but this will eventually change, you can always look it up at MySQL website.

Please also note that upgrades from non-GA (and to non-GA) versions are not supported. Not that it makes any sense to run non-GA versions in production but we wanted to make this one clear as well.

It’s a One Way Ticket

Whenever you decide to perform the upgrade, please be aware that, once the upgrade is complete, there is no coming back. The changes are not compatible and you just simply cannot use the data directory from MySQL 8.0 on MySQL 5.7. Make sure that you take a backup of your MySQL 5.7 data directly before the upgrade - you would be able to restore it on MySQL 5.7 instance should you need to revert the change. Please also keep in mind, as it may come as a surprise, that upgrade from MySQL 8.0.x to MySQL 8.0.x+1 may also be not compatible and, even though it is a minor version upgrade, you should not expect that downgrade would be possible. This is the result of Oracle’s deployment cycle - instead of doing feature-freeze for the latest GA branch, as it was the case with previous versions, new features, sometimes incompatible ones, are pushed as new releases of 8.0 branch.

In-Place Upgrade is a Go

In the past it was not always possible to perform an in-place upgrade of MySQL. In some cases you were forced to dump the data into SQL format and then load it back up to the new version. Luckily, MySQL 8.0 is more admin-friendly and in-place upgrade is supported. All you need to do is to run apt upgrade or yum update and you are all set. The upgrade is even more convenient - in the past one had to keep in mind to run mysql_upgrade to ensure all system tables are properly upgraded to the format required by the new version of MySQL. In MySQL 8.0, starting from MySQL 8.0.16, this is no longer needed - all you have to do is to start MySQL process, mysqld, and, by default, the upgrade will be performed over the data dictionary and other system schemas whenever it’s determined to be required. It is possible to change this behavior by passing different parameters to --upgrade server option but in the majority of the cases you would like to benefit from this improvement.

Am I Safe to Upgrade?

Of course, there are prerequisites for the safe upgrade. Let’s take a look at some methods that should help you to ensure you can safely upgrade to MySQL 8.0.

Sanity Checks

Before you attempt anything, you should double-check that your existing MySQL 5.7 setup ticks all the boxes on the sanity checklist before upgrading to MySQL 8.0. MySQL documentation presents an extensive list of things to test. It doesn’t make sense to go over the list here as it’s covered in the MySQL documentation, but here are a couple of points you may want to keep in mind.

First, partitioning is now supported only in engines that implement it on their end, which are NDB and InnoDB only. Please make sure that all the partitioned tables use one of those storage engines or that you remove the partitioning before the upgrade.

You May Want to Run

mysqlcheck -u root -p --all-databases --check-upgrade

to double-check that tables are in the proper format.

There are also other checks that you should perform - almost every new MySQL version comes with an updated list of reserved words and you should check that you don’t use them in your database. You need to check foreign key constraint names, they cannot be longer than 64 characters. Some options for sql_mode have been removed thus you should make sure you do not use them. As we mentioned, there’s an extensive list of things to test.

MySQL Shell to the Rescue

Testing all of those conditions is quite time-consuming therefore Oracle created an option in the MySQL Shell that is intended to run a series of tests to verify if your existing installation is safe to upgrade to MySQL 8.0. For starters, if you do not have MySQL Shell installed, you should do that. You can find downloads on Oracle’s website. Once you set it up, you can connect to your MySQL 5.7 and run the test. Let’s see how it can look like:

[email protected]:~# mysqlsh

MySQL Shell 8.0.21



Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

Other names may be trademarks of their respective owners.



Type '\help' or '\?' for help; '\quit' to exit.

 MySQL  JS > \c [email protected]

Creating a session to '[email protected]'

Please provide the password for '[email protected]': ****

Save password for '[email protected]'? [Y]es/[N]o/Ne[v]er (default No):

Fetching schema names for autocompletion... Press ^C to stop.

Your MySQL connection id is 71 (X protocol)

Server version: 5.7.31-log MySQL Community Server (GPL)

No default schema selected; type \use <schema> to set one.

We connected to the MySQL instance on the localhost using MySQL Shell. Now we can run the check. We’ll pass the path to the configuration file for more extensive tests:

MySQL  localhost:33060+ ssl  JS > util.checkForServerUpgrade({"configPath":"/etc/mysql/my.cnf"})

Then we have a long output.

The MySQL server at localhost:33060, version 5.7.31-log - MySQL Community

Server (GPL), will now be checked for compatibility issues for upgrade to MySQL

8.0.21...



1) Usage of old temporal type

  No issues found



2) Usage of db objects with names conflicting with new reserved keywords

  No issues found



3) Usage of utf8mb3 charset

  No issues found



4) Table names in the mysql schema conflicting with new tables in 8.0

  No issues found



5) Partitioned tables using engines with non native partitioning

  No issues found



6) Foreign key constraint names longer than 64 characters

  No issues found



7) Usage of obsolete MAXDB sql_mode flag

  No issues found



8) Usage of obsolete sql_mode flags

  No issues found



9) ENUM/SET column definitions containing elements longer than 255 characters

  No issues found



10) Usage of partitioned tables in shared tablespaces

  No issues found



11) Circular directory references in tablespace data file paths

  No issues found



12) Usage of removed functions

  No issues found



13) Usage of removed GROUP BY ASC/DESC syntax

  No issues found



14) Removed system variables for error logging to the system log configuration

  No issues found



15) Removed system variables

  Error: Following system variables that were detected as being used will be

    removed. Please update your system to not rely on them before the upgrade.

  More information:

    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed



  log_warnings - is set and will be removed, consider using log_error_verbosity

    instead

  query_cache_size - is set and will be removed

  query_cache_type - is set and will be removed



16) System variables with new default values

  Warning: Following system variables that are not defined in your

    configuration file will have new default values. Please review if you rely on

    their current values and if so define them before performing upgrade.

  More information:

    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/



  back_log - default value will change

  character_set_server - default value will change from latin1 to utf8mb4

  collation_server - default value will change from latin1_swedish_ci to

    utf8mb4_0900_ai_ci

  event_scheduler - default value will change from OFF to ON

  explicit_defaults_for_timestamp - default value will change from OFF to ON

  innodb_flush_neighbors - default value will change from 1 (enable) to 0

    (disable)

  innodb_max_dirty_pages_pct - default value will change from 75 (%)  90 (%)

  innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10

    (%)

  innodb_undo_log_truncate - default value will change from OFF to ON

  innodb_undo_tablespaces - default value will change from 0 to 2

  log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)

  max_error_count - default value will change from 64 to 1024

  optimizer_trace_max_mem_size - default value will change from 16KB to 1MB

  performance_schema_consumer_events_transactions_current - default value will

    change from OFF to ON

  performance_schema_consumer_events_transactions_history - default value will

    change from OFF to ON

  slave_rows_search_algorithms - default value will change from 'INDEX_SCAN,

    TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'

  transaction_write_set_extraction - default value will change from OFF to

    XXHASH64



17) Zero Date, Datetime, and Timestamp values

  No issues found



18) Schema inconsistencies resulting from file removal or corruption

  No issues found



19) Tables recognized by InnoDB that belong to a different engine

  No issues found



20) Issues reported by 'check table x for upgrade' command

  No issues found



21) New default authentication plugin considerations

  Warning: The new default authentication plugin 'caching_sha2_password' offers

    more secure password hashing than previously used 'mysql_native_password'

    (and consequent improved client connection authentication). However, it also

    has compatibility implications that may affect existing MySQL installations.

    If your MySQL installation must serve pre-8.0 clients and you encounter

    compatibility issues after upgrading, the simplest way to address those

    issues is to reconfigure the server to revert to the previous default

    authentication plugin (mysql_native_password). For example, use these lines

    in the server option file:



    [mysqld]

    default_authentication_plugin=mysql_native_password



    However, the setting should be viewed as temporary, not as a long term or

    permanent solution, because it causes new accounts created with the setting

    in effect to forego the improved authentication security.

    If you are using replication please take time to understand how the

    authentication plugin changes may impact you.

  More information:

    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues

    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication



Errors:   3

Warnings: 18

Notices:  0



3 errors were found. Please correct these issues before upgrading to avoid compatibility issues.

As you can see, 21 tests in total have been performed, the check has found 3 errors related to the configuration options that will not exist in MySQL 8.0.21. The tests are quite detailed. Among other things you will learn about changes in the default values for variables that you do not have configured in your MySQL configuration (so, those settings will change once you install MySQL 8.0).

Rolling Back a Failed Upgrade

As we mentioned before, you cannot downgrade from MySQL 8.0 once upgrade is complete. Luckily, it doesn’t mean you cannot rollback the upgrade if it fails in the middle. Actually, it happens semi-automatically should one of the issues we discussed in the previous section is detected. The only manual action that is required would be to remove redo logs and start MySQL 5.7 to address the problems detected during the upgrade. Then you should perform a slow shutdown (innodb_fast_shutdown=0) to ensure everything is written to the tablespaces and then you are all good to attempt the upgrade once more.

Final Tips

There are two, quite important changes in the default behavior that comes with MySQL 8.0 we would like to highlight.

Caching_sha2_password as the default

Please make sure that you double-check if your applications and proxies will work properly with caching_sha2_password authentication plugin as it becomes the default one in MySQL 8.0. Older applications might be impacted and not able to connect to the database. Of course, you can change this to whatever authentication plugin you want (like mysql_native_password for example, as it was the default in previous MySQL versions) so it is not a blocker by any means. It’s just something to remember to test before the upgrade so you won’t end up with MySQL 8.0 and apps that cannot connect to it unless you reconfigure your database to use an older authentication mechanism.

UTF8mb4 as the default charset

This shouldn’t come as a surprise given how widely changed to UTF8 was discussed in the community, but that’s the fact - MySQL 8.0 comes with UTF8mb4 charset as the default one. This has some additional impact that you should be aware of. First, your dataset’s size might increase if you will use UTF8mb4 charset. This leads to memory buffers being able to store smaller amounts of data than for data with latin1 charset. Second, the performance of MySQL is expected to be reduced. Sure, Oracle did a great job of minimizing the impact of this change, but you cannot expect that there will be no performance impact whatsoever - it will be some.

We hope this blog post will help you to go through the process of upgrading from MySQL 5.7 to MySQL 8.0. If you have your thoughts on the process, we encourage you to share them in the comments below this post.

ClusterControl
The only management system you’ll ever need to take control of your open source database infrastructure.