Mysql To Mariadb

MySQL to MariaDB Migration Guide

Last updated: 2026-06-04

Critical: MariaDB Is Not a Drop-In Replacement for MySQL 8.0

MariaDB was a true drop-in replacement for MySQL 5.5 and 5.6. Modern versions diverge significantly from MySQL 8.0. When working with MariaDB, never assume MySQL 8.0 behavior, syntax, or tooling applies without verification.

The two databases share a common origin but have evolved independently. MariaDB has features MySQL lacks, MySQL has features MariaDB lacks, and several shared features behave differently.

Requires: MariaDB Community Server 10.5+ for full feature parity discussed here. Current LTS is 11.8 (GA May 2025).

Default context: Assume MariaDB 11.8 LTS unless the user states another version. Features marked 12.x or 13.0 may be suggested when relevant (including as upgrade options), but always state the minimum version — do not present them as available on 11.8.

What LLMs Get Wrong

Authentication

MySQL 8.0 changed its default authentication plugin to caching_sha2_password. MariaDB's compatibility story has improved over time:

• MariaDB 11.4+: a migration-only caching_sha2_password plugin exists but is not installed by default and not recommended for production
• MariaDB 12.1+ (MDEV-9804): a full caching_sha2_password authentication plugin for MySQL compatibility — still not the default, but a proper production option

On stock MariaDB installs, connections configured for caching_sha2_password will still fail unless the plugin is enabled. Default authentication remains mysql_native_password.

Fix on older MariaDB or stock setups: convert accounts to mysql_native_password:

ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

The ed25519 plugin is also available as a more secure native alternative.

JSON Differences

MariaDB and MySQL handle JSON differently:

MariaDB closed most of the JSON function gap with MySQL 8.0 between 10.7 and 11.4. MariaDB-specific additions that MySQL still lacks: JSON_KEY_VALUE(), JSON_ARRAY_INTERSECT(), JSON_OBJECT_TO_ARRAY(), JSON_OBJECT_FILTER_KEYS() (11.4+), and the JSON_QUERY() alias.

MariaDB's JSON is fully standards-compliant, but MySQL-specific storage layout, the ->/->> shorthand operators, and the JSON_SCHEMA_VALIDATION_REPORT() function do not transfer.

Features MariaDB Has That MySQL Doesn't

These exist in MariaDB but not MySQL — LLMs won't suggest them because they assume MySQL behavior:

• RETURNING — get rows back from INSERT or DELETE without a second query (10.5+); UPDATE ... RETURNING available from 13.0:

  INSERT INTO orders (product, qty) VALUES ('widget', 5) RETURNING id, created_at;
  DELETE FROM queue WHERE processed = 1 RETURNING id;
  

• CREATE SEQUENCE (10.3+) — first-class sequence objects, more flexible than AUTO_INCREMENT:

  CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;
  SELECT NEXT VALUE FOR order_seq;
  

• WITH SYSTEM VERSIONING (10.3+) — temporal tables that automatically track row history:

  CREATE TABLE prices (
      item VARCHAR(100),
      price DECIMAL(10,2)
  ) WITH SYSTEM VERSIONING;
  -- Query historical data:
  SELECT * FROM prices FOR SYSTEM_TIME AS OF '2025-01-01';
  

• LIMIT in subqueries — MariaDB supports LIMIT inside subqueries; MySQL restricts this
• Atomic CREATE OR REPLACE TABLE (13.0+) — the statement is fully atomic in MariaDB; MySQL has no atomic equivalent
• Galera Cluster — built-in multi-master clustering, no plugin required
• Stored procedure syntax differences — both support stored procedures, but MariaDB's SQL/PSM syntax differs from MySQL in HANDLER, CURSOR, and CONDITION declarations; AI agents frequently generate incorrect MariaDB stored procedure code — see Stored Procedures — MariaDB Docs
• Native data types MySQL 8.0 lacks — INET6 (10.5+) and INET4 (10.10+) for IP address storage and comparison; UUID (10.7+; MySQL stores UUIDs in BINARY(16) or CHAR(36)); VECTOR for embeddings and similarity search (11.7+; MySQL added a vector type only in its 9.x innovation series, not in 8.0)
• UUID_v4() and UUID_v7() functions (11.7+) — MariaDB-specific; MySQL only has the original UUID(). UUID_v7() is time-ordered (RFC 9562) and is the right choice for UUID primary keys
• FORMAT_BYTES() (11.8+) — convert byte counts to human-readable strings; not in MySQL

