Upgrading MySQL 5.7 to 8.0: A Practical Guide for Magento Shops

1. Why the upgrade is not a mere formality

Oracle does not officially provide a supported downgrade path from MySQL 8.0 back to 5.7. Once the server has started with 8.0's new data dictionary structures, the instance cannot simply be restarted with an old 5.7 binary. Any upgrade plan needs to account for this fact from the start, not discover it once something goes wrong.

On top of that, MySQL 8.0 deliberately chose different defaults from 5.7 in several central areas, not by accident but as a conscious design decision. Treating an upgrade as a pure version swap without a content review risks silently changed execution plans, failing character set comparisons, or even a server that no longer starts because of a now invalid configuration option.

2. Optimizer behavior: silently different execution plans

MySQL 8.0 ships a reworked cost model for the optimizer, complemented by histogram statistics that can be generated specifically for columns without an index and represent data distribution far more precisely than the previous, purely index-based statistics. In practice that means: the same query, the same indexes, but a different execution plan, because the optimizer now computes different costs for the alternatives.

MySQL 8.0 also supports hash joins for cases where 5.7 still fell back to nested loop joins without a matching index, which can noticeably speed up certain complex reporting queries while unexpectedly slowing others down. Before any production upgrade, it is worth comparing EXPLAIN plans for the most business-critical, slowest queries between the old and new version, not just glancing at overall load.

3. The default collation trap: utf8mb4_general_ci vs. utf8mb4_0900_ai_ci

MySQL 8.0 changes the default collation for utf8mb4 from utf8mb4_general_ci to utf8mb4_0900_ai_ci, a considerably more precise, Unicode 9.0 based sort order. Magento's own schema sets the collation for its tables explicitly in db_schema.xml, so existing core tables are not directly affected, but the risk lies with newly created tables from third party modules, temporary tables, and comparisons between strings of different origin.

When two strings with different collations meet in a JOIN or WHERE clause, for example an old 5.7 table with the old default collation and a newly created 8.0 table with the new one, MySQL raises the error Illegal mix of collations. Before the upgrade it is worth systematically checking every table and column for its actual collation setting to catch inconsistencies early, rather than at a failing checkout.

-- Find every table whose collation differs from the expected default
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'magento'
  AND TABLE_COLLATION NOT LIKE 'utf8mb4_general_ci';

4. Removed query cache configuration

The query cache was already marked deprecated in MySQL 5.7.20, and in MySQL 8.0 it is removed entirely, including every related configuration option such as query_cache_size and query_cache_type. If an existing my.cnf dating back to 5.6 or early 5.7 still contains one of these options, the MySQL 8.0 server will not start at all afterward, it aborts immediately with a clear error in the error log.

For Magento shops this is usually uncritical in practice, since full page cache and object cache already run through Redis or Varnish and the query cache itself was barely used even under MySQL 5.7. It is still important to systematically check the production my.cnf against the list of removed variables in the official upgrade documentation before the upgrade, rather than finding out during a failed restart.

5. Authentication: caching_sha2_password as the new default

MySQL 8.0 sets caching_sha2_password as the default authentication plugin instead of the previous mysql_native_password. Newly created users without an explicit setting automatically use the new method, which can cause connection failures with older PHP drivers or connection libraries that do not support caching_sha2_password, especially over unencrypted connections without SSL.

Modern PHP versions with a current mysqlnd driver, as run in a Magento 2.4.8 stack on PHP 8.4, fully support caching_sha2_password, so in most cases no action is needed. Existing user accounts created before the upgrade keep their original authentication method, an upgrade does not change it automatically, but an explicit test of every production connection string right after the upgrade is still worth the effort.

-- Check a user's current authentication plugin
SELECT user, host, plugin FROM mysql.user WHERE user = 'magento_app';

-- If needed, explicitly reset it back to mysql_native_password
ALTER USER 'magento_app'@'%' IDENTIFIED WITH mysql_native_password BY 'a-strong-password';

6. Reserved keywords and other data type subtleties

Every MySQL version adds new reserved keywords that previously worked fine as table or column names. In MySQL 8.0 these include RANK, ROW, GROUPS, WINDOW and several others tied to the newly introduced window functions. An existing custom table with a column named rank without backtick quoting works fine under 5.7 and raises a syntax error under 8.0.

Before the upgrade it is worth checking every table and column name in your own schema, especially in custom modules and third party extensions, against the current list of reserved words in the MySQL 8.0 documentation. Existing objects with a now reserved name usually keep working as long as they are consistently referenced with backticks, but new DDL statements without quoting will fail.

7. The mysql_upgrade workflow in practice

Since MySQL 8.0.16, the separate mysql_upgrade command has become unnecessary, the server checks and updates its internal system tables and data dictionary automatically on the first start after a binary upgrade. The actual procedure therefore follows a classic in-place upgrade path: stop the server, install the new binary packages, restart the server against the same data directory, and follow progress in the error log.

Before the actual upgrade, the pre-check tool built into MySQL Shell, util.checkForServerUpgrade(), proactively checks for known incompatibilities like reserved words, removed system variables, or problematic collations, without changing anything itself. This pre-check step should always run before the actual upgrade maintenance window, so any issues found can be fixed calmly instead of surfacing as a surprise mid-window.

# Pre-check before the actual upgrade using MySQL Shell
mysqlsh -- util check-for-server-upgrade --user=root --password

# After installing the new binary packages, restart the server
# and watch the automatic upgrade process in the error log
bin/log mysql/error.log

8. Test strategy before the production upgrade

A production upgrade should never be the first test. The safest path is a full copy of the production database on a separate staging instance, brought up there under the new 8.0 version, followed by a complete Magento reindex and a full run of the production test suite, including the most business-critical checkout and order flows.

It also pays to run a direct before and after comparison of the slow query log under comparable, simulated load, to identify queries that got slower because of changed optimizer decisions. Only once these tests run stably across several days without new critical errors is the production maintenance window the right next step.

9. Rollback strategy if the upgrade causes trouble

Since no official downgrade path from 8.0 back to 5.7 exists, the rollback strategy needs to be settled before the upgrade, not improvised afterward. The most reliable safeguard is a complete, consistent backup taken immediately before the upgrade, ideally a filesystem or storage level snapshot, which can restore the old 5.7 instance within minutes rather than requiring a lengthy logical restore from a mysqldump.

For environments with enough capacity, a blue-green approach is the more robust alternative: a not-yet-upgraded 5.7 replica stays untouched in the background during the upgrade window and can be promoted to the new primary within minutes if things go wrong, while the problematic 8.0 instance gets analyzed at leisure. A clearly defined, time-boxed maintenance window with a fixed abort criterion belongs in the plan either way.

10. Summary

11. FAQ: MySQL 5.7 to 8.0: Upgrade Guide