【MariaDB】MySQLからMariaDBへの移行方法

MariaDBとMySQLの関係

MariaDBはMySQLからフォーク（分岐）したオープンソースのデータベース管理システム（RDBMS）です。

MySQLがオラクル（Oracle Corporation）に買収された後、MySQLの創設者がMySQLのオープン性を守るために作られたデータベースです。

MySQLからMariaDBへ移行する利点

1. オープンソースの完全性
   GPLライセンスに基づく完全なオープンソースです。商用ライセンスに依存しません。
   
   
2. 高速なパフォーマンス
   特定のクエリやストレージエンジンでMySQLよりも高速な処理が可能です（例: AriaやTokuDB）。
   
   
3. 拡張機能
   新しい機能が追加されています（例: 仮想列、JSON対応の拡張、分散SQL機能など）。
   
   
4. より多くのストレージエンジン
   MariaDBは多様なストレージエンジン（例: Aria, TokuDB, ColumnStoreなど）をサポートしています。

以上の様にオープンソース性/拡張性/パフォーマンス/互換性の観点で優位性がある点がメリットです。

MySQLからMariaDBへの移行環境

1. OS
   Windows 10 Pro 22H2
2. Mysql
   Server version: 8.0.40
3. MariaDB
   Server version: 11.4.4-MariaDB

MySQLからMariaDBへの移行方法

本ブログではユーザーデータベースを移行しシステムデータベースはMariaDBを使用します。

• システムデータベースも移行しようとしたのですが、私の場合、Mysqlのdumpファイル内に不具合があったり、MariaDBがサポートしていない部分が有ったして、リストア時にエラーが散発しました。その為、ユーザーデータのみの移行方法を紹介します。

MySQL設定情報の確認

MySQLの以下の項目の内容を確認しておきます。確認の方法は以降で紹介します。

1. SQLモード（sql_mode）の設定
2. 使用しているストレージエンジン
3. gtid_mode（GTIDの使用）
4. ユーザー情報の取得 —– 今回は（MariaDB）で再作成するので権限等詳細に調査して下さい
5. 設定ファイルの確認と移行

MySQL ユーザーデータベースのバックアップ

今回はユーザー情報や権限等は新規に作成するつもりなので、ユーザーデータベースとストアドプロシージャ、イベント等をバックアップします。

但し、移行の失敗に備えてMySQLの全てのバックアップも取得しておきます。

C:\>mysql --version
mysql  Ver 8.0.40 for Win64 on x86_64 (MySQL Community Server - GPL)

C:\>mysqldump --version
mysqldump  Ver 8.0.40 for Win64 on x86_64 (MySQL Community Server - GPL)

-- ユーザーデータベースのバックアップ
C:\>mysqldump -u root -p --databases testdb --routines --events --triggers --single-transaction --complete-insert --column-statistics=0 --set-gtid-purged=OFF --default-character-set=utf8mb4 > C:\Users\testuser\bkup\backup.sql
Enter password: ******

-- 全データベースのバックアップ
C:\>mysqldump -u root -p --all-databases --routines --events --triggers --single-transaction --complete-insert --column-statistics=0 --set-gtid-purged=OFF --default-character-set=utf8mb4 > C:\Users\testuser\bkup\backup_all.sql

1. –set-gtid-purged=OFF
   GTIDはレプリケーションの管理を簡単にするために使用されます。MariaDBとは完全な互換性は無い為、レプリケーションを使用していてもここではMySQLのGTID情報はバックアップに含めていません。
   
   
2. –column-statistics=0
   ServerもmysqldumpもVer. 8.0 なのでカラム統計情報（column statistics）はサポートされている筈ですが、警告メッセージ（Warning: column statistics not supported by the server.）が表示されたので表示しないようにしました。　

ＭySQL 文字セットと照合順序のデファオルト値確認方法

下記コマンドで確認しておきます。

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | cp932                                                   |
| character_set_connection | cp932                                                   |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | cp932                                                   |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | cp932_japanese_ci  |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set, 1 warning (0.01 sec)

mysql>

バックアップデータの修正

