Bridging the Gap: MySQL to MariaDB Migration Guide

Before initiating any migration, it is absolutely crucial to prepare your environment and codebase:

• Review incompatibilities: Always verify the specific feature differences and incompatibilities between your current MySQL version and your target MariaDB version. MariaDB maintains high compatibility, but checking for differences helps you to select the most suitable version.
• Backup: Before making any changes, always back up your data to ensure recovery.
• Test applications: While the syntax for application code and database scripts is often highly compatible, thorough testing and validation are required due to potential functional differences.

Most differences often appear in data storage formats, user authentication, and encryption methods.

1. JSON Data Type Handling

MariaDB and MySQL handle the storage of JSON data differently. MariaDB implements the JSON data type as LONGTEXT while MySQL uses a binary format for JSON.

Migration Considerations:

• Replication/Galera: If you are migrating a cluster using replication or Galera, you must convert JSON columns to TEXT in the MySQL source to ensure consistent data storage between systems.
• SQL changes/Casting: If application SQL contains direct CAST( AS JSON), these will fail on MariaDB because JSON is an alias and not a native type. The remediation would be to cast it to LONGTEXT.
• JSON functional differences (MySQL 8.4 to MariaDB 11.8): Identify and replace the MySQL-specific shorthand operators (->, ->>) and any unavailable functions being used (e.g., JSON_STORAGE_SIZE()).
• Due to the implementation differences, thorough testing and validations are required for complex JSON functions to catch any subtle differences.

Remediation

Tools:

• Automatic conversion: MariaDB versions 10.6.15 or above, or 10.11.6 (and newer), will automatically convert the MySQL JSON type to a MariaDB TEXT column during the upgrade process using the 7 Bridging the Gap: MySQL to MariaDB Migration Guide E-BOOK mariadb-upgrade tool.
• Migration tools: MariaDB’s migration assessment tool, SQLines, identifies incompatibilities and converts MySQL schemas into a supported MariaDB schema format.

Manually:

• Identify JSON columns: Use SELECT table_schema, table_name FROM information_schema. COLUMNS WHERE data_type=”JSON”
• Convert JSON to TEXT: Execute ALTER TABLE table_name MODIFY json_column LONGTEXT

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.

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.

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.

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:

• Detect compressed tables:

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