Why MySQL ↔ MariaDB Migrations Fail — And How to Make Yours a Success

Migrating between MySQL and MariaDB used to be described as a “drop-in replacement.”
But any DBA who has managed real production workloads knows that this is no longer true—especially with the modern community versions MySQL 8.4 LTS and MariaDB 11.4 LTS.

It’s a common scenario: your application is hitting a performance bottleneck, scalability issue, or licensing restriction, and you decide to migrate from MySQL to MariaDB or vice-versa. Although these systems share a common origin, they have evolved into significantly different database engines over the past decade.

Their optimizer behavior, replication protocols, authentication systems, SQL syntax, system tables, and storage engines have diverged. A migration performed without addressing these differences can lead to:

• Application errors
• Authentication failures
• DDL crashes
• Replication breakage
• Slowdowns due to the optimizer choosing different plans
• Unexpected downtime

This blog walks through the real-world incompatibilities you must address before planning a MySQL ↔ MariaDB migration—along with clear, actionable solutions

Authentication & Security Defaults

This is the most common cause of immediate application failure after migration.

MySQL’s Modern Authentication Default

MySQL 8.4 uses caching_sha2_password as the default authentication plugin.

MariaDB 11.4 still uses mysql_native_password as its default, unless the optional ed25519 plugin is explicitly installed.

What breaks?

• MySQL → MariaDB:
  MariaDB does not support MySQL’s caching_sha2_password. Applications will fail to connect unless user accounts are converted.
• MariaDB → MySQL:
  Users migrated directly from MariaDB system tables may not map correctly into MySQL’s mysql.user schema.

How to Fix It (Before Migration)

Check user plugin:

SELECT user, host, plugin FROM mysql.user WHERE user = 'app_user';

If it shows caching_sha2_password, convert it:

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
FLUSH PRIVILEGES;

This command reverts the specified user to the older, but universally accepted, password hashing plugin.

Data Dictionary & System Table Incompatibilities

MySQL 8.0+ uses a unified, transactional InnoDB data dictionary.
MariaDB 11.4 still uses file-based metadata (.frm, .ibd) with its own internal structure.

These are fundamentally incompatible.

What breaks?

• You cannot copy MySQL’s datadir into MariaDB or vice-versa
• .frm, .ibd, and dictionary structures cannot be shared
• MySQL’s Clone Plugin cannot be used for MariaDB
• A direct upgrade path from MariaDB → MySQL is not supported

Solution: Use Logical Backup Only

Always use a logical export/import process for migration. This ensures the schema is parsed and recreated correctly on the destination database’s native dictionary structure.

Example:

mysqldump --single-transaction --routines --triggers --events dbname > dump.sql

• Never migrate system tables.
• Do not dump the mysql.user table. Recreate users manually on the destination server (MariaDB or MySQL) to avoid internal table structure conflicts.

JSON Data Type and Function Divergence

While both databases support JSON, they handle it in fundamentally different ways.

The Problem: Native Binary vs. Text Alias

• MySQL to MariaDB: MySQL’s JSON type is a native binary object optimized for quick querying and modification. MariaDB treats the JSON data type as an alias for LONGTEXT. This means MariaDB lacks MySQL’s efficient, native binary storage format.
• Divergent Functions: Many specialized JSON functions have different syntax and simply do not exist on the other platform.

The Solution: Convert or Rewrite

1. The Fix (MySQL to MariaDB):Before migrating, explicitly change any JSON column types in your MySQL database to LONGTEXT in the schema dump.
   • Verify that your application does not rely on the performance benefits of MySQL’s native JSON indexing. If it does, you might need a different architectural approach in MariaDB.
2. Function Review (Both Directions):Scan your stored procedures, views, and application code for specific functions like JSON_EXTRACT, JSON_SET, and JSON_REPLACE.
   • If you use the dot notation (->) for extracting JSON fields in MySQL, ensure it is properly adapted or rewritten for the target database’s supported syntax.

Replication and High Availability Solutions

If you rely on synchronous or asynchronous replication, the solutions are completely incompatible at the cluster level.

The Problem: Different Clustering Technologies

• MySQL to MariaDB: MySQL Group Replication (MGR) is MySQL’s native synchronous cluster solution. It is entirely incompatible with MariaDB Galera Cluster (the MariaDB synchronous HA solution).
• MariaDB to MySQL: Similarly, a MariaDB Galera node cannot talk to an MGR node

Feature	MySQL 8.4	MariaDB 11.4
GTID format	uuid:transaction	domain-id:server-id:seqno
Group Replication	Yes	No
Galera Cluster	No	Yes
Binlog events	MySQL-proprietary	MariaDB-specific formats

The Solution: Rebuild and Reconfigure

1. You cannot simply point one cluster technology to the other.
2. Use asynchronous Binlog Replication for the initial data transfer. Once the target database (MySQL with MGR, or MariaDB with Galera) is fully synced, stop replication and rebuild the entire HA cluster using the new database’s native tools and protocols.

Storage Engine Differences

While InnoDB (or its MariaDB counterpart) is the default and standard for transactional tables, be aware of proprietary and specific engines.

Migration Impact