1. 文字セットと照合順序のデファオルト値確認方法
   ＭySQLのサーバーでは「utf8mb4_0900_ai_ci」が使用されています。「utf8mb4_0900_ai_ci」はMariaDBではサポートされていないので、バックアップデータ内の全てのutf8mb4_0900_ai_ciをutf8mb4_general_ciに変更します。

• 例）変更前

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

• 例）変更後

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

MariaDBのインストール

MySQL（本番環境）が動作しているサーバーとは別の機器に推奨するMariaDBのバージョンをインストールします。インストール方法は下記ブログを参考下さい。

【参考ブログ】：MariaDBのインストール

ＭyＳQLバックアップデータのリストア

リストア先のデータベースを作成します。

MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.010 sec)

MariaDB [(none)]>

mysqlコマンドを使用して、バックアップファイルを指定したデータベースにリストアします。

C:\>mysql -u root -p testdb < C:\Users\Cool_Kabachan\bkup\backup.sql
Enter password: ******

リストア結果の確認

下記の内容を確認するとデータベースは正しくリストアされています。

MariaDB [testdb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.003 sec)

MariaDB [testdb]> use testdb
Database changed
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| users            |
+------------------+
1 row in set (0.001 sec)

MariaDB [testdb]> select * from users;
+----+--------------+--------------------------+
| id | username     | email                    |
+----+--------------+--------------------------+
|  1 | john_doe     | john.doe@example.com     |
|  2 | jane_smith   | jane.smith@example.com   |
|  3 | alice_wonder | alice.wonder@example.com |
+----+--------------+--------------------------+
3 rows in set (0.001 sec)

MariaDB [testdb]>

【1】SQLモード（sql_mode）の設定

MySQLやMariaDBのサーバーでSQLの構文や動作を制御する設定のことです。
SQLモードを移行後も維持する場合は、MySQLとMariaDBでのsql_modeの違いを確認し、必要に応じて調整するします。

SQLモード（sql_mode）が異なる事によって発生する問題

1. データの整合性が保たれない
2. クエリが異なる結果を返してアプリケーションの動作に影響を与える可能性がある。

SQLモード（sql_mode）の確認方法

• MySQL8.0

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql>

• MariaDB11.4

ariaDB [(none)]> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

SQLモード（sql_mode）

今回は相違点を下記のように変更します。

• ONLY_FULL_GROUP_BY
  MySQL では、ONLY_FULL_GROUP_BY が有効だと、GROUP BY に指定されていない非集約カラムが SELECT 句に含まれるとエラーになりますが、MariaDBではエラーにならない場合がありあります。テストをして確認する必要がありますが、MariaDBに設定する事とします。
  
  
• NO_ZERO_IN_DATE,NO_ZERO_DATE
  日付の０を含まなようにする設定です。MySQLとの互換性を保つ為、MariaDBにも設定します。

SQLモード（sql_mode）設定方法

MariaDBの「my.ini」に以下の様に追加します。

[mysqld]
　・
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

【２】使用しているストレージエンジンの違い

ストレージエンジンとは、MariaDBがデータを保存し操作する方法を決定するモジュールです。テーブルごとに異なるストレージエンジンを設定できるため、用途に応じた柔軟なデータ管理が可能です。

ストレージエンジンが異なる事によって発生する問題

1. 特定のストレージエンジンでしか動作しない機能を使用している場合、移行後にエラーが発生する。
   
   
2. 代替ストレージエンジンに自動変更され、パフォーマンスや動作が変わる可能性がある。
   
   
3. データ移行時、移行が途中で停止したりデータが一部失われる

ストレージエンジンの確認方法

• MySQL 8.0

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'testdb';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| users      | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

mysql>

• MariaDB 11.4

MariaDB [(none)]> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'testdb';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| users      | InnoDB |
+------------+--------+
1 row in set (0.008 sec)

MariaDB [(none)]>

MySQLのストレージエンジンがMariaDBでサポートされていない場合

この場合はMariaDBの適切なエンジンに変更する必要があります。

• MySQLからMariaDBへの移行時にサポートされていないエンジン
  • MyISAM → MariaDBではInnoDBやAriaなどのエンジンに変更する必要があります。
  • HEAP → MariaDBでは利用できません。

