StackPioneer

A MySQL 5.7 to 8.0 Upgrade Checklist That Holds Up

MySQL 5.7 reached end of life in October 2023. Running it now means no security patches and a growing list of tools that no longer test against it. The 8.0 upgrade is well-trodden and mostly smooth — but it is also one-way (there is no supported downgrade from 8.0's data dictionary), which means the checklist matters more than usual. This is ours.

1. Run the upgrade checker first

MySQL Shell ships an upgrade checker: util.checkForServerUpgrade(). Point it at the 5.7 server and it reports removed features you use, reserved-word collisions, orphaned temporal columns, and problem character sets — before you commit to anything. Fix everything it lists as an error; read everything it lists as a warning. Ten minutes here saves a failed upgrade at 2 a.m.

2. The query cache is gone

MySQL 8.0 removed the query cache entirely. If your 5.7 server has query_cache_size set and your application quietly depends on it for read performance, you will feel the removal as a latency regression on hot read paths.

Find out before the upgrade: set query_cache_type=0 on 5.7 for a day and watch. If performance holds, you are fine. If it degrades, you need an application-side cache (Redis or memcached in front of the hot queries) as part of the upgrade project, not as a surprise afterwards.

3. Character sets and collations

The default character set changes from latin1 to utf8mb4, and the default utf8mb4 collation changes to utf8mb4_0900_ai_ci. Two practical consequences:

  • Mixed-collation joins. New tables created after the upgrade default to the 0900 collation; old tables keep utf8mb4_general_ci or latin1_swedish_ci. Joining a new table to an old one on a string column can throw "illegal mix of collations" or, worse, silently stop using an index. Decide the target collation up front and set collation_server explicitly rather than accepting drift.
  • Old dumps. A dump that says utf8 means utf8mb3, which is deprecated. Plan the utf8mb3 → utf8mb4 conversion table by table; it rewrites data and takes real time on large tables.

4. SQL mode and grammar changes

  • NO_AUTO_CREATE_USER is removed from sql_mode; a 5.7 config that sets it will stop the 8.0 server from starting. Clean the option file first.
  • GRANT no longer creates users implicitly — every user needs an explicit CREATE USER. Audit provisioning scripts.
  • GROUP BY ... ASC/DESC implicit sorting is gone. Queries that relied on GROUP BY producing ordered output need an explicit ORDER BY. These fail silently — output is simply in a different order.
  • New reserved words arrived with window functions: RANK, GROUPS, ROW_NUMBER, LATERAL, and friends. A column or alias named rank now needs backticks. The upgrade checker flags these.

5. Authentication and old clients

8.0's default auth plugin is caching_sha2_password. Older client libraries — including the PHP drivers shipped with EOL PHP versions — only speak mysql_native_password and will fail to connect with an unhelpful error.

If the application still runs on old PHP, either upgrade the PHP side first (the better order) or set default_authentication_plugin=mysql_native_password as a bridge and record it as debt to remove.

6. Rehearse on a restored backup

The actual upgrade rehearsal is also your backup test. Take a full backup of production (xtrabackup or a consistent mysqldump), restore it onto a scratch server, and run the in-place upgrade there. You learn three things at once: that your backup actually restores, how long the upgrade takes on your data volume, and whether anything in the schema trips it. Then point a staging copy of the application at the upgraded scratch server and run your smoke tests.

Because there is no downgrade, the production plan should be one of:

  • Replica-based: build an 8.0 replica off the 5.7 primary, let it catch up, verify, then promote. Rollback is "point back at the old primary".
  • In-place with a full backup and a tested restore path, accepting a longer rollback if something goes wrong.

For most estates the replica path is worth the extra setup.

7. After the switch

Watch the error log for deprecation notices, re-run ANALYZE TABLE on the big tables, and re-check the slow query log for a week — the 8.0 optimizer is better overall but occasionally picks a different plan on a query 5.7 handled fine. Keep the histogram feature (ANALYZE TABLE ... UPDATE HISTOGRAM) in your pocket for the stragglers.

None of this is exotic. It is a checklist, run in order, with a rehearsal in the middle — which is exactly what an irreversible upgrade should be.