SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsmariadb-impl-migration-mysql-to-mariadb

mariadb-impl-migration-mysql-to-mariadb

Deployment & operations

View repository

Updated 10-15 22:18

About this skill

MariaDB : Migration from MySQL to MariaDB

Deterministic guide for in-place upgrades and dump-and-restore migrations from MySQL 5.6 / 5.7 / 8.0 / 8.4 to MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x. Covers the compatibility matrix, the four hard divergences (JSON, GTID, auth-plug-in, INVISIBLE vs IGNORED INDEX), the privilege-store migration (mysql.user to mysql.global_priv), and the mandatory mariadb-upgrade workflow.

Quick Reference

  • MariaDB JSON is a LONGTEXT alias, NOT binary like MySQL 5.7.8+. After migration, ALWAYS add CHECK (JSON_VALID(col)) to every JSON column to preserve the MySQL validation invariant. Without it, a subsequent non-JSON UPDATE corrupts the row silently. (D-010, L-005)
  • MariaDB GTID format is domain-server-sequence. MySQL GTID is uuid:seqno. They are INCOMPATIBLE. MySQL to MariaDB is a one-way dump-and-load, NOT a continuous GTID-tracked replication. (L-004)
  • MySQL 8 default auth plug-in is caching_sha2_password. MariaDB does NOT ship it. ALWAYS re-create users with ed25519 or mysql_native_password before pointing apps at MariaDB.
  • MariaDB uses ALTER TABLE t ALTER INDEX idx IGNORED. MySQL 8 uses INVISIBLE. Copying MySQL 8 syntax produces a syntax error. (L-006)
  • ALWAYS run mariadb-upgrade exactly once after the binary swap. It migrates mysql.user to mysql.global_priv (10.4+) and runs CHECK TABLE / ALTER TABLE on format-drift. NEVER run it twice in a row : the second pass logs spurious warnings that mask real first-pass errors. (Vooronderzoek §6)
  • NEVER copy a MySQL my.cnf verbatim. sql_mode defaults differ, query_cache_* was REMOVED in MariaDB 11.0+, default_authentication_plugin is unsupported, and several InnoDB variables have different default scaling.
  • MySQL 5.6 is a near drop-in replacement for MariaDB 10.x (in-place upgrade works). MySQL 5.7+ requires JSON-validation remediation. MySQL 8.0+ requires user re-creation. MySQL 8.4 additionally removes the query cache (MariaDB also removed it in 11.0+).
  • MariaDB has CREATE SEQUENCE + NEXT VALUE FOR (10.3+) that MySQL lacks. Migrating FROM MySQL : keep AUTO_INCREMENT as-is, NEVER auto-convert to sequences during migration. Convert deliberately after stability.
  • Binary names changed in 10.5+ : mysqldump is now mariadb-dump, mysqlbinlog is mariadb-binlog. Old names remain as compatibility symlinks today, but cron jobs that hard-code mysqldump SHOULD be updated.
  • Verify privileges after migration via SELECT user, host, plugin FROM mysql.global_priv; (10.4+), NOT only mysql.user. The mysql.user view is still queryable as a backward-compat layer but does not reflect role grants natively.

Decision Tree : Which Migration Path

START
 |
 +-- Source is MySQL 5.5 or 5.6 ?
 |       --> in-place upgrade : stop mysqld, install mariadb-server on same datadir, start mariadbd, run mariadb-upgrade.
 |       --> validate apps, then sweep JSON columns to add CHECK (JSON_VALID(col)) where appropriate.
 |
 +-- Source is MySQL 5.7 ?
 |       --> in-place upgrade is supported but JSON columns need explicit re-validation (LONGTEXT alias does NOT enforce JSON).
 |       --> after mariadb-upgrade, add CHECK (JSON_VALID(col)) on every JSON column.
 |       --> re-test app for any sha256_password users (rare in 5.7).
 |
 +-- Source is MySQL 8.0 or 8.4 ?
 |       --> dump-and-restore is the SAFE default. In-place is technically possible but high-risk due to data-dictionary differences.
 |       --> mysqldump --single-transaction --routines --triggers --events --add-drop-database from MySQL 8.
 |       --> stop MySQL, install MariaDB on a clean datadir, restore the dump.
 |       --> drop all caching_sha2_password users, re-create with ed25519 or mysql_native_password.
 |       --> add CHECK (JSON_VALID(col)) on every JSON column.
 |       --> if app uses INVISIBLE INDEX : rewrite to IGNORED INDEX.
 |       --> if app uses ALTER USER ... DEFAULT ROLE : rewrite to MariaDB SET DEFAULT ROLE syntax.
 |       --> run mariadb-upgrade once to finalise system tables.
 |
 +-- Need continuous replication during the cut-over window ?
 |       --> NEVER assume MySQL-GTID continuity (incompatible). Use binlog-positional replication MariaDB-reads-from-MySQL during the window only.
 |       --> CHANGE MASTER TO MASTER_HOST='mysql-source', MASTER_LOG_FILE='...', MASTER_LOG_POS='...', MASTER_USE_GTID=NO.
 |       --> at cut-over, stop apps, wait for replica lag = 0, STOP SLAVE, repoint apps to MariaDB. Then run mariadb-upgrade.
 |
 +-- Need to keep MySQL running as a fallback for N days ?
 |       --> set up MariaDB-replica-of-MySQL as above, but do NOT add a reverse MariaDB-to-MySQL path : MySQL cannot replicate FROM MariaDB.
 |       --> the rollback plan is "repoint apps back to MySQL" without re-syncing, accepting data loss for the window.