例）MyISAMテーブルをInnoDB に変更する方法

ALTER TABLE my_table ENGINE = InnoDB;

• 注意点
  • エンジン変更中はテーブルがロックされます。
  • データサイズが大きい場合、時間がかかる可能性があります。
    

【３】gtid_mode（GTIDの使用）

GTID は、分散環境でのレプリケーションを管理しやすくするために、トランザクションごとに一意の識別子を割り当てる仕組みです。

しかし、MariaDBとは完全な互換性は無い為レプリケーションを使用していてもGTID情報はバックアップに含めていませんでした。

MariaDBでGTIDを使用する場合は、MariaDBで設定します。

gtid_modeの設定が異なる事によって発生する問題

1. レプリケーションが失敗する可能性がある
   
   
2. レプリケーションパフォーマンスの低下
   
   
3. 複数のトランザクションが同じデータにアクセスし、データの一貫性や整合性が損なわれる可能性がある

【４】ユーザー情報の取得と設定

MySQLでのユーザー情報の取得

下記のようにしてmysql.userテーブルから詳細なユーザー一覧と権限を取得します。

• ユーザー一覧の取得

mysql> SELECT user,host,plugin,authentication_string,ssl_cipher FROM mysql.user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+------------------------+
| user             | host      | plugin                | authentication_string                                                  | ssl_cipher             |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+------------------------+
| test_user        | %         | mysql_native_password | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B                              | 0x                     |
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 0x                     |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 0x                     |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 0x                     |
| root             | localhost | mysql_native_password | *B9369488AFF3B0F0455CF44AB022B7ABDE6F9761                              | 0x                     |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+------------------------+
5 rows in set (0.00 sec)

• 権限の取得
  下記のように各ユーザー毎に権限を確認します。

mysql> SHOW GRANTS FOR 'test_user'@'%';
+------------------------------------------------------+
| Grants for test_user@%                               |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`                |
| GRANT ALL PRIVILEGES ON `users`.* TO `test_user`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

MariaDBでのユーザー追加と権限の設定

MySQLで確認したユーザーと権限をMariaDBで設定します。

【５】設定ファイルの確認

MySQLで設定されている項目がMariaDBに無かった場合等は、MariaDBの設定ファイルに追加/変更して下さい。

十分なテストを実施する

十分なテストを実施し不具合が無い事を確認してから本番環境に切り替えて下さい。

まとめ：移行を成功させるために

MySQLからMariaDBへの移行を成功させるために重要なポイントを以下に簡潔にまとめます。

• 移行可能なバージョンの互換性を確認する
  • 例）MySQL 8.0 → MariaDB 10.5以降。
    
    
• データと構成のバックアップ
  • 移行前にすべてのデータをmysqldumpでバックアップし設定ファイルも保存する
    
    
• SQLモードの確認
  • MySQLとMariaDBのSQLモード（例: STRICT_TRANS_TABLES, ONLY_FULL_GROUP_BY）の違いを確認し、必要に応じて調整する
    
    
• 認証プラグインの確認　—– ユーザーデータしか移行していない為、今回は対象外
  • MySQL 8.0の**caching_sha2_passwordはMariaDBで非対応のため、mysql_native_password**に切り替える。
    
    
• ストレージエンジンの互換性を確認する
  • MariaDBでサポートされないエンジン（例: MySQLのNDB）を使用していないか確認。
  • 必要に応じてInnoDBやAriaに変更。
    
    
• GTID（グローバルトランザクションID）対応
  • MySQLとMariaDBのGTIDは互換性がないため、必要なら無効化する。
    
    
• 特定機能の互換性確認
  • ストアドプロシージャ、トリガー、イベントなどがMariaDBで動作するか十分テストする。
    
    
• 確実なテスト後の移行
  • 開発環境またはステージング環境で移行テストを実施し、パフォーマンスや互換性を確認する。
    
    
• 移行後の動作検証
  • アプリケーションやクエリの互換性を確認。
  • ログやエラーをチェックして問題を修正。
    
    
    

以上を実施し確実な移行を実施して下さい。