MySQL to MariaDB Migration: The Master Guide

Mastering the MySQL to MariaDB transition? This guide covers migrations from MySQL 5.7, 8.0, and 8.4 LTS to MariaDB 11.8/12.3, featuring a full Compatibility Matrix and safe dump/restore workflows.

Environment Scope

This guide focuses on migrations within Linux-based environments (RHEL/CentOS/Alma, Debian/Ubuntu, etc.), as these represent the vast majority of production MySQL and MariaDB deployments.

Preparation

Pre-Migration Checklist

Before touching the production server, verify these requirements:

• Version Target: Identify your destination.

• Coming from MySQL 5.7? MariaDB 10.11 LTS or 11.8 LTS are safe harbors with the longest remaining support runways.

• Coming from MySQL 8.0/8.4? MariaDB 11.8 LTS or the new 12.3 LTS are recommended. These versions have the most modern feature parity required for 8.x-era applications.

• Compatibility Check: Review the MySQL to MariaDB Compatibility Matrix for potential high-impact differences in authentication and SQL syntax.

Mandatory Backup

Never start a migration without two types of backups:

1. Physical/Binary Backup: A copy of the /var/lib/mysql directory (while the server is stopped). This allows for the fastest "undo" if the binary swap fails.

2. Logical Backup: A full dump of your data. Use the MySQL mysqldump program, not MariaDB's mariadb-dump, because the latter hasn't been extensively tested against MySQL. Don't dump --all-databases, because that would include the MySQL system databases, which are different from the MariaDB ones – loading them would fail on data import. Instead, specify which databases to export as arguments to --databases.

   # The Universal Master Dump Command
   mysqldump --user=root --password --databases db1 db2 ... \
   --single-transaction --routines --events \
   --triggers --hex-blob > migration_dump.sql

Porting Users and Privileges

Standard data dumps often fail to capture complex user permissions correctly when moving from MySQL 8.0+. Instead of migrating the entire mysql system database, use these scripts on your MySQL source server to generate the exact SQL commands needed to recreate your users and their permissions on MariaDB.

Creating Users on MariaDB

The following SQL can be used to generate CREATE USER statements with a default password that can be executed on MariaDB. It does the following:

• Authentication method mysql_native_password – the script supports passwords porting.

• Authentication method caching_sha2_password or authentication string is NULL – passwords are reset to default with expiry.

The PARSEC authentication plugin is intended to be the default in a future release, hence it is recommended to use this during user migration.

