A major version upgrade with a rollback position at every stage

Overview

A hosting provider was running MySQL 5.7 past its supported life across a large estate of customer databases. The upgrade had been deferred twice because no one could commit to the maintenance windows that in-place schema changes would require. AceMQ designed and executed the migration.

Challenge

The upgrade carried several distinct risks: authentication plugin changes that would break older clients, the default character set and collation move that affected sorting and index sizes, reserved word conflicts in existing schemas, and optimizer behavior changes that could alter plans on critical queries. Several tables were large enough that blocking ALTER statements were not an option.

Environment

On-premises MySQL estate hosting multi-tenant customer databases with strict maintenance window limits.

Approach

AceMQ ran compatibility checks across the full estate before any change, then captured production query patterns and replayed them against an 8.0 instance to catch plan regressions in advance. Schema changes on large tables were performed with gh-ost so the tables stayed writable, and each phase had a defined rollback position.

Solution

• Ran upgrade compatibility checks across the estate covering reserved words, removed settings, and deprecated syntax
• Captured and replayed production query patterns against 8.0 to identify optimizer plan regressions before cutover
• Planned the character set and collation migration with explicit handling of index length and sort order effects
• Executed schema changes on large tables with gh-ost, keeping tables writable throughout
• Sequenced client authentication plugin changes ahead of the server upgrade to avoid connection failures
• Defined per-phase rollback positions and validated each on a staging copy before production execution

Outcome

Technologies