MySQL 5.7 end-of-life: upgrading without downtime

MySQL 5.7 reached end-of-life on October 31, 2023. There are no more security patches and no more bug fixes. Hosting providers have started forcing migrations; managed services have started charging extended-support premiums. The upgrade is not optional anymore — only the timing is.

It is also not only a version bump. Collation defaults change, JSON behavior tightens, sql_mode gets stricter, and replication topology assumptions shift. A clean upgrade is sequenced. A rushed one finds the breakage in production.

1. Confirm the real version

The version on the marketing copy of your hosting plan is not always the version your application is talking to.

• SELECT VERSION(); from the application’s actual connection
• mysql --version on the CLI
• Replica versions if any are in the topology
• The version of the binary that produces backups (older mysqldump writes incompatible files)
• The version of the client driver in the application — PDO, mysqli, or the ORM’s underlying driver

Mismatches here are common on systems that have been running for years. The application is the source of truth.

2. Inventory what depends on 5.7 quirks

Most of the breakage in MySQL 5.7 → 8.x upgrades comes from behaviors that were quietly relied on. Search the codebase for these:

• Character set and collation. 5.7 defaults are often utf8 (which is utf8mb3, three-byte UTF-8). 8.x defaults to utf8mb4. Mixed collations between tables and connections cause silent join breakage and emoji-related data loss.
• JSON column behavior. 8.x is stricter about JSON validation and changes the result of some JSON_* functions. If the application stores JSON in TEXT columns and parses it in PHP, that is fine; if it uses JSON columns, test the queries.
• sql_mode defaults. 8.x ships with a stricter mode set, including ONLY_FULL_GROUP_BY. Queries that worked in 5.7 because of permissive grouping will fail outright in 8.x.
• Reserved words. 8.x adds new reserved words (notably RANK, ROW, GROUPS, several window-function-related). Schema columns or aliases using them now need backticks.
• Authentication plugin. 8.x defaults to caching_sha2_password. Older client libraries cannot authenticate against it without configuration.

Greppable patterns: GROUP BY without aggregates on every non-grouped column, utf8_ collation names in migrations, JSON_ functions, mysql_native_password in connection strings, and any column named after a new reserved word.

3. Pick the target

There are three reasonable targets and one trap.

• MySQL 8.0 — broad support, the default upgrade path, supported until April 2026 (post-EOL extended). Fine if the migration completes well before then.
• MySQL 8.4 LTS — released April 2024, long-term support. The right target for systems being upgraded after early 2025.
• MariaDB 10.11 / 11.4 — drop-in replacement for many use cases, with its own migration considerations (some JSON_* functions differ, replication is not interoperable). Worth considering only if you have a specific reason.
• The trap: skipping 8.0 entirely on a system that is already on 5.7. The migration tooling is more battle-tested for 5.7 → 8.0 than for 5.7 → 8.4. If the system is sensitive, take it in two steps over time, not one big jump.

4. Test the dump-and-restore on a copy first

Before any cutover, prove the data survives the round trip on a non-production environment.

• Dump from 5.7. Restore to a clean 8.x instance. Note every warning the restore emits — none are fine to ignore.
• Run the application’s smoke tests against the restored copy.
• Specifically test: search queries with non-ASCII characters, queries that group by partial columns, JSON-column queries, and any query that uses functions whose behavior changed.
• If you find collation drift, the fix is usually a CONVERT TO CHARACTER SET utf8mb4 per affected table — but that is a destructive operation. Do not run it in production without a backup tested on the new version first.

This is where most of the unpleasant surprises surface. They are cheaper to find here than at 2 a.m. on cutover night.

5. Replication-first cutover for zero downtime

For systems that cannot tolerate a downtime window, the cutover is replication-driven, not dump-and-restore.

• Stand up a new MySQL 8.x instance. Configure it as a replica of the existing 5.7 primary.
• Verify replication is healthy and lag is consistent. Run smoke tests against the replica.
• Switch read traffic to the new instance first, if the application supports read/write splitting. Watch for a few days.
• During a low-traffic window, promote the new instance to primary. Update the application’s connection string. Retire the old primary.
• Keep the 5.7 primary running, read-only, for at least 7 days as a recovery option.

For systems that can tolerate a 5-to-15-minute window, a coordinated maintenance-mode dump-and-restore is simpler and equally safe. Pick based on actual SLAs, not assumed ones.

6. Smoke test against business flows

The smoke tests after a database upgrade are the same shape as after a PHP upgrade: the flows that pay the bills, in priority order.

• Login (with stored hashes — this is where authentication-plugin issues surface)
• Search and listing pages with non-ASCII queries
• Checkout, payment, and the resulting order records
• Reports and exports — these often hit GROUP BY queries that 5.7 tolerated and 8.x does not
• Any feature that writes to or reads from JSON columns
• Background jobs that batch-update large tables — the locking semantics changed slightly

Tail the slow query log for at least 24 hours after cutover. The query optimizer on 8.x is meaningfully different; queries that used to be fast can briefly become slow in ways that resolve once the optimizer has stable statistics.

What to write in the runbook

After a clean cutover, leave behind:

• The exact version on each instance and what they replaced
• The collation, character set, and sql_mode in effect
• The rollback path (which is mostly “the 5.7 read-only primary is still alive until X date”)
• Every query that was fixed during the migration, with a note explaining why it broke
• The date the old primary can be safely retired

Most teams skip this last step. The runbook is what makes the next upgrade — to MySQL 8.4 or beyond — a 4-week project instead of a 12-week one. It is part of the same broader playbook as legacy PHP modernization and the PHP EOL checklist — staged, reversible, and audit-first. If the runbook does not exist yet, an audit is the way to produce one without making the upgrade itself the discovery exercise.