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:
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 |
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:
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 of the basic commands have changed. For example, you can’t dump and restore user definition:
1 2 3 4 | mysql 80 > show create user msandbox_ro@localhostG *************************** 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) |
1 2 | 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 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:
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. |
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!
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) |
Vs.
1 2 3 4 5 6 7 8 9 | 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 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:
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 with the corresponding position has the following SET line:
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 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:
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, 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:
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 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:
1 2 3 4 5 6 7 8 | 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_serverG *************************** 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) |
For MySQL 5.7, we can switch to this instead:
1 2 3 4 5 6 7 8 | 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_serverG *************************** 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) |
Therefore, let’s modify the culprit lines in the binary log on the fly before applying:
1 2 | $ 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 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:
- https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html
- Percona Utilities That Make Major MySQL Version Upgrades Easier
- Upgrading to MySQL 8: Tools That Can Help
Read the upgrade-related posts and documentation:
- https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
- MySQL 8 Minor Version Upgrades Are ONE-WAY Only
- Upgrading to MySQL 8: Embrace the Challenge
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!
On MySQL 8.0 Server side, we could turn on skip-character-set-client-handshake=1, so that the character set 0900 will be ignored and use “general” (server side setting) instead. there will be no 0900 in bin log event as well.
Good point; thank you for mentioning it! The downside is the server will ignore all requests on how clients want to interact with the server, possibly leading to problems.