Bridging the Gap:

MySQL to MariaDB Migration Guide

2. Authentication Differences

The implementation of user authentication has historically been a major point of incompatibility; however, the caching_sha2_password authentication plugin is now available to address this.

• Newer releases (compatible): The caching_sha2_password authentication plugin has been added as a compatibility feature for MySQL. Starting with the MariaDB Community Server 11.4, 11.8 and MariaDB Enterprise Server 11.8.3-1 release, this is no longer a challenge.
• Older releases (incompatible): For releases prior to the versions mentioned above, the caching_sha2_password authentication plugin is not supported. It may be backported to 10.6 in the future.

3. Data Type Defaults for Virtual Columns

Incompatibilities arise from how MySQL 8.0 handles default values on large text fields and the syntax for defining constraints on generated columns.

• Virtual column NULL/NOT NULL syntax: MariaDB currently does not support NULL/NOT NULL syntax for virtual (computed) columns unlike MySQL.
  • In MariaDB, using NOT NULL on a virtual column is semantically equivalent to applying a CHECK constraint (e.g., CONSTRAINT col_not_null CHECK (col IS NOT NULL)).
  • Identification (virtual columns): To check if your tables use virtual columns in MySQL with NULL/NOT NULL constraints, query the column metadata and update the constraints.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,COLUMN_TYPE,EXTRA, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNSWHERE EXTRA LIKE ‘%VIRTUAL%’OR EXTRA LIKE ‘%STORED%’ORDER BY TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME;

MariaDB’s Migration Assessment Tool can identify these and convert them automatically into a supported MariaDB schema.

4. Metadata (System Tables) Management – Architectural Differences

• MariaDB’s metadata management is a major difference from MySQL 8.0’s approach.
  • MariaDB uses .frm files and system tables
  • MySQL 8.0+ uses a unified Global Data Dictionary (DD)
• Migration path: Due to this architectural divergence, avoiding in-place upgrades and an export/import build migration strategy is recommended with pre/post data migration remediation tasks.

Remediation Task	Details and Required Action
User and Privileges	The mysql.user table structure is incompatible and was not restored. You must manually re-create all necessary users and grants on the new MariaDB server.
Authentication (SHA-2)	The required feature is available in the latest releases of MariaDB 11.4 and 11.8.3-1.
Run Upgrade Script	As a best practice post-install step, running mariadb-upgrade is mandatory. This updates the system privilege and event tables, ensuring consistency with all fields unique to the MariaDB version.

5. User Privilege Differences and Mapping the Security Model

While MariaDB maintains high syntactic compatibility with MySQL, the core difference lies in how MySQL and MariaDB classify and manage permissions, impacting your migration strategy, particularly for administrative users.

The two systems diverge in their approach to granting administrative control:

• MariaDB: Relies on Static Privileges (hardcoded, built-in permissions like SELECT) and a Role system for granular administrative control.
• MySQL 8.0+: Uses Static Privileges plus Dynamic Privileges (permissions defined and managed by components/plugins at runtime, e.g., BACKUP_ADMIN).

Remediation

Use Show Privileges to identify any MySQL user relying on a Dynamic Privilege. You must then map these functions to MariaDB’s granular Static Privileges or its Role structure.

MariaDB introduced an architectural change by dividing the monolithic SUPER privilege into multiple, distinct, granular privileges. This allows administrators to adopt a security model based on the principle of least privilege.

If MySQL users relied on the single SUPER privilege, you must now explicitly grant several distinct MariaDB privileges to achieve the same administrative scope.

MariaDB Granular Privilege	Function/Purpose
CONNECTION ADMIN	Administers connection resource limits and allows ignoring the max_user_connections variable.
REPLICA MONITOR	Required to execute SHOW REPLICA STATUS and SHOW RELAYLOG EVENTS.
SET USER	Enables setting the DEFINER when creating routines or triggers.
READ_ONLY ADMIN	Allows a user to perform write operations even when the database is protected by the read_only system variable.
BINLOG MONITOR	The functional equivalent of the older REPLICATION CLIENT privilege (though the old name remains an alias).

This means you must explicitly grant multiple privileges (e.g., BINLOG ADMIN, REPLICATION MASTER ADMIN, REPLICATION SLAVE ADMIN) instead of a single SUPER grant.

6. Data-at-Rest Encryption and Compression

The implementation of table-level encryption and compression features differs significantly between MySQL and MariaDB.

• Migration requirement: Encrypted or compressed tables must be de-encrypted/decompressed before migration using a logical dump, and then re-encrypted/re-compressed after the tables are loaded into MariaDB.
• Detect encrypted tables:

SELECT table_schema, table_name, create_options FROM  information_schema.TABLES WHERE create_options LIKE “%ENCRYPT%”;.

• Detect compressed tables:

SELECT table_schema, table_name, create_options FROM information_schema.TABLES WHERE create_options LIKE “%COMP%”;.

7. Replication and Advanced Clustering Considerations

When migrating highly available setups (like Galera clusters) or replication environments, specific configuration changes are mandatory.

Replication from MySQL 8.0 to MariaDB

MariaDB versions MariaDB 11.4/11.8 and newer are capable of replicating from a MySQL 8.0 primary, but require configuration adjustments on the MySQL side.

• Disable incompatible events: You must disable binary log compression and incompatible row update events in your MySQL 8.0 configuration file:
  
  • Set binlog-row-value-options=”” (disables PARTIAL_UPDATE_ROWS_EVENT).
  • Set binlog_transaction_compression=0 (disables TRANSACTION_PAYLOAD_EVENT).
  • It is also recommended to set binlog_format=row for cross-replication compatibility.
• GTID handling: MySQL implements GTID differently from MariaDB, hence MariaDB replica must use the binary log file and position by explicitly disabling GTID usage for the master connection.
  • Command on replica: CHANGE MASTER … MASTER_LOG_FILE = file_name MASTER_LOG_POS = # MASTER_USE_GTID =no.