MySQL-to-MariaDB and MariaDB-to-MySQL Migration: Compatibility Matrix and Data Tests

Learning outcomes

A team points an application using the MySQL protocol at MariaDB and sees it connect successfully. That proves only wire-level compatibility for that interaction. Migration must answer a larger question: does the source system’s behavior survive on the target? Compatibility is directional: MySQL→MariaDB and MariaDB→MySQL have different hazards.

1. Build the matrix before moving data

Every row should end in PASS, TRANSLATE, REPLACE, or BLOCK—never “probably compatible.”

2. A reproducible schema/data probe

CREATE DATABASE IF NOT EXISTS migrate20_l3 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE migrate20_l3;CREATE TABLE customers (  customer_id BIGINT PRIMARY KEY,  email VARCHAR(190) NOT NULL UNIQUE,  credit DECIMAL(18,4) NOT NULL,  profile_json LONGTEXT NULL,  created_at DATETIME(6) NOT NULL,  CHECK (JSON_VALID(profile_json) OR profile_json IS NULL)) ENGINE=InnoDB;INSERT INTO customers VALUES(1,'a@example.test',100.1250,'{"tier":"gold","emoji":"✓"}','2026-08-20 10:00:00.123456'),(2,'b@example.test',0.0000,'{"tier":"free"}','2026-08-20 11:00:00.000001');SHOW CREATE TABLE customers;SELECT @@sql_mode, @@character_set_server, @@collation_server;

3. Data acceptance is stronger than import success