END

Compatibility Matrix : MySQL Version vs Required Remediation

Area MySQL 5.6 MySQL 5.7 MySQL 8.0 MySQL 8.4
In-place upgrade viable yes (best path) yes risky risky
Dump-and-restore viable yes yes yes (recommended) yes (recommended)
JSON re-validation needed not applicable (no native JSON pre-5.7.8) yes (LONGTEXT alias) yes yes
Default auth plug-in needs migration no rare (sha256_password) yes (caching_sha2_password) yes
INVISIBLE INDEX rename not applicable not applicable yes (to IGNORED) yes
Role syntax adaptation not applicable not applicable yes (DEFAULT ROLE) yes
sql_mode review needed yes (defaults differ) yes yes yes
Query cache removal no no no n/a (MariaDB 11.0+ has no cache)

The Four Hard Divergences

1. JSON Storage (D-010, L-005)

MariaDB JSON is a LONGTEXT alias with an optional CHECK (JSON_VALID(col)) hook. MySQL 5.7.8+ stores JSON in a packed binary format that validates on every write. After migration, raw INSERT or UPDATE on a JSON column accepts non-JSON text silently. ALWAYS add the CHECK :

-- After dump-restore, on every JSON column
-- 10.6+ / 10.11+ / 11.x / 12.x
ALTER TABLE orders
  ADD CONSTRAINT orders_payload_json CHECK (JSON_VALID(payload));

To index a JSON path, MariaDB requires a virtual column + index (NO functional indexes on JSON expressions directly) :

-- 10.6+ / 10.11+ / 11.x / 12.x
ALTER TABLE orders
  ADD COLUMN buyer_id VARCHAR(36)
    AS (JSON_VALUE(payload, '$.buyer_id')) VIRTUAL,
  ADD INDEX idx_orders_buyer (buyer_id);

2. GTID Format (L-004)

MariaDB : domain-server-sequence, e.g. 0-1-1234. MySQL : uuid:seqno, e.g. 3e11fa47-71ca-11e1-9e33-c80aa9429562:23. They are NOT interconvertible. During a cut-over, MariaDB can replicate FROM MySQL using BINLOG POSITION only (MASTER_USE_GTID=NO), and MySQL CANNOT replicate from MariaDB at all. Plan one-shot dump-and-load for two-way scenarios.

3. Authentication Plug-in

MySQL 8 default caching_sha2_password is not in MariaDB. MariaDB native options are mysql_native_password (compatible), ed25519 (recommended, OpenSSH-grade), and unix_socket (local-only). After migration, scan and re-create :

-- 10.6+ / 10.11+ / 11.x / 12.x
INSTALL SONAME 'auth_ed25519';     -- one-off; or set plugin_load_add=auth_ed25519 in my.cnf
CREATE OR REPLACE USER 'app'@'%'
  IDENTIFIED VIA ed25519 USING PASSWORD('new-strong-secret');
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

4. INVISIBLE vs IGNORED INDEX (L-006)

-- WRONG (MySQL 8 syntax, fails in MariaDB)
ALTER TABLE t ALTER INDEX idx INVISIBLE;

-- RIGHT (MariaDB 10.6+)
ALTER TABLE t ALTER INDEX idx IGNORED;

