MariaDB / Advanced: evidence and reliable operations / Lesson 18

Migration and operational reliability

Plan a MariaDB upgrade or MySQL migration as a reversible evidence-driven change. Integrate schema evolution, service routing, recovery and version maintenance.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Build a compatibility and recovery checklist for exact versions.
Use expand-and-contract schema evolution with measurable cutover criteria.

Inventory actual dependencies

A product name does not define compatibility. Record source and destination MariaDB versions or the exact MySQL release, storage engines, collations, SQL modes, JSON use, generated columns, routines, connectors and replication topology. MariaDB and MySQL have diverged in authentication, GTIDs, optimizer output and SQL features. Logical portability must be tested with representative data and application behavior. Do not move data directories between products or major versions merely because the protocol or table syntax looks familiar.

Inspect effective configuration

Read-only inventory reveals current behavior before any migration is proposed. When a Unicode character set is explicitly named without COLLATE, its default collation selection changed in MariaDB 11.5. Separately, server defaults changed from latin1/latin1_swedish_ci to utf8mb4/utf8mb4_uca1400_ai_ci in 11.6. MariaDB 11.8 is the first LTS containing both changes, so an upgrade from an earlier LTS can expose both together. Relying on implicit defaults can therefore create a different destination schema. Record explicit table definitions, not just server defaults. A release series being available does not prove it is maintained under your support policy. Verify the official maintenance table during planning and avoid encoding a guessed latest version into the runbook.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT VERSION(), @@session.sql_mode;
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';
SELECT TABLE_NAME, ENGINE, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'harbor_lab' ORDER BY TABLE_NAME;

Results are deployment evidence. The course is reviewed on 2026-10-23, not a promise that any series remains supported forever.

Expand before removing old contracts

For a renamed field, first add the new compatible representation, deploy code that can handle both, backfill in bounded batches and validate convergence. Switch reads only after the application and replicas are ready, then retire the old column in a later change. ALTER TABLE locking and algorithm support depend on engine, operation and exact version. An INPLACE or INSTANT label is not a general promise of no metadata-lock wait, no resource cost or instant application compatibility.

Check migration convergence explicitly

Assume a synthetic customers table temporarily contains old_label and new_label with the same intended NULL semantics and comparison policy. MariaDB null-safe equality <=> permits a mismatch audit that also detects one-sided NULL values. A zero mismatch count is necessary for this backfill contract but does not prove collation-sensitive binary identity or application compatibility. If exact byte equality matters, design that check explicitly. Sample boundary rows and verify new constraints before retiring the old path.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT COUNT(*) AS mismatches
FROM customers WHERE NOT (old_label <=> new_label);
SELECT customer_id, old_label, new_label
FROM customers WHERE NOT (old_label <=> new_label)
ORDER BY customer_id LIMIT 20;

Expected after a correct backfill: mismatches = 0 and no sample rows, under the stated comparison policy.

Make rollback and maintenance concrete

Rehearse backup restoration, migration duration, application tests and write routing in isolation. Define a last safe rollback point: once the destination accepts unique writes, switching back without reverse synchronization can lose data. Cutover criteria should include mismatch counts, representative query latency, replica health and effective service grants. Keep the source protected for the agreed window. Track supported release branches and security updates continuously; reliability comes from tested recovery and controlled changes, not from staying on an old release indefinitely.

Common errors and fixes

A MySQL-only collation blocks import

Why it happens: A shared charset name was mistaken for identical collation catalogs.
How to fix it: Choose a supported destination collation and test changed equality and sorting.
Prevent it next time: Inventory collation names and uniqueness collisions before conversion.

A supposedly online ALTER waits indefinitely

Why it happens: A long transaction can retain conflicting metadata locks.
How to fix it: Inspect blockers and reschedule using the reviewed migration procedure.
Prevent it next time: Set deployment wait budgets and rehearse the exact operation with long transactions.

Rollback loses writes made after cutover

Why it happens: The rollback plan ignored divergent destination history.
How to fix it: Fence writers and reconcile through a tested reverse or recovery path.
Prevent it next time: Define rollback eligibility and data ownership before the first destination write.

Check your understanding

1. The destination uses a different case-insensitive collation. Why must unique email values be checked before conversion?

Show worked solution

The new equivalence rules can make previously distinct strings compare equal and violate a unique constraint. Test the target collation on a representative copy, identify collisions and agree on a business resolution before import. A mechanical collation-name substitution can change identity semantics even when every character is valid utf8mb4.

2. A team proposes a rollback by changing the application endpoint back one hour after destination writes begin. What evidence is missing?