SELECT COUNT(*) AS row_count,       SUM(credit) AS credit_sum,       MIN(created_at) AS min_created,       MAX(created_at) AS max_createdFROM migrate20_l3.customers;SELECT customer_id,       SHA2(CONCAT_WS('|',email,CAST(credit AS CHAR),COALESCE(profile_json,''),DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s.%f')),256) AS row_hashFROM migrate20_l3.customersORDER BY customer_id;

Hashes are useful only if both sides serialize values identically. Business invariants—counts by state, monetary sums, uniqueness, referential integrity—are often more portable than a whole-row textual hash.

4. Wrong approach: copy system privilege tables

MySQL and MariaDB system-table structures differ and evolve. Copying raw mysql.user or related tables can corrupt privilege semantics or leave unusable definitions. Recreate accounts with supported CREATE USER/GRANT statements, target-supported authentication plugins, and explicit role mapping.

5. GTID and replication boundary

MariaDB GTIDs use domain-server-sequence triplets and do not share MySQL GTID semantics. Current MariaDB migration guidance warns against treating them as interchangeable. For cross-vendor replication, use the exact supported source/target method—often binary log file/position and compatible row-format settings—rather than copying a same-vendor GTID recipe.

6. Collation and JSON tests must use semantics

SELECT 'straße' = 'STRASSE' COLLATE utf8mb4_unicode_ci AS equality_probe;SELECT JSON_VALID(profile_json) AS valid_json, profile_jsonFROM migrate20_l3.customers ORDER BY customer_id;-- Capture exact DDL/metadata on both source and target:SHOW FULL COLUMNS FROM migrate20_l3.customers;SHOW CREATE TABLE migrate20_l3.customers;

A migration can return the same bytes yet change equality/sort semantics because collations differ. Likewise MariaDB’s JSON handling is not identical to MySQL’s native JSON implementation. Test application operations, not just type names.

7. Migration runbook lab

Use two disposable containers: one exact MySQL source version and one exact MariaDB target—or reverse them for the opposite direction. Export only application schemas/data, not raw system schemas. Restore, recreate users, run the compatibility matrix, execute invariants and the application integration test suite. Record every translation. The lab’s outcome may legitimately be BLOCK if a feature has no safe counterpart.

Production judgment and bridge to Lesson 4

Call the migration ready only when the directional matrix, data invariants, application suite, auth model and cutover/rollback method are proven on exact versions. Lesson 4 applies the same compatibility discipline inside one application: evolving schemas while old and new code coexist.

Directional migration testing: compatibility is a matrix, not a yes/no property

MySQL↔MariaDB migration must be described with exact source and target versions because compatibility is directional. The wire protocol may let an application connect while storage formats, JSON representation, collations, authentication plugins, GTID models, system variables, SQL modes, optimizer semantics, and administration tools differ. Build the matrix before choosing physical copy, logical dump/restore, replication-assisted cutover, or application dual-write/CDC strategies.

Start with schema extraction. Parse or restore the DDL into a disposable target and inventory every failure or silent rewrite: data types, defaults, generated columns, indexes, partitioning, table options, collations, routines, functions, triggers, events, views/definers, and privileges. A target that accepts the DDL may still implement a type differently, so add semantic tests for JSON, timestamp/time-zone behavior, string comparison, zero/invalid dates if relevant, and generated/default expressions.

Then move representative data and test invariants at several levels. Row counts catch gross omissions but not value changes. Aggregate totals and min/max distributions catch some truncation or conversion defects. Business invariants catch relationships the schema may not encode. Sampled or partitioned checksums can increase confidence when full comparison is too expensive. Keep the validation queries under source control so the cutover rehearsal and production migration use the same evidence.

Authentication and operations are separate migration surfaces. Recreate accounts/roles using target-native syntax instead of copying system tables. Test backup/restore tools, monitoring queries, connector TLS/authentication, migration tooling, and scheduled jobs. For replication-assisted migrations, verify the exact binlog/GTID compatibility and supported direction; do not infer interoperability from the word “GTID.”

Finally, rehearse rollback. Once target-only writes begin, the old source is stale unless an explicitly supported reverse data path exists. Define the point of no return, how traffic is fenced during cutover, how write acceptance is proven on the new primary, and what data reconciliation is required if the cutover is aborted after writes. A migration is safe only when the data and application contract—not merely the socket connection—has been proven in the intended direction.

Cutover reconciliation: prove source and target agree at a named boundary

A production migration needs a boundary that both systems can name. For a dump/restore migration it may be the backup snapshot time plus captured binary-log coordinates. For replication-assisted cutover it is the point where the target has applied through the final source position after writes are fenced. For application-mediated migration it may be an application sequence or event offset. Without a named boundary, “row counts match” can compare different moments and produce false confidence.

At that boundary, run the same validation set on source and target. Compare entity counts, financial or quantity totals, min/max identifiers and timestamps, null/invalid-value counts, business invariants, and sampled hashes of canonicalized rows. Canonicalization matters when physical representations differ—JSON ordering, collation rules, timestamp formatting, or numeric rendering can make byte-level output differ while business values are equivalent. Define the comparison semantics before the migration.

Then run behavior tests against the target: representative writes, constraint failures, Unicode comparisons, JSON reads/updates, transaction retries, generated identifiers, routines/events, and application queries with real connector settings. A migration is not complete when data has arrived; it is complete when the target can enforce the same intended business contract under production-like concurrency.

Keep a delta window after cutover. Monitor error-rate changes, slow statements, collation/sort surprises, authentication failures, and data-reconciliation alerts. The rollback or forward-fix decision should be time-bounded and tied to explicit thresholds while the source backup and migration evidence remain readily recoverable.

Tooling compatibility deserves its own migration lane

DBAs often validate SQL while overlooking deployment automation. Rehearse schema migration tools, dump/restore utilities, backup scripts, monitoring collectors, ORM dialect detection, connector capability checks, and CI smoke tests against the target. A tool that identifies the server as “MySQL compatible” may choose MySQL-specific SQL or metadata queries that are invalid or semantically different on MariaDB. Record tool versions and the exact commands that passed.

Authoritative references

• MariaDB migration documentation for MySQL↔MariaDB compatibility.
• MariaDB documentation on GTIDs, JSON, collations, account management and logical backups.
• Official MySQL documentation for source/target behavior when MySQL is part of the migration.