SELECT
    CASE
        WHEN plugin = 'mysql_native_password'
             AND authentication_string IS NOT NULL
             AND authentication_string <> ''
        THEN CONCAT(
            'CREATE USER IF NOT EXISTS ''', user, '''@''', host,
            ''' IDENTIFIED VIA mysql_native_password USING ''',
            authentication_string, ''';'
        )

        WHEN plugin = 'mysql_native_password'
             AND (authentication_string IS NULL OR authentication_string = '')
        THEN CONCAT(
            'CREATE USER IF NOT EXISTS ''', user, '''@''', host,
            ''' IDENTIFIED BY ''Welcome@123'' PASSWORD EXPIRE; ',
            '-- no source hash available'
        )

        WHEN plugin IN ('caching_sha2_password', 'sha256_password')
        THEN CONCAT(
            'CREATE USER IF NOT EXISTS ''', user, '''@''', host,
            ''' IDENTIFIED BY ''Welcome@123'' PASSWORD EXPIRE; ',
            '-- original plugin: ', plugin
        )

        ELSE CONCAT(
            '-- SKIPPED unsupported plugin for ''', user, '''@''', host,
            ''': ', COALESCE(plugin, 'NULL')
        )
    END AS migration_sql
FROM mysql.user
WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema','root');

Copying User Privileges to MariaDB

Use the following SQL to generate user privilege statements for execution in MariaDB from MySQL.

Privileges covered are:

• Global privileges

• Database privileges

• Table privileges

• Procedure privileges

The SQL may vary depending on the version of MySQL you're coming from.

SET SESSION group_concat_max_len = 1000000;

SELECT '-- GLOBAL PRIVILEGES' AS '';

SELECT CONCAT(
'GRANT ',
TRIM(TRAILING ',' FROM CONCAT(
IF(Select_priv='Y','SELECT,',''),
IF(Insert_priv='Y','INSERT,',''),
IF(Update_priv='Y','UPDATE,',''),
IF(Delete_priv='Y','DELETE,',''),
IF(Create_priv='Y','CREATE,',''),
IF(Drop_priv='Y','DROP,',''),
IF(Grant_priv='Y','GRANT OPTION,',''),
IF(References_priv='Y','REFERENCES,',''),
IF(Index_priv='Y','INDEX,',''),
IF(Alter_priv='Y','ALTER,',''),
IF(Create_view_priv='Y','CREATE VIEW,',''),
IF(Show_view_priv='Y','SHOW VIEW,',''),
IF(Create_routine_priv='Y','CREATE ROUTINE,',''),
IF(Alter_routine_priv='Y','ALTER ROUTINE,',''),
IF(Execute_priv='Y','EXECUTE,',''),
IF(Event_priv='Y','EVENT,',''),
IF(Trigger_priv='Y','TRIGGER,','')
)),
' ON *.* TO ''', user, '''@''', host, ''';'
)
FROM mysql.user
WHERE CONCAT(
Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv,Grant_priv,References_priv,
Index_priv,Alter_priv,Create_view_priv,Show_view_priv,
Create_routine_priv,Alter_routine_priv,Execute_priv,
Event_priv,Trigger_priv
) REGEXP 'Y';


SELECT '-- DATABASE PRIVILEGES' AS '';

SELECT CONCAT(
'GRANT ',
TRIM(TRAILING ',' FROM CONCAT(
IF(Select_priv='Y','SELECT,',''),
IF(Insert_priv='Y','INSERT,',''),
IF(Update_priv='Y','UPDATE,',''),
IF(Delete_priv='Y','DELETE,',''),
IF(Create_priv='Y','CREATE,',''),
IF(Drop_priv='Y','DROP,',''),
IF(Grant_priv='Y','GRANT OPTION,',''),
IF(References_priv='Y','REFERENCES,',''),
IF(Index_priv='Y','INDEX,',''),
IF(Alter_priv='Y','ALTER,',''),
IF(Create_view_priv='Y','CREATE VIEW,',''),
IF(Show_view_priv='Y','SHOW VIEW,',''),
IF(Create_routine_priv='Y','CREATE ROUTINE,',''),
IF(Alter_routine_priv='Y','ALTER ROUTINE,',''),
IF(Execute_priv='Y','EXECUTE,',''),
IF(Event_priv='Y','EVENT,',''),
IF(Trigger_priv='Y','TRIGGER,','')
)),
' ON `', db, '`.* TO ''', user, '''@''', host, ''';'
)
FROM mysql.db;

SELECT '-- TABLE PRIVILEGES' AS '';

SELECT CONCAT(
'GRANT ', Table_priv,
' ON `', Db, '`.`', Table_name,
'` TO ''', User, '''@''', Host, ''';'
)
FROM mysql.tables_priv;

SELECT '-- PROCEDURE PRIVILEGES' AS '';

SELECT CONCAT(
'GRANT ', Proc_priv,
' ON PROCEDURE `', Db, '`.`', Routine_name,
'` TO ''', User, '''@''', Host, ''';'
)
FROM mysql.procs_priv;

Proactive SQL Validation

If your application is mission-critical:

• Enable the General Query Log on your MySQL server for a period of time to capture real-world traffic.

• Replay those queries against a MariaDB test instance.

• Check the MariaDB error log for any "Syntax Error" or "Unknown Function" messages. This prevents surprises on migration night.

Step by Step Migration Procedure

Logical migration (dump and restore) is the gold standard for safety. It is the best choice if you are moving to a new server, a different Linux distribution, or a cloud-managed environment.

By exporting the data into an SQL script, you effectively "filter" out any binary-level incompatibilities between MySQL and MariaDB.

A logical migration involves exporting your data into a text-based SQL file (a "dump") and importing it into a fresh MariaDB instance ("restore"). This is the safest way to ensure data integrity, as it completely recreates your tables and indexes in the MariaDB format.

1

Export Data from MySQL

On your existing MySQL server, create a complete dump of all databases, including stored procedures, triggers, and events. (Note this is the same dump command used in the Preparation step, so you can use that one instead of re-creating it.)

# The Universal Master Dump Command
mysqldump --user=root --password --databases db1 db2 ... \
--single-transaction --routines --events \
--triggers --hex-blob > migration_dump.sql

• --single-transaction: Ensures a consistent backup without locking your tables (for InnoDB).

• --hex-blob: Properly handles binary data like images or encrypted strings.

• --routines and --events ensures that the "logic" of the database moves, not just the "rows".

2

Prepare the MariaDB Environment

On your new server, install MariaDB using the Installation Guide for MariaDB Enterprise Server or the Installation Guide for MariaDB Community Server.

Before importing, ensure your MariaDB configuration (my.cnf) has enough resources to handle a large import.

• max_allowed_packet: Set this to at least 64M or higher if you have large BLOB columns.

• innodb_buffer_pool_size: Temporarily increase this to 70-80% of available RAM to speed up index creation.

3

Import Data into MariaDB

Transfer your mysql_full_dump.sql file to the new server and run the import:

# Import the data
mariadb -u root -p < mysql_full_dump.sql

Alternative: Direct Network Streaming (The Pipe Method)

If you have limited disk space or a high-speed network connection between your servers, you can "stream" the data directly from the MySQL source to the MariaDB target. This avoids creating a large intermediate .sql file on your local disk.

Streaming can take a long time for large databases. If it is aborted for any reason (like network timeout), it usually cannot be resumed. This means you'd have to start again from the beginning.

mysqldump --user=root --password --databases db1 db2 ... \
--single-transaction --routines --events \
--triggers --hex-blob | mariadb --host=new_server_ip --user=root --password

Pro Tip: To monitor the progress of your transfer in real-time, install the pv (Pipe Viewer) utility and insert it into the command: mysqldump ... | pv | mariadb ...

Verification and Optimization

Once your data is moved (via either method), complete these three steps to ensure your new server is running at peak performance.

1

Rebuild Optimizer Statistics

MariaDB uses a sophisticated cost-based optimizer that may differ from MySQL’s. To ensure your queries use the most efficient execution plans, force a fresh analysis of all tables:

# Run ANALYZE TABLE on every table in every database to refresh statistics
mariadb-check -u root -p --analyze --all-databases

This makes the difference between a migration that "works" and a migration that "works fast".

2

Character Set & Collation Validation

Ensure your server and your applications agree on the character set and collation to use.

If you notice unexpected behavior or performance drops in specific queries, verify your collations:

-- Check for collation issues in your migrated database
SHOW GLOBAL VARIABLES LIKE 'collation%';
SHOW  VARIABLES LIKE 'collation%';
SELECT table_schema, table_name, column_name, collation_name FROM information_schema.columns WHERE collation_name IS NOT NULL and table_schema not in('information_schema','performance_schema','mysql','sys') ORDER BY table_schema, table_name;

Check if you have queries that may lead to a mismatch error when joining 2 columns with different collations. Alter your table or database to use the same collation across all your databases. See ALTER TABLE and ALTER DATABASE for details.

Why this matters

If a user has one table set to the old MySQL default and another table set to a MariaDB default, a JOIN between them might suddenly stop using indexes because of the "collation mismatch." The above snippet gives you the SQL command to find the problem immediately.

3

Check the Error Log

Check for any "Deprecated Variable" or "Ignored Option" warnings that might have appeared during startup.

sudo tail -f /var/log/mysql/error.log | grep -e 'Deprecated|Ignored'

4

Application Smoke Test

Verify that your application can connect. Pay special attention to character sets: Ensure your apps and the server agree on utf8mb4 or whatever character set you choose.

5

Security Hardening: Transition to PARSEC

For migrations to MariaDB 11.8 LTS and later, consider transitioning your users to the PARSEC authentication plugin. While mysql_native_password and caching_sha2_password are supported for compatibility, PARSEC is the modern successor designed for significantly stronger password hashing and better long-term security.

-- Example: Update a user to use PARSEC
ALTER USER 'app_user'@'localhost' IDENTIFIED VIA parsec USING PASSWORD('secure_password');

Troubleshooting & FAQ

Even with careful preparation, migrations can encounter specific hurdles. Here are the most common issues and how to resolve them.

Common Troubleshooting Scenarios

Issue	Likely Cause	Resolution
"Table 'mysql.user' doesn't exist"	Missing mariadb-upgrade step.	The system tables must be converted. Run sudo mariadb-upgrade -u root -p immediately after starting the service.
Replication fails with "relay log" errors.	GTID Incompatibility.	MariaDB cannot use MySQL GTIDs. You must switch to position-based replication (File and Position) to link the two systems.
Slow queries after migration.	Outdated optimizer statistics.	MariaDB's optimizer needs fresh data. Run ANALYZE TABLE on all large tables, or use mariadb-check --analyze --all-databases.

Frequently Asked Questions

Q: Can I go back to MySQL if the MariaDB migration fails? A: Yes, provided you took a physical backup (the /var/lib/mysql folder) before running mariadb-upgrade. Once mariadb-upgrade has modified the system tables, you cannot simply point MySQL binaries back at that data directory.

Q: Do I need to change my application's client libraries? A: Usually, no. MariaDB is protocol-compatible with MySQL. Your existing MySQL connectors (JDBC, PHP PDO, Python mysqlclient) will continue to work. However, for the best performance and features (like IAM authentication), switching to the official MariaDB Connectors is recommended.

Q: What about the JSON data type? A: Your JSON columns will be treated as LONGTEXT with a CHECK constraint. Your queries using JSON_EXTRACT() will continue to work exactly as they did in MySQL.

Q: Is MariaDB 11.8 compatible with MySQL 8.4? A: Yes, for standard SQL and data. However, MySQL 8.4 removed many "legacy" behaviors that MariaDB still supports.

Q: Can I perform an in-place upgrade (binary swap)? A: No. MariaDB stores metadata (table structures etc.) in individual .frm files. In contrast, MySQL removed .frm files in MySQL 8.0, replacing it with a global data dictionary. If you stop a MySQL 8.0/8.4 server and point MariaDB binaries at that data directory, MariaDB will look for .frm files to understand what tables exist. Since those files no longer exist in MySQL 8.0+, MariaDB will see an "empty" data directory or throw critical errors. It has no way to read MySQL's new internal dictionary tables.

Q: Can I perform an in-place upgrade for MySQL versions prior to 5.7? After all, those versions still use .frm files instead of a global data dictionary. A: In theory, that's possible, but in practice, you shouldn't do that either. Those old MySQL versions are built for operating systems like RHEL 6 which are out of support. You'd end up on an unsupported version of the operating system, on which you shouldn't install supported versions of MariaDB.

Further Reading

MySQL to MariaDB Compatibility Matrix

The reference guide companion page to the Migration Master Guide.

---

MariaDB versus MySQL - Features

Compare MariaDB vs. MySQL features. Learn about exclusive storage engines, speed enhancements and binary compatibility.

MariaDB versus MySQL - Compatibility

Complete MariaDB Community Server release notes. Complete version history with features, bug fixes, and upgrade compatibility details for production use.

Function Differences Between MariaDB and MySQL

Functions in MariaDB that are not present in MySQL, or vice versa.

System Variable Differences between MariaDB and MySQL

Explore system variable differences between MariaDB Rolling Release and MySQL 8.0. This section details how configuration options vary, aiding in compatibility and migration planning.

Replication Compatibility Between MariaDB and MySQL

Describes replication compatibility between MariaDB and MySQL.