• MariaDB → MySQL: MariaDB includes proprietary engines like Aria (a robust replacement for MyISAM),MyRocks, Spider, and ColumnStore (for analytics). These engines do not exist in MySQL.tables must be converted
• MySQL → MariaDB: InnoDB is compatible, some specialized MySQL features or third-party engines might not be ported to MariaDB.

The Solution: Standardize to InnoDB

Identify Non-InnoDB Tables: Run the following query on your source database:

SELECT table_schema, table_name, engine FROM information_schema.tables WHERE engine NOT IN ('InnoDB', 'NULL');

Convert all tables using non-standard engines (especially Aria) to InnoDB before migrating the schema and data.

ALTER TABLE table_name ENGINE=InnoDB;

Crucial Note: Ensure you test performance thoroughly after conversion, as the characteristics of InnoDB differ significantly from Aria/MyISAM.

SQL Syntax & Feature Differences

Beyond JSON, several critical SQL syntax elements and features have diverged, leading to silent breakage in stored code.

The Problem: Window Functions, System Variables, and DEFAULT Values

• Window Functions: MySQL 8.0 made Window Functions standard and robust, while MariaDB has its own implementation. Syntax might differ for complex analytical queries.
• The DEFAULT keyword for BLOB/TEXT: MySQL 8.0 lifted the long-standing restriction on using DEFAULT values for BLOB/TEXT columns. MariaDB 10.x/11.x generally still enforces this restriction.
• Common Table Expressions (CTEs): While both support standard CTEs (via WITH clause), MariaDB also supports recursive CTEs with specific syntax that may need verification when migrating to MySQL.

The Solution: Pre-emptive Schema Validation

1. The Fix (Migration Code Review): Before migrating, run a tool like pt-online-schema-change (Percona Toolkit) or a staging environment test to validate the DDL.
2. Action on DEFAULT: If migrating from MySQL 8.0, check your schema for TEXT/BLOB columns with DEFAULT values. You must remove the DEFAULT clause from these columns before importing the schema into MariaDB.

-- Example of DDL fix required when moving from MySQL to MariaDB

ALTER TABLE my_table MODIFY column_text TEXT NOT NULL;

This command removes the DEFAULT clause and only specifies the column data type and nullability, addressing the MariaDB restriction.

Performance & Optimizer Differences

The fundamental performance behavior of your application can change dramatically, even if the SQL syntax is correct, because the Query Optimizer works differently.

The Problem: Cost Models and Engine Behavior

• Optimizer Cost Models: The MariaDB Optimizer and the MySQL Optimizer (based on the same query planner structure) use different cost models, which can result in different execution plans for the exact same query.
• Virtual Columns: MariaDB’s Virtual Columns can be used as a primary key or foreign key, offering performance gains. If you migrate from MariaDB, MySQL might not use these columns as efficiently, changing performance profiles.

The Solution: Execution Plan Testing

1. Action: Identify your top 10 slowest queries and run them through EXPLAIN ANALYZE (or just EXPLAIN) on both the source and target databases.
2. Strategy: If a crucial query has a drastically different (and worse) execution plan on the target database, you must use optimizer hints (e.g., USE INDEX, FORCE INDEX) to guide the optimizer to the better plan. This ensures predictable performance after the switch.

Migration Strategy: Safe Approach for Both Directions

A successful migration isn’t about running one command; it’s about a staged process that minimizes downtime and risk.

The Safe DBA Migration Flow (Logical Dump Method)

1. Preparation & Audit:
   • Fix Users: Ensure all application users use mysql_native_password (if moving away from MySQL 8.0 default).
   • Fix Schema: Address JSON and BLOB/TEXT DEFAULT restrictions.
   • Standardize Engines: Convert all non-standard engines (like Aria) to InnoDB.
2. Dump Data (The Logical Approach):
   • Use mysqldump (or mariadb-dump): This creates a logical SQL file containing the CREATE TABLE and INSERT statements.
   • Crucial Flag: Use --set-gtid-purged=OFF and do not dump system databases (mysql, information_schema, performance_schema).
3. Restore Schema (Destination):
   • Import the schema part of the logical dump onto the destination database. Manually create system users and permissions first.
4. Establish Replication (Zero Downtime):
   • Set up asynchronous replication (primary-replica) between the source (now primary) and the destination (now acting as a replica). This allows the destination to catch up to the live traffic.
5. Cutover & Testing:
   • Once the destination is fully synced, put the application into maintenance mode (writes are paused).
   • Wait for replication lag to reach zero.
   • Switch the application connection strings to the new database.
   • Take the application out of maintenance mode.
   • Monitor performance and logs closely for 24-48 hours.

Conclusion

MySQL and MariaDB are both powerful open-source databases—but they are no longer close twins. They are more like cousins evolving in their own ways. That means migration requires planning, not assumptions.

Migrating between MySQL and MariaDB is manageable, but only with a deep, expert understanding of where they diverge. Don’t let a minor incompatibility lead to a major production issue.

If you’re preparing for a MySQL ↔ MariaDB migration, our open-source DBA team can guide you through a zero-downtime, production-safe transition.
Reach out for a migration readiness assessment!