Replication Compatibility Between MariaDB and MySQL

This page describes replication compatibility between MariaDB and MySQL. For replication compatibility details between different MariaDB versions, see Cross-Version Replication Compatibility.

Replication Compatibility

Replication compatibility depends on:

• The MariaDB version
• The MySQL version
• The role of each server

Replicating from MySQL to MariaDB

MySQL 5.7 to MariaDB

MariaDB 10.2 and later can replicate from a MySQL 5.7 primary server.

MariaDB does not support the MySQL implementation of Global Transaction IDs (GTIDs), so the MariaDB replica server must use the binary log file and position for replication. If GTID mode is enabled on the MySQL primary server, the MariaDB replica server will remove the MySQL GTID events and replace them with MariaDB GTID events.

You can disable GTID and use logfile name and position in MariaDB by excuting on the slave:

CHANGE MASTER ... MASTER_LOG_FILE=file_name MASTER_LOG_POS=# MASTER_USE_GTID=no

Although MariaDB and MySQL 5.7 are compatible at the replication level, they may have some incompatibilities at the SQL level. Those differences can cause replication failures in some cases. To decrease the risk of compatibility issues, it is recommended to set binlog_format to ROW in MySQL. When you want to replicate from MySQL 5.7 to MariaDB, it is recommended to test your application, so that any compatibility issues can be found and fixed. See Incompatibilities and Feature Differences between the specific versions.

MySQL 8.0 to MariaDB

Prior to MariaDB 10.6.21, MariaDB 10.11.11, MariaDB 11.4.5 and MariaDB 11.7.2, MariaDB Server could not replicate from a MySQL 8.0 primary server, because MySQL 8.0 has a binary log format that includes new events which makes it incompatible.

MariaDB 10.6.21, MariaDB 10.11.11, MariaDB 11.4.5, MariaDB 11.7.2 and newer can replicate from a MySQL 8.0 server with the following conditions:

• MariaDB does not support the MySQL default authentication caching_sha2_password, so one has to add another replication user using the mysql_native_password protocol and use this with CHANGE MASTER in MariaDB.
• Columns of type JSON are not supported. One should change these to TEXT in MySQL. MySQL will work fine with JSON as TEXT, except for a minor performance degradation when using JSON expressions.
• binlog-row-value-options should be set to "" should be set. This disables the incompatible PARTIAL_UPDATE_ROWS_EVENT event.
• binlog_transaction_compression should be set to 0. This disables binlog compression and the incompatible TRANSACTION_PAYLOAD_EVENT event.
• MySQL 8.0 utf8mb4_ja_0900_ collations can not be used when replicating to MariaDB 10.6 - MariaDB 11.4.4. MariaDB 11.4.5 and above will support most of the MySQL 8.0 utf8mb4_ja_0900_... collations.
• For differences at the SQL level that may cause replication failures, see Incompatibilities and Feature Differences between the specific versions. When using binlog_format this almost exclusively affects DDL'sm, where MariaDB is very compatible with MySQL.

Here are the changes one should do in the config files for MySQL 8.0:

binlog-row-value-options=""
binlog_transaction_compression=0
# Not required, but recommended for cross-replication
binlog_format=row

Replicating from MariaDB to MySQL

Here are some issues to be aware of when replicating from MariaDB to MySQL.

On the MySQL side, one should:

• Not use binlog with global transaction ids (CHANGE SOURCE_AUTO_POSITION=no)

On the MariaDB side, one should:

• Not use binlog encryption (--encrypt-binlog should be 0)
• Not use binary log compression (--log-bin-compress should be 0)
• Ensure that one uses a character set and collations that MySQL supports. The MariaDB default collations for utf8mb4 is not supported by MySQL. Note that if you just have upgraded from MySQL to MariaDB then you are probably already using the MySQL character sets and collations for your old tables! If you want replication to also work for new tables, the best option is to set the character set and collation in the MariaDB config file.

Example of changes to add to the MariaDB config file:

encrypt-binlog=0
log-bin-compress=0
# Not required, but recommended for cross-replication
binlog_format=row
# Character set to be able to replicate new tables to  MySQL 8.0
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

For differences at the SQL level that may cause replication failures, see Incompatibilities and Feature Differences between the specific versions.