MariaDBとMySQLはどこまで互換か｜Linuxサーバーでの乗り換え判断とレプリケーション・my.cnfの非互換ポイント

この記事のポイント

・MariaDB 10.x と MySQL 5.7 は高互換だが、MySQL 8.0 との差異は認証・GTID・JSONで大きい
・GTIDの形式が異なるため MySQL↔MariaDB 間の混在レプリケーションは設定に注意が必要
・my.cnfで MySQL 8.0 廃止のオプションが MariaDB では継続使用できるケースが多い
・mysqldump を使えばデータ移行自体は単純だが、文字セットと SQLモードを事前確認すること

# MariaDB 10.11 上でのユーザー認証プラグイン確認
$ sudo mariadb -u root -p
MariaDB [(none)]> SELECT user, plugin FROM mysql.user WHERE user = 'root';
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 row in set (0.001 sec)

# MySQLでのSQLモード確認
mysql> SELECT @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
            NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# MariaDBでのSQLモード確認
MariaDB [(none)]> SELECT @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
            NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# my.cnfのオプション有効性チェック（MariaDB）
$ sudo mariadbd --validate-config
# エラーがなければ何も表示されない

# MySQL 8.0でのチェック（廃止オプションが残っている場合のエラー例）
$ sudo mysqld --validate-config
mysqld: [ERROR] unknown variable 'query_cache_type=1'

[mariadb]
# MariaDB→MySQLソースへのレプリカ設定
server_id = 2
log_bin = mysql-bin
binlog_format = ROW
# MariaDB固有GTIDを使わない設定（MySQL GTIDと形式が違うため）
gtid_strict_mode = OFF
slave_parallel_threads = 0
# MySQL 8.0のバイナリログイベントを処理するための設定
replicate_ignore_db = performance_schema

# MariaDB側での設定（MySQL 5.7ソースのポジションに合わせる）
MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST = '192.0.2.10',
  MASTER_USER = 'replicator',
  MASTER_PASSWORD = '***',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 154;

MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# が表示されれば正常

# MySQL側でダンプ（本番のInnoDBをロックなしで取得）
$ mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --set-gtid-purged=OFF \
  -u root -p \
  --databases myapp_db > /backup/myapp_db_dump.sql

# ダンプファイルの文字セットを確認する
$ head -5 /backup/myapp_db_dump.sql
-- MySQL dump 10.19  Distrib 8.0.39, for Linux (x86_64)
--
-- Host: localhost    Database: myapp_db
-- --------------------------------------------------------
-- Server version	8.0.39

$ grep 'character_set_database\|SET NAMES' /backup/myapp_db_dump.sql | head -3
SET NAMES utf8mb4;

# MariaDB側にインポート
$ sudo mariadb -u root -p < /backup/myapp_db_dump.sql

# インポート完了後にデータベースとテーブルを確認
$ sudo mariadb -u root -p -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myapp_db           |
| mysql              |
| performance_schema |
+--------------------+

$ sudo mariadb -u root -p -e "SHOW TABLES FROM myapp_db;"

MariaDB [(none)]> SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 10.11.8-MariaDB    |
+--------------------+

MariaDB [(none)]> USE myapp_db;
MariaDB [myapp_db]> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
  FROM information_schema.SCHEMATA
  WHERE SCHEMA_NAME = 'myapp_db';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_general_ci     |
+----------------------------+------------------------+

# アプリケーションで使っているクエリを実際に実行してエラーがないことを確認する
MariaDB [myapp_db]> EXPLAIN SELECT * FROM orders WHERE user_id = 1;

# MySQL 8.0でのエラー例
2024-09-04T03:12:45.123456Z 0 [ERROR] [MY-011071] [Server]
  unknown variable 'query_cache_type=1'

# 対処: /etc/my.cnf から該当行をコメントアウトまたは削除
# query_cache_type = 1   ← この行をコメントアウト
# query_cache_size = 64M ← この行をコメントアウト

# ダンプファイル内のcollation指定を確認
$ grep 'utf8mb4_0900_ai_ci' /backup/myapp_db_dump.sql | wc -l
47

# 一括置換してMariaDB互換のcollationに変更する
$ sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' /backup/myapp_db_dump.sql

# 置換後に再確認
$ grep 'utf8mb4_0900_ai_ci' /backup/myapp_db_dump.sql | wc -l
0