Chapter 20 · Schema Migration, Upgrades, Compatibility, and Zero/Low-Downtime Change

Rollback Planning, Data Validation, Canaries, and Post-Upgrade Performance Verification

Design rollback as a decision system using tested backups, canaries, business invariants, plan and latency evidence, explicit points of no return, and decision authority.

Advanced180–250 minmigration/upgrade compatibility labMySQL Community Server 8.4.10 LTSMySQL Shell 8.4.10 for upgrade checkssingle node mandatory · topology extension optionalLast reviewed: August 2026

Learning outcomes

Rollback planning is not a sentence that says “restore if something breaks.” Once an upgraded system accepts writes, changes authentication behavior, or uses target-only functionality, the cheapest safe recovery option can change. ServiceHub therefore defines what evidence exists before the change, what canary proves success, where the point of no return is, and who decides roll-forward versus restore.

Distinguish supported in-place downgrade from logical restore, replication, and pre-upgrade backup rollback.

Treat a backup as rollback-capable only after a clean restore test.

Define canary queries, connector tests, checksums/business invariants, plan comparisons, and latency/error baselines.

State a point of no return before the window begins.

Use explicit decision authority and RPO/RTO/correctness criteria for roll-forward versus rollback.

Rollback method depends on the exact release pair

Current MySQL 8.4 downgrade documentation distinguishes within-LTS paths from cross-series rollback. Within 8.4 LTS, in-place downgrade is supported for specified release pairs; other paths can require logical dump/load or replication and may be conditional on not having applied target-only functionality. Never replace packages based on memory of an older release.

Mechanism	Appropriate when	Prerequisite
in-place downgrade	exact release pair explicitly supports it	verified path and compatible state
logical restore	clean reconstruction/cross-series rollback	tested dump/backup and restore time
replication rollback target	old-compatible target deliberately maintained	GTID and write compatibility
pre-upgrade restore	return to a known data boundary	accepted RPO and preserved logs
roll forward	rollback loses more data or is unsupported	known remediation and authority

A backup is not a rollback until it has been restored

Before upgrade, preserve application data, migration state, relevant grants/system metadata, configuration, version information, and binary-log/GTID continuity. Restore the backup into a disposable target and run deterministic checks. Chapter 13 taught the mechanics; this chapter makes the tested restore an upgrade gate.

sql · pre-upgrade data and migration fingerprint

USE servicehub_change_lab;SELECT COUNT(*) AS work_orders,       MIN(work_order_id) AS min_id,       MAX(work_order_id) AS max_id,       SUM(status='open') AS open_ordersFROM work_orders;SELECT COUNT(*) AS missing_referenceFROM work_orders WHERE public_reference IS NULL;SELECT version_no,migration_name,checksum_sha256,statusFROM schema_migrations ORDER BY version_no;SELECT @@version,@@global.gtid_executed,@@sql_mode,       @@character_set_server,@@collation_server;

These are compact business invariants rather than a cryptographic digest of the entire database. Critical systems should add stronger reconciliation/checksum logic, but every release should at least define known expected outcomes that can be compared after restore or upgrade.

Canaries span SQL, data, clients, and plans

sql · post-upgrade read-only canary bundle

SELECT @@version AS server_version,       @@version_comment AS edition,       @@sql_mode AS sql_mode,       @@collation_server AS collation;SHOW CREATE TABLE work_orders\GSELECT COUNT(*) AS missing_referenceFROM work_orders WHERE public_reference IS NULL;EXPLAIN FORMAT=TREESELECT work_order_id,status,opened_atFROM work_ordersWHERE tenant_id=17ORDER BY opened_at DESCLIMIT 20;-- On staging, EXPLAIN ANALYZE can provide execution evidence.-- Do not make applications parse human-oriented EXPLAIN text.

Also run the real connector/ORM integration smoke test from Chapter 19. Compare critical query plan shape plus throughput, error rate, and p50/p95/p99 under equivalent workload conditions. Do not invent a percentage improvement from a toy lab or a single warm query.

Failure scenario: server is healthy but a connector or plan regresses

A successful server startup does not prove release success. An older client may fail authentication, or a critical query may choose a materially worse plan. The correct response is evidence-driven: inspect statistics/configuration/cardinality and client compatibility. Roll forward with a targeted fix when that is safer; restore or use a supported downgrade when the rollback criteria are met.

text · go/no-go decision record

Decision time: __________________Decision authority: ______________Source / target versions: _________Backup restore test: PASS / FAILData invariants: PASS / FAILConnector/TLS/auth canary: PASS / FAILReplication/HA state: PASS / FAIL / N/ACritical plans: ACCEPT / REGRESSIONp95/p99/error rate vs baseline: __________________Point of no return crossed? YES / NODecision: CONTINUE / PAUSE / ROLL FORWARD / RESTORE / SUPPORTED DOWNGRADEEvidence / owner: __________________

Define the point of no return before starting

A point of no return is a stage after which rollback becomes unsupported, loses unacceptable data, or is less safe than roll-forward. Examples include enabling target-only functionality, accepting writes the old release cannot interpret, expiring required backup/binlog artifacts, or contracting a schema required by the old application.

Rollback can worsen RPO

If a healthy upgraded system has accepted valid writes for hours, restoring a pre-upgrade image can lose more data than a controlled roll-forward fix. Correctness, recoverability, and business RPO must be considered together.

Final Chapter 20 acceptance and optional cleanup

sql · verify disposable lab state