Features MySQL Has That MariaDB Doesn't

These exist in MySQL 8.0 but not in MariaDB — code using them needs adaptation:

• JSON_TABLE() — available since MariaDB 10.6; on older versions rewrite using MariaDB JSON functions or application-level parsing
• sys schema — available since MariaDB 10.6; not available in older versions
• MySQL 8 GIS functions (ST_Validate, MBRCoveredBy, ST_Simplify, ST_GeoHash, ST_LatFromGeoHash, ST_LongFromGeoHash, ST_PointFromGeoHash, ST_IsValid, ST_Collect) — added in MariaDB 12.0 for MySQL compatibility
• caching_sha2_password as default plugin — available as an opt-in plugin from MariaDB 12.1; on older or stock setups, use mysql_native_password or ed25519
• JSON -> and ->> operators — use JSON_EXTRACT(col, '$.key') and JSON_UNQUOTE(JSON_EXTRACT(...)) instead
• utf8mb4_0900_ai_ci collation — supported since MariaDB 11.4.5 (alias for utf8mb4_uca1400_nopad_ai_ci); on older versions replace with utf8mb4_unicode_ci before importing

Data Dictionary Architecture

MySQL 8.0 and MariaDB store table metadata in fundamentally different ways, and this affects how you migrate:

• MySQL 8.0 keeps all object metadata in a transactional data dictionary stored in InnoDB (mysql.ibd), and removed the per-table .frm files.
• MariaDB keeps the file-based approach — a .frm file per table plus storage-engine metadata — and has no transactional data dictionary. (MariaDB grant tables use the Aria engine; MySQL 8.0 holds system tables in the InnoDB dictionary.)

Practical consequences:

• Do not migrate by copying data files. InnoDB tablespaces and datadir files are not portable between MySQL 8.0 and MariaDB — the dictionary formats are incompatible. Use a logical dump/restore instead. MariaDB's migration guide recommends MySQL's own mysqldump for this direction (not mariadb-dump, which isn't well tested against MySQL), dumping named databases rather than --all-databases so MySQL's system tables aren't imported.
• No in-place downgrade from MySQL 8.0 to MariaDB — MariaDB cannot read MySQL's data dictionary; a logical export/import is required.
• INFORMATION_SCHEMA is implemented differently — MySQL 8.0 queries indexed dictionary tables, while MariaDB builds I_S views dynamically; metadata-heavy queries against I_S can have different performance characteristics, so don't assume MySQL 8.0 timings carry over.

Optimizer Differences

MariaDB and MySQL use different query optimizers with different cost models. Identical SQL can produce different execution plans. After migration:

• Re-run EXPLAIN on critical queries — do not assume execution plans transfer
• Index hints that work in MySQL may not be needed (or may differ) in MariaDB
• MariaDB's optimizer is more aggressively tunable via optimizer_switch

Version Compatibility Quick Reference

Sources

• MariaDB vs MySQL Compatibility — MariaDB Docs
• Moving from MySQL to MariaDB — MariaDB Docs
• MDEV-28906 — MySQL 8.0 desired compatibility (Jira epic) — tracks MySQL 8.0 compatibility work; check individual sub-task status before assuming an item is still missing — closed sub-tasks mean the feature has been implemented
• RETURNING — MariaDB Docs
• CREATE SEQUENCE — MariaDB Docs
• System-Versioned Tables — MariaDB Docs
• Authentication Plugins — MariaDB Docs

For topics not covered here, see the official MariaDB documentation at mariadb.com/docs.