MySQL to MariaDB Migration: Compatibility, Commands, and Gotchas

MariaDB began as a MySQL 5.1 fork in 2009 after Oracle acquired Sun (and with it, MySQL). For its first several years MariaDB was a near-drop-in replacement; the wire protocol matched, the SQL dialect was 99% the same, and the client tools were identical down to the binary name. The two have drifted apart since MySQL 5.7 / MariaDB 10.3. They're still compatible enough that the migration is mostly mechanical, but there are specific surfaces where the dialects diverge in ways that will silently corrupt your data if you don't handle them. Below is the migration I do in practice: backup with mysqldump, install MariaDB on a parallel port, restore, validate, swap. Plus the divergence cheat-sheet you need before you start.

How do I migrate from MySQL to MariaDB?

The supported migration is dump-and-restore, not in-place. First, install MariaDB alongside the existing MySQL on a separate port (3307 by default for the parallel install) so you can verify the restore before cutting traffic. Second, take a logical dump with mysqldump --all-databases --single-transaction --routines --triggers --events. Third, restore into MariaDB with mariadb -u root < dump.sql (the MariaDB client binary; mysql is now an alias). Fourth, validate row counts, foreign keys, and the divergent surfaces (JSON, virtual columns, authentication plugins). Fifth, stop the application, take a final delta dump, restore it into MariaDB, and switch the application's database connection string. An in-place file copy from /var/lib/mysql to a MariaDB data directory often "works" for older versions but is unsupported and breaks on the system schema; always dump-and-restore.

Jump to:

• Why migrate (and why not)
• Compatibility matrix
• Where the dialects diverge
• The dump-and-restore migration
• JSON columns: the silent corruption risk
• Authentication plugin mismatch
• Replication between MySQL and MariaDB
• Validation queries
• What to do next
• FAQ

Why migrate (and why not)

The case for migrating to MariaDB:

• License clarity. MariaDB is GPLv2-only, run by the non-profit MariaDB Foundation. MySQL is owned by Oracle, dual-licensed (commercial + GPL), and the open-source posture has been less consistent over the years.
• Features MySQL doesn't have. Sequences (proper CREATE SEQUENCE), system-versioned tables (temporal queries with AS OF), virtual columns with broader engine support, system-wide query timeout, the ColumnStore analytic engine.
• Different storage engines. MariaDB ships Aria (a crash-safe MyISAM replacement), ColumnStore, MyRocks, S3 storage engine. MySQL ships InnoDB and not much else.
• Active and friendlier release cadence. MariaDB 10.5+ has had a steady minor-release cadence.

The case for staying on MySQL:

• You use MySQL-specific features that MariaDB doesn't have. Window functions and CTEs are in both, but MySQL has more polished JSON, group replication and Group Replication-based InnoDB Cluster, and the official X Protocol.
• You're on AWS Aurora. Aurora's MySQL-compatible engine is much further along than the MariaDB-compatible engine (which Amazon hasn't shipped). Migrating off Aurora is non-trivial.
• Vendor support contracts. Oracle MySQL Enterprise has audit log, transparent data encryption, thread pool, and 24/7 support. MariaDB has equivalents through MariaDB plc but the procurement path is different.

For most self-hosted MySQL installs without an active enterprise contract, MariaDB is a reasonable destination. The migration cost is the dump-restore window plus the dialect-divergence fixes below.

Compatibility matrix

The general rule: pick the MariaDB version released around the same time as the MySQL version you're migrating from, or one major step later. Avoid migrating to a much older MariaDB.

Where the dialects diverge

The breaking divergences are not in the bulk of your SQL. They're in specific features. The cheat-sheet:

Read this table once. The two highest-impact rows for most apps are JSON and the authentication plugin. The rest are usually one-of cases.

The dump-and-restore migration

The safe procedure runs both MySQL and MariaDB side-by-side until you've validated the restore.

# 1. Take a full logical dump from MySQL
mysqldump \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    -u root -p > mysql-to-mariadb-$(date +%Y%m%d-%H%M).sql

# 2. Install MariaDB alongside MySQL (Debian / Ubuntu)
sudo apt update
sudo apt install -y mariadb-server

# By default MariaDB grabs port 3306. To run alongside MySQL,
# edit /etc/mysql/mariadb.conf.d/50-server.cnf:
#   [mysqld]
#   port            = 3307
#   socket          = /var/run/mysqld/mysqld-mariadb.sock
#   datadir         = /var/lib/mariadb
#   pid-file        = /var/run/mysqld/mysqld-mariadb.pid

sudo systemctl restart mariadb

# 3. Restore the dump into MariaDB on port 3307
mariadb -u root -p -h 127.0.0.1 -P 3307 < mysql-to-mariadb-*.sql

# 4. Validate (see the validation section below)

# 5. When ready to cut over: stop the app, take a final delta dump,
# restore it, then switch the connection string from MySQL:3306 to MariaDB:3307.

For a single-database migration (not all databases), the same flow with --databases mydb in step 1 and just that one schema in step 3.

JSON columns: the silent corruption risk

The single most-bitten case. MySQL stores JSON in an internal binary format that preserves key order, supports fast field extraction, and is queried with operators like ->> and JSON_EXTRACT(). MariaDB up to 10.7 stores JSON as plain LONGTEXT with a JSON_VALID() CHECK constraint. 10.7+ has a native JSON type but it's still a different on-disk representation than MySQL.

The dump-and-restore preserves the JSON text content. What it can't preserve:

• JSON object key order in MySQL is implementation-dependent; the canonical form is sorted by key. MariaDB preserves insertion order.
• JSON path indexes (the CREATE INDEX ... ((CAST(json_col->>'$.field' AS CHAR(255)) COLLATE utf8mb4_bin)) pattern) work in both but the planner uses them slightly differently.
• JSON_TABLE in MySQL works on 5.7.8+; in MariaDB on 10.6+.

After the restore, run every distinct JSON query the application uses against the MariaDB instance and compare results to MySQL. The cases that bite are subtle: a query that returns object keys in a particular order, or one that relies on JSON_QUOTE / JSON_UNQUOTE semantics for embedded quotes.

If your application uses MySQL-style JSON heavily and you can't easily test every path, this alone may be the reason to stay on MySQL.

Authentication plugin mismatch

After the dump-restore, the mysql.user table in MariaDB has rows with caching_sha2_password plugin entries (from MySQL 8.0+) that MariaDB can't use. Fix during the restore:

-- Convert all users to mysql_native_password (MariaDB's default).
-- WARNING: this resets the password hash. Re-grant with known passwords.
UPDATE mysql.global_priv
SET priv = JSON_REPLACE(priv,
    '$.plugin', 'mysql_native_password',
    '$.authentication_string', '*<known_hash>')
WHERE plugin = 'caching_sha2_password';
FLUSH PRIVILEGES;

In practice this means recreating users with their original cleartext passwords (which you should have in a secrets manager). Anything else has password-hash compatibility problems because caching_sha2_password and mysql_native_password use different hash formats. The safer path: write a one-time user-recreation SQL script as part of the migration plan.

For resetting users that you don't have the password for, the reset procedure is the same on MariaDB.

Replication between MySQL and MariaDB

For very large databases, the cleanest migration is replication: MariaDB as a replica of MySQL, then promote.

Officially, replication from MySQL to MariaDB is supported between adjacent versions only (MySQL 5.7 → MariaDB 10.3 was the last clean pair). For MySQL 8.0 → MariaDB 10.5+, it works but is not formally tested; the binary log formats are close enough that the basic row-based replication flows.

GTID compatibility is broken: MySQL GTIDs and MariaDB GTIDs are entirely different. Run replication with gtid_mode=OFF on the MySQL side and use traditional binary-log-position-based replication.

For mission-critical migrations this path is more involved than the dump-and-restore. It's mainly useful when the database is too large to dump in your maintenance window (multi-terabyte InnoDB).

Validation queries

After restore, run before cutting traffic:

-- Row counts per table, compare to a corresponding query against MySQL
SELECT table_schema, table_name, table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
ORDER BY table_schema, table_name;

-- Foreign-key references
SELECT constraint_schema, constraint_name, table_name, referenced_table_name
FROM information_schema.referential_constraints
ORDER BY constraint_schema, table_name;

-- Storage engines
SELECT engine, COUNT(*) FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
GROUP BY engine;

-- Character sets and collations (check utf8mb4 carried over)
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('mysql','sys','information_schema','performance_schema');

-- Stored routines
SELECT routine_schema, routine_name, routine_type
FROM information_schema.routines
ORDER BY routine_schema, routine_name;

table_rows from information_schema is an estimate for InnoDB. For exact counts, run SELECT COUNT(*) FROM tbl against your top tables on both sides and diff the results.

What to do next

After the cutover:

• How to Reset a Forgotten MySQL Root Password — same procedure on MariaDB.
• How to Export All MySQL Databases with mysqldump — same tool, same flags.
• MySQL Cheat Sheet — the syntax is overwhelmingly the same.
• utf8 to utf8mb4 Migration in MySQL — if you haven't done it, the same procedure applies on MariaDB.

External references:

• MariaDB Knowledge Base: Migrating from MySQL
• MariaDB KB: Compatibility differences
• MariaDB system-versioned tables

FAQ