USE servicehub_change_lab;SELECT COUNT(*) AS rows_total,       SUM(public_reference IS NULL) AS missing_referenceFROM work_orders;SELECT version_no,migration_name,statusFROM schema_migrations ORDER BY version_no;-- After exporting evidence, optional cleanup:-- DROP DATABASE servicehub_change_lab;-- DROP USER IF EXISTS 'upgrade_check'@'127.0.0.1';-- DROP USER IF EXISTS 'upgrade_check_too_small'@'127.0.0.1';

Chapter 21 moves to specialized MySQL capabilities—NDB Cluster, X Protocol/Document Store, spatial workloads, and FULLTEXT search. The change discipline remains relevant because specialized features also carry version, topology, client, and rollback constraints.

Rollback is a data timeline, not only a software timeline

Suppose the pre-upgrade backup represents 10:00, the upgrade completes at 10:20, and the new server accepts customer writes until 12:00 before a serious regression is found. Restoring the 10:00 backup without replay/reconciliation would discard two hours of valid work. That is why rollback design must include Recovery Point Objective (RPO), binary-log continuity, and whether post-upgrade writes can be replayed safely on the rollback target.

The rollback decision should therefore answer two different questions: “Can the old software run?” and “Can the old environment represent all data written since cutover?” A technically supported binary downgrade can still be the wrong business choice if target-only schema/features or incompatible writes have already been introduced.

Build the baseline before the change window

Performance verification is defensible only when pre-upgrade evidence exists. Select a small set of critical ServiceHub queries and transactions, record their plans, execution evidence on a staging/canary system, latency distribution, throughput, and error rate under a defined workload. Capture server variables that can influence plans and behavior, plus relevant statistics timestamps. After upgrade, repeat the same workload conditions as closely as possible.

sql · baseline optimizer/statistics evidence

SHOW INDEX FROM servicehub_change_lab.work_orders;ANALYZE TABLE servicehub_change_lab.work_orders;EXPLAIN FORMAT=TREESELECT work_order_id,status,opened_atFROM servicehub_change_lab.work_ordersWHERE tenant_id=17ORDER BY opened_at DESC LIMIT 20;

If a plan changes after upgrade, first establish whether statistics, collation, optimizer defaults, indexes, or data distribution changed. A different plan is not automatically worse; measured latency, resource use, and correctness determine whether it is a regression.

Canary scope should grow deliberately

Start with a disposable/staging restore, then a production-like canary node or replica where supported, then a small fraction of application traffic, and only then the full rollout. At each stage, verify data invariants, application authentication/TLS, migration version, replication/HA health, logs, and critical query behavior. Annotate the deployment in observability dashboards so deviations can be correlated with the change.

Keep the canary period long enough to exercise the workload that matters. A system with a heavy hourly report or nightly batch cannot be validated by five minutes of daytime traffic. There is no universal observation duration; derive it from workload cycles and risk.

Decision authority prevents panic-driven rollback

When a regression appears, teams can lose time debating who may stop or reverse the rollout. The runbook should name one decision authority (and backup authority), define evidence thresholds, and state which actions are reversible before and after the point of no return. Operators can then pause the rollout immediately while the authority chooses roll-forward, restore, or a supported downgrade.

Production judgment

Rollback is not automatically the conservative choice. If restoring violates RPO, if downgrade is unsupported for the state already created, or if the defect has a narrow forward fix, roll-forward can preserve more correctness. The decision must be documented, not improvised.

Post-upgrade observation completes the release

Do not declare the change complete as soon as every node reports the target version. Keep an observation window for application error rate, authentication failures, replica/group health, redo/I/O behavior, query latency percentiles, deadlocks/lock waits, backup success, and recovery continuity. Only after the observation window passes should temporary rollback infrastructure, old artifacts, or expanded legacy schema be retired according to the contract plan.

Run the post-upgrade canary through a real driver configuration

SQL executed from an administrator shell can pass while the application still fails. The final canary should use the same connector family, TLS verification, account, schema, and timeout policy as production. It should stay read-only until the release authority approves writes, and it should report the exact driver error rather than converting every failure into “database unavailable.”

python · deterministic connector canary pattern

import osimport mysql.connectorcnx = mysql.connector.connect(    host="127.0.0.1",    user="servicehub_app",    password=os.environ["SERVICEHUB_DB_PASSWORD"],    database="servicehub_change_lab",    ssl_verify_cert=True,    connection_timeout=5,)cur = cnx.cursor()cur.execute("SELECT @@version, @@sql_mode, @@time_zone")print(cur.fetchone())cur.execute("SELECT COUNT(*), SUM(public_reference IS NULL) FROM work_orders")print(cur.fetchone())cur.close()cnx.close()

In a real TLS deployment, also supply the trusted CA and enable hostname verification according to the connector’s current options. The important acceptance rule is deterministic: the connection succeeds using the production security contract, the expected server/session metadata is returned, and the business invariant matches the pre-upgrade record. A driver exception is release evidence, not something to hide with infinite retries.

Knowledge check

Why is package replacement not a universal rollback?
What proves a backup is usable?
What should performance verification compare?
What is a point of no return?
Who decides rollback versus roll-forward?

Reveal answers

Downgrade support depends on the exact release path and compatible data/state.
A successful restore to a clean/disposable target plus application/data validation.
Equivalent workload conditions, plan evidence, latency percentiles, throughput, and error rate.
A predefined stage after which rollback is unsupported, loses too much data, or is less safe than roll-forward.
A named authority using documented evidence and business correctness/RPO/RTO criteria.

Authoritative references

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only assets compatible with the Ethereum/ERC-20 network. Do not send TRC-20/TRON assets.