Upgrading a Database Major Version Without Downtime: The Side-by-Side Replica Playbook
Add new-version replicas, soak them on real reads, promote, then clean up. The four stages, the point of no return, and the MySQL 5.7 to 8.0 gotchas.
On this page
End of life dates do not care that your database is the busiest thing you run. Eventually the version loses security fixes and you have to move. The only question is whether the move is a planned, reversible procedure or a weekend of improvisation.
Why bother
The upgrade Uber described, MySQL 5.7 to 8.0, was prompted by end of life, which means no more security patches or bug fixes, plus useful features: better indexing and concurrency, window functions, richer JSON and spatial support, dual passwords so credentials can be rotated without an outage, and instant ADD COLUMN for schema changes with almost no downtime. Their scale made the approach matter: over 2,100 clusters, more than 16,000 nodes and around three million queries per second, with primaries taking writes and replicas in other data centres.
At that size, manual upgrades are impossible. At any size, they are riskier than a scripted procedure you have rehearsed.
Two strategies
| In place | Side by side | |
|---|---|---|
| How | Stop the service, upgrade software on the same host, restart | Add new-version nodes next to old ones as replicas |
| Downtime | A visible outage per node or cluster | Minimal, only the promotion |
| Rollback | Hard, often a restore | Easy until promotion, old nodes still run |
| Testing | Synthetic only | Real production reads on the new version |
| Cost | No extra hardware | Temporary extra nodes |
Side by side wins for anything where downtime or surprise is expensive. The temporary hardware is cheap insurance.
The four stages
- 1. Pre-maintenance: add new-version replicas
New nodes join each cluster as replicas of the existing primary. The old version keeps serving everything, and nothing changes for users. If a new node misbehaves, drop it. No data is at risk.
- 2. Soak: let real traffic find the problems
Route a share of production reads to the new replicas for about a week. Watch latency percentiles, error rates, CPU, memory and replication lag, and compare against the old nodes under the same traffic. A week catches weekly peaks, batch jobs and rare queries.
- 3. Maintenance: promote
Promote a new-version node to primary so it takes writes. This is the point of no return: replication typically flows from older to newer versions, not the reverse, so the old nodes can no longer follow.
- 4. Post-maintenance: clean up
Remove the old-version nodes once the new primary has proven itself. Only now do you stop paying for the safety net.
Automated rollback is easy while the old primary still leads. After promotion it is a restore or a reverse migration. Treat “promote” as a gate that requires an explicit pass from the soak metrics, not as the next item on a checklist.
What actually broke in the real upgrade
EXPLAIN output before and after.STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY are on by default in 8.0. Clusters that ran without them had legacy queries that suddenly failed. Fix the queries, then turn the modes on before upgrading so the failures arrive early.utf8mb4_unicode_520_ci to utf8mb4_0900_ai_ci, which changes sorting and comparison. Mixed collations across clusters cause mismatches and index surprises. Pin the collation explicitly everywhere.The results, and the caveat
Uber reported, after the upgrade: about 29 percent better p99 insert latency under high concurrency, 33 percent better read latency, 47 percent better updates, a 94 percent reduction in database lock time on the client side, and a 78 percent drop in query time for some queries. Treat those as their workload’s numbers, not a promise. The point is that a major version can pay for itself, but you only know on your own traffic, which is why the soak exists.
Your checklist, any database
| Stage | Do |
|---|---|
| Before | Read the release notes for removed features and changed defaults. Inventory clients and extensions. Record baseline latencies |
| Staging | Replay production queries against the new version. Diff query plans. Run the application test suite |
| Replicas | Add new-version replicas, serve a slice of reads, compare metrics against old nodes |
| Soak | At least one full traffic cycle. Keep a rollback owner on call |
| Promote | One cluster at a time, automated, with a stop-the-line condition |
| After | Keep old nodes briefly, then retire them. Update runbooks, backups and monitoring for the new version |
Do
Automate each stage with monitoring and alerts, pin settings that changed defaults, upgrade the least critical clusters first, and rehearse the full path including rollback before touching anything important.
Don't
Upgrade the production primary in place because “the release notes looked fine”. Optimizer and collation changes do not show up in release notes as warnings about your queries.
Takeaway
A major database upgrade is a controlled experiment: run the new version beside the old one, feed it real reads, compare, promote only when the data says so, and retire the old nodes last. Test the four usual breakers early, automate everything that repeats, and remember that promotion is the line you cannot walk back across.
Frequently asked questions
Why prefer a side-by-side upgrade over an in-place upgrade?
In-place upgrades stop the service, change the software on the same machine and make rollback hard. Side-by-side adds new-version nodes beside the old ones as replicas, tests them on real traffic and switches over by promotion, keeping the old nodes available for rollback until the last moment.
What is the point of no return in a replica-based upgrade?
Promoting a new-version node to primary. Replication usually works from older to newer versions but not back, so once the new primary takes writes, the old nodes cannot follow. Everything must be validated on replicas before that promotion, because rollback after it is a much harder operation.
What commonly breaks in a MySQL 5.7 to 8.0 upgrade?
Query plans that change and slow some queries, stricter SQL modes rejecting sloppy statements, the default collation changing from utf8mb4_unicode_520_ci to utf8mb4_0900_ai_ci, and old client libraries that cannot authenticate or speak the new protocol. All four can be found before cutover with the right tests.
How long should a soak period last?
Long enough to see your whole traffic cycle, typically at least a week so weekly peaks, batch jobs and month-start patterns all run. A shorter soak finds crashes but misses rare slow queries and periodic jobs, which are exactly the things that regress after a version change.
/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.