# MySQL to MariaDB Migration: The Master Guide

{% hint style="info" %}
**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.
{% endhint %}

## 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](/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/moving-from-mysql/mysql-to-mariadb-compatibility-matrix.md) 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`.

   <pre class="language-bash" data-overflow="wrap"><code class="lang-bash"># The Universal Master Dump Command
   mysqldump --user=root --password --databases db1 db2 ... \
   --single-transaction --routines --events \
   --triggers --hex-blob > migration_dump.sql
   </code></pre>

### 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](/docs/server/reference/sql-statements/account-management-sql-statements/create-user.md) statements with a default password that can be executed on MariaDB. It does the following:

* Authentication method [mysql\_native\_password](/docs/server/reference/plugins/authentication-plugins/authentication-plugin-mysql_native_password.md) – the script supports passwords porting.
* Authentication method [caching\_sha2\_password](/docs/server/reference/plugins/authentication-plugins/authentication-plugin-caching_sha2_password.md) or authentication string is `NULL` – passwords are reset to default with expiry.

The [PARSEC](/docs/server/reference/plugins/authentication-plugins/authentication-plugin-parsec.md) authentication plugin is intended to be the default in a future release, hence it is recommended to use this during user migration.

{% code overflow="wrap" expandable="true" %}

```sql
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');

```

{% endcode %}

#### 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

{% hint style="info" %}
The SQL may vary depending on the version of MySQL you're coming from.
{% endhint %}

{% code overflow="wrap" expandable="true" %}

```sql
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;

```

{% endcode %}

### **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.

{% stepper %}
{% step %}
**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.)

```bash
# 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".
  {% endstep %}

{% step %}
**Prepare the MariaDB Environment**

On your new server, install MariaDB using the [Installation Guide for MariaDB Enterprise Server](/docs/server/server-management/install-and-upgrade-mariadb/installing-enterprise-server.md) or the [Installation Guide for MariaDB Community Server](/docs/server/server-management/install-and-upgrade-mariadb/installing-mariadb.md).

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.
  {% endstep %}

{% step %}
**Import Data into MariaDB**

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

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

{% endstep %}
{% endstepper %}

### **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.

{% hint style="warning" %}
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.
{% endhint %}

{% code overflow="wrap" %}

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

{% endcode %}

> 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.

{% stepper %}
{% step %}
**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:

```bash
# Run on all tables to refresh statistics
mariadb-admin -u root -p analyze
```

This makes the difference between a migration that "works" and a migration that "works fast".
{% endstep %}

{% step %}
**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:

{% code overflow="wrap" %}

```sql
-- 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;
```

{% endcode %}

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](/docs/server/reference/sql-statements/data-definition/alter/alter-table.md) and [ALTER DATABASE](/docs/server/reference/sql-statements/data-definition/alter/alter-database.md) for details.

{% hint style="info" %}
**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.
{% endhint %}
{% endstep %}

{% step %}
**Check the Error Log**

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

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

{% endstep %}

{% step %}
**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.
{% endstep %}

{% step %}
**Security Hardening: Transition to PARSEC**

For migrations to MariaDB 11.8 LTS and later, consider transitioning your users to the [PARSEC](/docs/server/reference/plugins/authentication-plugins/authentication-plugin-parsec.md) 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.

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

{% endstep %}
{% endstepper %}

## 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-admin analyze`.              |

## 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](https://mariadb.com/docs/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

{% columns %}
{% column %}
{% content-ref url="/pages/XudJckKYO60EEFIXyU7d" %}
[MySQL to MariaDB Compatibility Matrix](/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/moving-from-mysql/mysql-to-mariadb-compatibility-matrix.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The reference guide companion page to the Migration Master Guide.
{% endcolumn %}
{% endcolumns %}

***

{% columns %}
{% column %}
{% content-ref url="/spaces/aEnK0ZXmUbJzqQrTjFyb/pages/ljaPnuP9OEOIwgSWsxZb" %}
[MariaDB versus MySQL - Features](/docs/release-notes/community-server/about/compatibility-and-differences/mariadb-vs-mysql-features.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Compare MariaDB vs. MySQL features. Learn about exclusive storage engines, speed enhancements and binary compatibility.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/spaces/aEnK0ZXmUbJzqQrTjFyb/pages/pdH6OO12JKjzlmojRycE" %}
[MariaDB versus MySQL - Compatibility](/docs/release-notes/community-server/about/compatibility-and-differences/mariadb-vs-mysql-compatibility.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Complete MariaDB Community Server release notes. Complete version history with features, bug fixes, and upgrade compatibility details for production use.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/spaces/aEnK0ZXmUbJzqQrTjFyb/pages/h9uM4o04qrwCSg4s94Rr" %}
[Function Differences Between MariaDB and MySQL](/docs/release-notes/community-server/about/compatibility-and-differences/function-differences-between-mariadb-and-mysql.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Functions in MariaDB that are not present in MySQL, or vice versa.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/spaces/aEnK0ZXmUbJzqQrTjFyb/pages/TFpbjVF4o9j2I44sKMNT" %}
[System Variable Differences between MariaDB and MySQL](/docs/release-notes/community-server/about/compatibility-and-differences/system-variable-differences-between-mariadb-and-mysql.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
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.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/spaces/aEnK0ZXmUbJzqQrTjFyb/pages/w6vsaUgY1DWNEAddWcSQ" %}
[Replication Compatibility Between MariaDB and MySQL](/docs/release-notes/community-server/about/compatibility-and-differences/replication-compatibility-between-mariadb-and-mysql.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Describes replication compatibility between MariaDB and MySQL.
{% endcolumn %}
{% endcolumns %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/moving-from-mysql/mysql-to-mariadb-migration-the-master-guide.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.