Step-by-Step : In-Place Upgrade (MySQL 5.6 / 5.7 to MariaDB)

  1. Verify backup is fresh and restorable. mariabackup --backup --target-dir=/backup/pre-migration --user=root (or equivalent mysqldump --single-transaction --routines).
  2. Stop the MySQL service : systemctl stop mysqld.
  3. Remove MySQL packages WITHOUT removing the datadir : apt-get remove --purge mysql-server mysql-server-core-* or yum remove mysql-server.
  4. Install MariaDB pointing at the SAME datadir : apt-get install mariadb-server (Debian/Ubuntu) or yum install MariaDB-server (RHEL family).
  5. Start MariaDB : systemctl start mariadb.
  6. Run mariadb-upgrade exactly ONCE : mariadb-upgrade -u root -p. This migrates system tables, including mysql.user to mysql.global_priv on 10.4+.
  7. Validate apps. Run smoke tests.
  8. Sweep JSON columns and add CHECK (JSON_VALID(col)) where appropriate (see Divergence 1).
  9. If users on sha256_password (rare in 5.7) : re-create as ed25519 or mysql_native_password.

Step-by-Step : Dump-and-Restore (MySQL 8.0 / 8.4 to MariaDB)

  1. On MySQL : mysqldump --single-transaction --routines --triggers --events --add-drop-database --databases db1 db2 > dump.sql.
  2. Provision a clean MariaDB datadir. Install MariaDB server, start it.
  3. On MariaDB : restore the dump : mariadb -u root -p < dump.sql. Expect occasional warnings on caching_sha2_password user statements ; ignore and re-create users in step 6.
  4. Run mariadb-upgrade -u root -p once.
  5. Sweep JSON columns and add CHECK (JSON_VALID(col)).
  6. Drop and re-create all app users with ed25519 (or mysql_native_password if drivers are old). See Divergence 3.
  7. Search dumped DDL / app code for INVISIBLE INDEX and rewrite to IGNORED INDEX. See Divergence 4.
  8. Search dumped DDL / app code for ALTER USER ... DEFAULT ROLE r1, r2 and rewrite to MariaDB : SET DEFAULT ROLE r1 FOR 'user'@'host' (one role per statement).
  9. Review my.cnf : remove default_authentication_plugin, remove query_cache_* if targeting 11.0+, re-review sql_mode.
  10. Repoint apps. Monitor SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS during ramp-up.

Cross-References

  • Schema-design with JSON CHECK constraints : mariadb-impl-schema-design
  • JSON syntax patterns : mariadb-syntax-json
  • Replication-positional bridging during cut-over : mariadb-impl-replication-setup
  • Index syntax including IGNORED : mariadb-syntax-indexing
  • Encoding and collation pitfalls discovered post-migration : mariadb-errors-encoding-and-collation
  • User and privilege model post-10.4+ : mariadb-core-security-model

References

  • references/methods.md : full compatibility matrix per area, mariadb-upgrade flag reference, user-migration script template, JSON re-validation script template
  • references/examples.md : 10+ end-to-end migration scripts (in-place 5.7, dump-and-restore 8.0, JSON CHECK sweep, ed25519 re-creation, GTID-positional bridge, INVISIBLE rename, sequence introduction post-stability, one-shot MariaDB-to-MySQL export)
  • references/anti-patterns.md : 8 documented anti-patterns with why-it-fails and the correct alternative

Source Verification

  • https://mariadb.com/kb/en/upgrading-from-mysql-to-mariadb/ : in-place upgrade workflow, drop-in compatibility statement for MySQL 5.5 / 5.6
  • https://mariadb.com/docs/release-notes/community-server/about/compatibility-and-differences/mariadb-vs-mysql-compatibility : JSON storage difference, GTID interop limit, system table differences
  • https://mariadb.com/docs/release-notes/community-server/about/compatibility-and-differences/mariadb-vs-mysql-features : MariaDB-only features (SEQUENCE 10.3+, virtual columns 5.2+, system-versioned tables 10.3+, dynamic columns 5.3+)
  • https://mariadb.com/docs/server/clients-and-utilities/deployment-tools/mariadb-upgrade : mariadb-upgrade purpose, flags (--force, --upgrade-system-tables, --check-if-upgrade-is-needed, --version-check, --write-binlog), when to run
  • https://mariadb.com/kb/en/authentication-plugin-ed25519/ : ed25519 plug-in INSTALL SONAME, CREATE USER VIA ed25519 USING PASSWORD syntax
  • Vooronderzoek docs/research/vooronderzoek-mariadb.md §6 (Migration MySQL to MariaDB) and §1 / §3 cross-cuts
  • LESSONS L-004 (GTID incompatible), L-005 (JSON LONGTEXT alias), L-006 (IGNORED vs INVISIBLE), L-001 (KB path redirects), L-007 (per-statement version verification)
  • DECISIONS D-010 (LONGTEXT-alias must appear in Quick Reference of every JSON-touching skill)