Upgrading to MySQL version 8.0 is a hot topic since version 5.7 is approaching 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.
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.
Ensure your databases are performing their best — today and tomorrow — with proactive database optimization and query tuning. Book a database assessment
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 expected, is with system tables. You cannot dump 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 import errors like these:
|
1 2 3 4 5 6 7 |
$ mysql --force < 8.0.33.dump ERROR 1273 (HY000) at line 24: Unknown collation: 'utf8mb4_0900_ai_ci' ERROR 1726 (HY000) at line 35: Storage engine 'InnoDB' does not support system tables. [mysql.columns_priv] ERROR 1146 (42S02) at line 51: Table 'mysql.columns_priv' doesn't exist ERROR 1812 (HY000) at line 63: InnoDB: A general tablespace named `mysql` cannot be found. ERROR 1146 (42S02) at line 119: Table 'mysql.db' doesn't exist ERROR 1146 (42S02) at line 1021: Table 'mysql.user' doesn't exist |
The target 5.7 instance then becomes broken and unable to operate because crucial tables are missing. Even the usual upgrade tool won’t fix it:
|
1 2 3 4 |
mysql_upgrade -uroot -p Checking if update is needed. Checking server version. Error occurred: Query against mysql.user table failed when checking the mysql.session. |
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 basic commands have changed.
For example, you can’t simply dump and restore user definitions:
|
1 2 3 4 5 6 7 |
mysql 80 > show create user msandbox_ro@localhost\G *************************** 1. row *************************** CREATE USER for msandbox_ro@localhost: CREATE USER `msandbox_ro`@`localhost` IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 1 row in set (0.00 sec) mysql 57 > CREATE USER `msandbox_ro`@`localhost` IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT' at line 1 |
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 that 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, util.dumpInstance() from MySQL Shell, by default, does not back up 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:
|
1 2 3 4 5 6 |
MySQL localhost:5751 ssl JS > util.loadDump("/data/backup/mysql80_1/", {threads: 4}) Loading DDL and Data from '/data/backup/mysql80_1/' using 4 threads. Opening dump... Target is MySQL 5.7.42. Dump was produced from MySQL 8.0.33 ERROR: Destination MySQL version is older than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. Enable the 'ignoreVersion' option to load anyway. Util.loadDump: MySQL version mismatch (MYSQLSH 53011) |
Forcing is possible, though:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
MySQL localhost:5751 ssl JS > util.loadDump("/data/backup/mysql80_1/", {threads: 4, ignoreVersion: true}) Loading DDL and Data from '/data/backup/mysql80_1/' using 4 threads. Opening dump... Target is MySQL 5.7.42. Dump was produced from MySQL 8.0.33 WARNING: Destination MySQL version is older than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. The 'ignoreVersion' option is enabled, so loading anyway. Scanning metadata - done Checking for pre-existing objects... Executing common preamble SQL Executing DDL - done Executing view DDL - done Starting data load 4 thds loading / 100% (108.61 MB / 108.61 MB), 16.27 MB/s, 124 / 191 tables and partitions done Recreating indexes - done Executing common postamble SQL 191 chunks (800.14K rows, 108.61 MB) for 155 tables in 6 schemas were loaded in 9 sec (avg throughput 16.08 MB/s) 0 warnings were reported during the load. |
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 the default.
|
1 2 3 4 5 6 7 |
mysql 80 > select * from information_schema.COLLATIONS where CHARACTER_SET_NAME='utf8mb4' AND IS_DEFAULT='Yes'; +--------------------+--------------------+-----+------------+-------------+---------+---------------+ | COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE | +--------------------+--------------------+-----+------------+-------------+---------+---------------+ | utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD | +--------------------+--------------------+-----+------------+-------------+---------+---------------+ 1 row in set (0.08 sec) |
Versus 5.7:
|
1 2 3 4 5 6 7 8 9 10 |
mysql 57 > select * from information_schema.COLLATIONS where CHARACTER_SET_NAME='utf8mb4' AND IS_DEFAULT='Yes'; +--------------------+--------------------+----+------------+-------------+---------+ | COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | +--------------------+--------------------+----+------------+-------------+---------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | +--------------------+--------------------+----+------------+-------------+---------+ 1 row in set (0.00 sec) mysql 57 > select * from information_schema.COLLATIONS where id=255; Empty set (0.00 sec) |
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 using MySQL 8.0 drivers or C libraries:
https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html
A simple sysbench against the 8.0 source will break the 5.7 replica with something like:
|
1 2 3 4 |
Relay_Master_Log_File: mysql-bin.000023 Exec_Master_Log_Pos: 761428 Last_SQL_Errno: 22 Last_SQL_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/data/opt/mysql/5.7.42/share/charsets/Index.xml' file' on query. Default database: 'test'. Query: 'BEGIN' |
The binary log contains:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# at 761428 #230624 22:33:55 server id 5748 end_log_pos 761514 CRC32 0x2cd0da71 Anonymous_GTID last_committed=1000 sequence_number=1001 rbr_only=yes original_committed_timestamp=1687638835657515 immediate_commit_timestamp=1687638835662742 transaction_length=401 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1687638835657515 (2023-06-24 22:33:55.657515 CEST) # immediate_commit_timestamp=1687638835662742 (2023-06-24 22:33:55.662742 CEST) /*!80001 SET @@session.original_commit_timestamp=1687638835657515*//*!*/; /*!80014 SET @@session.original_server_version=80033*//*!*/; /*!80014 SET @@session.immediate_server_version=80033*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 761514 #230624 22:33:55 server id 5748 end_log_pos 761584 CRC32 0x18c98746 Query thread_id=27 exec_time=0 error_code=0 SET TIMESTAMP=1687638835/*!*/; /*!C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=192/*!*/; BEGIN /*!*/; |
Unfortunately, I found no way to force applications to use utf8 or a 5.7-compatible collation like utf8mb4_general_ci other than changing it inside the application itself.
That means replication may work only if your apps use MySQL 5.7 connectors or if you control the connection settings.
So, if you are lucky enough to deal with the compatibility issue, you may want to upgrade one replica first, then keep another 5.7 replica below it as a downgrade path.
While these screenshots were made from Orchestrator, which makes it easy to change replication topologies, I still had to move the 5.7 replica under the 8.0 source manually, because this is unsupported:
|
1 2 |
# orchestrator-client -c relocate -i 127.0.0.1:5749 -d 127.0.0.1:5750 2023-06-22 20:31:10 ERROR przemek-dbg:5749 cannot replicate from przemek-dbg:5750. Reason: instance przemek-dbg:5749 has version 5.7.42-log, which is lower than 8.0.33 on przemek-dbg:5750 |
As long as it works for you, this topology allows you to run applications on MySQL 8.0 while still keeping a 5.7 recovery replica. In case of emergency downgrade, downtime can be minimal.
This combines a straightforward backup restore step with another unsupported action: applying binlogs produced by 8.0 to 5.7. The same incompatibility issues as replication apply.
|
1 2 3 4 5 6 7 8 9 |
$ mysqlbinlog binlog.000005 | mysql -v -------------- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/ -------------- (...) -------------- SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255 -------------- ERROR 1115 (42000) at line 33: Unknown character set: '255' |
To work around this, you can convert charset/collation values on the fly:
|
1 2 3 4 5 6 7 8 9 |
mysql 80 > set @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255; Query OK, 0 rows affected (0.00 sec) mysql 80 > select @@session.character_set_client,@@session.collation_connection,@@session.collation_server\G *************************** 1. row *************************** @@session.character_set_client: utf8mb4 @@session.collation_connection: utf8mb4_0900_ai_ci @@session.collation_server: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
On 5.7, use compatible values:
|
1 2 3 4 5 6 7 8 9 |
mysql 57 > set @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45; Query OK, 0 rows affected (0.00 sec) mysql 57 > select @@session.character_set_client,@@session.collation_connection,@@session.collation_server\G *************************** 1. row *************************** @@session.character_set_client: utf8mb4 @@session.collation_connection: utf8mb4_general_ci @@session.collation_server: utf8mb4_general_ci 1 row in set (0.00 sec) |
Then rewrite the binlog stream before applying:
|
1 |
$ mysqlbinlog binlog.000005 | sed 's/SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/set @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/' | mysql |
This worked for a simple sysbench OLTP workload, but there is no guarantee it will work for yours. Charset is only one of several incompatibility issues.
To summarize, I highly recommend considering the following points for a MySQL 8.0 upgrade:
Useful reading:
And do not hesitate to contact Percona Experts for help preparing and performing the upgrade.
MySQL Performance Tuning is an essential guide covering the critical aspects of MySQL performance optimization.
Download and unlock the full potential of your MySQL database today.
