この記事は How to Migrate from MariaDB to MySQL 8.0 の翻訳版です。
MariaDBからMySQLデータベース移行の要望が増加しています。
MariaDBは大幅に分岐しており、MySQLと互換性のあるドロップインではありません。
MariaDBからMySQLに移行するには、インプレース・アップグレードを実行が出来ず、論理ダンプとロードが必要になります。
このブログでは、このような移行のプロセスと、(完全なリストではなく)アップグレード中に発生する可能性のある問題について説明します。
このプロセスは、次の4つの操作で構成されます：
- 最終的な非互換性の確認
- 論理データダンプ
- MySQL 8.0のインストール
- データのロード
今回の移行プロセスはMariaDB 10.6を使用して解説しています。
最終的な非互換性の確認
この部分は非常に影響が大きく、移行を遅らせれば遅らせるほどプロセスも複雑にもなります。
データ元となるMariaDB 10.6サーバーでの異なる機能、削除された機能、またはMySQL 8.0で使用できない機能を使用しているかどうかを確認する必要があります。
高可用性
MariaDBにおけるHAは、別の会社Codershipによって開発されたプラグインGaleraによって提供されてます。MySQLには、Group Replication、InnoDB Cluster、ClusterSetおよびReplicaSetを使用したネイティブ、組込み、HAおよびDRが含まれます。この記事では、GaleraからInnoDBクラスタへの移行については説明していません。
ストレージエンジン
MariaDB Community Editionでは、様々なストレージ・エンジンがαまたはβステージに含まれています。これらのエンジンは、MariaDB Enterprise Editionには含まれていません。移行の前に、全てのストレージ・エンジンのデータをInnoDBに変換する必要があります。
MySQLプライマリ・ストレージ・エンジンはInnoDBです。
以下のクエリーはデータベース上で実際に使用されているストレージエンジンを検証するために使用できます：
SELECT COUNT(*) as '# TABLES',
CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') INDEXES,
CONCAT(sum(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2)), 'G') 'TOTAL SIZE', ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA
NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY engine;
+----------+-------+---------+------------+--------+
| # TABLES | DATA | INDEXES | TOTAL SIZE | ENGINE |
+----------+-------+---------+------------+--------+
| 1 | 0.00G | 0.00G | 0.00G | Aria |
| 5 | 0.00G | 0.00G | 0.00G | InnoDB |
| 1 | 0.00G | 0.00G | 0.00G | MyISAM |
+----------+-------+---------+------------+--------+
3 rows in set (0.002 sec)前述の例では、MySQL 8.0でサポートされていないストレージ・エンジンを使用する表が1つあることがわかります。次の表を見つけましょう：
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT
IN ('mysql', 'information_schema', 'performance_schema', 'sys')
AND engine NOT IN ('MyISAM','InnoDB');
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| mydatabase | t4 | Aria |
+--------------+------------+--------+
1 row in set (0.001 sec)これを修正するには、MariaDBサーバーでエンジンを直接変更するか(推奨)、MySQL 8.0でデータをロードするときにエンジンを変更します。
推奨手順:
ALTER TABLE mydatabase.t4 ENGINE=InnoDB;あらかじめストレージ・エンジンをInnoDBに変更せずに、この後の手順でダンプ例を示しますが、行のサイズに関連する制限がある場合があります。また、使用される文字セットによっても異なる場合があることに注意してください。
機能
MariaDBにはJSON_DETAILEDなど、MySQL8.0には存在しない関数がいくつかあります。
これらの関数のリストは MariaDB’s ドキュメントに記載されています。しかしこのページでは、特にMySQL 8.0に関する一部の情報が古く更新されていませんので注意してください (非表示の列 invisible columns, 仮想列 virtual columns,、wait、intersect、例外など)
これらの関数が列のデフォルト値に存在しない限り、これは移行の妨げにはなりません。 もちろん、アプリケーションでこれらの関数の一部を使用している場合は、MySQL 8.0で適切な関数を使用するように変更する必要がある場合があります。
これを説明するために、ADD_MONTHS関数を使用してみましょう。
まず、いくつかの列のデフォルトとしてこの関数があるかどうかを見てみましょう:
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE COLUMN_DEFAULT LIKE '%add_months%';
Empty set (0.055 sec)素晴らしい ！…。 うーん、しかし、その特定の関数をデフォルトとしてテーブルを作成したと確信しています。 だって、以下のクエリーを私が実行しましたから：
ALTER TABLE t6 ADD COLUMN future DATETIME DEFAULT (ADD_MONTHS(NOW(), 2));実際、いくつかの関数はエイリアスのように機能しています。 SHOW CREATE TABLEステートメントの出力を確認すると、関数が変換されていることがわかります：
SHOW CREATE TABLE t6\G
*************************** 1. row ***************************
Table: t6
Create Table: CREATE TABLE `t6` (
`id` int(11) NOT NULL DEFAULT nextval(`mydatabase`.`s3`),
`b` int(11) DEFAULT NULL,
`future` datetime DEFAULT (current_timestamp() + interval 2 month),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
1 row in set (0.000 sec)ADD_MONTHS() が + interval 2 か月分変換されていることがわかります。
これは、テーブルを作成するときにデフォルト値として使用された場合、この関数が問題にならないことを意味します。
ただし、アプリケーションがクエリーでそのような関数を使用する場合は書き直す必要があります。 一般ログを有効にして解析することは、そのようなクエリーを探すための最良の方法です。 しかし、多くの DBAは一般ログを有効にすることを好みません。これは、重いワークロードで大きなオーバーヘッドが発生する可能性があるためです。
もう 1 つのオプションは、Performance_Schemaを使用することです。 これを使用する方法は次のとおりです：
SELECT DIGEST_TEXT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%add_months%';
+------------------------------------------------------------------------------------------+
| DIGEST_TEXT |
+------------------------------------------------------------------------------------------+
| ALTER TABLE `t6` ADD COLUMN `future` DATETIME DEFAULT ( ADD_MONTHS ( NOW ( ) , ? ) ) |
| SELECT ID , `b` , ADD_MONTHS ( `future` , ? ) `present` FROM `t6` |
| SELECT ID , `b` , ADD_MONTHS ( `future` , ? ) `present` FROM `t6` ORDER BY `b` |
| SELECT ADD_MONTHS ( `future` , ? ) `present` , COUNT ( * ) FROM `t6` GROUP BY `present` |
+------------------------------------------------------------------------------------------+
4 rows in set (0.000 sec)書き換え対象の４つのクエリーが出力されました。
データ型
MariaDBはデータ型としてINET6をサポートしていますが、このデータ型はMySQL 8.0にはありません。MySQL 8.0では、IPv6値はVARBINARY(16)に格納されます。
データベースで使用されているデータ型のリストを見つけるには、次のクエリーとなります：
SELECT DATA_TYPE , count(*) TOT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT
IN ('mysql', 'sys', 'information_schema', 'performance_schema')
GROUP BY 1;
+-----------+-----+
| DATA_TYPE | TOT |
+-----------+-----+
| bigint | 14 |
| datetime | 1 |
| inet6 | 1 |
| int | 10 |
| longtext | 3 |
| tinyint | 2 |
+-----------+-----+
6 rows in set (0.001 sec)inet6を使用している1つの列があることがわかります。
JSON列を作成したのに、何も表示されないことに気付きましたか..?!?
MariaDBでは、JSON列の実体は、JSON_VALID()を使用してチェックを行うLONGTEXTのエイリアスに過ぎません：
`doc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
DEFAULT NULL CHECK (json_valid(`doc`))MySQL 8.0では、JSONはネイティブ データ型であり、パフォーマンスとレプリケーションに関連する複数の機能と機能強化を可能にしています。
不明なデータ型をそのまま使用すると、論理ダンプは次のようなエラー メッセージで失敗します：
Util.dumpInstance: Unknown data_type: inet6 and column_type: inet6 (LogicError)したがって、この場合は、ダンプの実行前に列を VARBINARY(16)に変換する必要があります：
ALTER TABLE t5 MODIFY address VARBINARY(16);
Query OK, 4 rows affected (0.019 sec)
Records: 4 Duplicates: 0 Warnings: 0データダンプ
上記のすべての点を確認したら、データをダンプします。
MySQL Shellをダンプおよびロード ユーティリティと共に使用して、この論理ダンプを実行します。
この機能はコマンドラインから、またはインタラクティブに使用できます：
$ mysqlsh root@127.0.0.1:10612 -- util dumpInstance "/tmp/dump_mariadb_10_6" \
--users=false
NOTE: Backup lock is not supported in MySQL 5.6 and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping.
Acquiring global read lock
Global read lock acquired
Initializing - done
WARNING: Failed to fetch value of @@GLOBAL.GTID_EXECUTED.
2 out of 6 schemas will be dumped and within them 5 tables, 0 views.
Gathering information - done
All transactions have been started
Global read lock has been released
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
NOTE: Table statistics not available for `mydatabase`.`t2`, chunking operation may be not optimal.
Please consider running 'ANALYZE TABLE `mydatabase`.`t2`;' first.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
122% (11 rows / ~9 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 2
Tables dumped: 5
Uncompressed data size: 287 bytes
Compressed data size: 224 bytes
Compression ratio: 1.3
Rows written: 11
Bytes written: 224 bytes
Average uncompressed throughput: 287.00 B/s
Average compressed throughput: 224.00 B/sもしくは、MySQL Shellでインタラクティブに：
ダンプは簡単な操作です。{users: false} オプションを使用することを忘れないでください。
サポートされていないストレージ エンジンがある場合は、次のコマンドを使用して強制的にInnoDBにすることができます：
util.dumpInstance("/tmp/dump_mariadb_10_6_force",
{users: false, compatibility: ["force_innodb"]}プロセスの処理中には次のような状況報告が出力されます:
NOTE: Table `mydatabase`.`t4` had unsupported engine Aria changed to InnoDB
NOTE: Table `mydatabase`.`t3` had unsupported engine MyISAM changed to InnoDBただし、一部の構文は手動で変更する必要があります。 たとえば、AriaエンジンはInnoDBに置き換えられますが、PAGE_CHECKSUM=1の補足は残り、削除する必要があります。 この変更は、ダンプディレクトリ内のテーブルのsqlファイルで行う必要があります。
データのロード
新しくインストールしたMySQL 8.0で、ダンプをロードできるようになりました。 ここでも、この操作にMySQL Shellを使用します。
一部の特殊なストレージ エンジンまたはその他の機能に関連する余分な構文が原因で、ロードが失敗し、手動での変更が必要になる場合があります (FAQ を参照)。
すべての準備が整ったら、次のようにMySQL 8.0にダンプをロードできます：
前回、検証用に失敗したものがありましたので以前のデータを削除し、resetProgress: trueオプションで再ロードしました。これはMySQL Shell が中断されて再開される可能性があるための対応です。
ユーザーと認証
論理ダンプを作成できるようにするには、{users: false} オプションを使用してユーザーをスキップする必要があったことを思い出してください。
ただし、移行したいユーザーがいる場合は、 user.getUsersGrants() プラグインを使用してください：
JS > user.getUsersGrants("fred")
-- User `fred`@`%`
CREATE USER IF NOT EXISTS `fred`@`%` IDENTIFIED
WITH 'mysql_native_password' AS '*6C69D17939B2C1D04E17A96F9B29B284832979B7';
GRANT ALL PRIVILEGES ON *.* TO `fred`@`%`;そして、新しいMySQL 8.0でステートメントを再生します。 使用される認証プラグインは、MySQL 8.0のデフォルトではない古いもの (mysql_native_password) であることに注意してください。 MySQL 8.0では、より安全な認証方法caching_sha2_passwordが使用されます。
ライブマイグレーション
MariaDBサーバーで特定の機能を使用していない場合は、両方のシステム間で非同期レプリケーションを使用することもできます。 もちろん、これは事前検証が必要です。これにより最小限のダウンタイムでライブ マイグレーションを実行できる可能性があります。
まとめ
MariaDB固有の機能を使用していない場合、MariaDBからMySQL 8.0への移行は、MySQL Shell Dump & Load Utility を使用して簡単に行うことができます。 ただし、移行の決断を遅らせば遅らせるほどプロセスは複雑になり、locked in MariaDBのように断念する可能性があることを忘れないでください。
あらゆるセキュリティ機能の強化や開発の継続性、またMySQL 8.0で利用可能なすべての新機能を活用したい場合は、オフィシャルのMySQLに移行することが適切な時期です。
FAQ
問題が発生する可能性のあるいくつかのまれなケースをいくつか挙げます:
MariaDBのシーケンスはどのように移行すれば良いでしょうか?
MariaDBは、MySQL 8.0では利用できないシーケンスをサポートしています。この機能を使用しているMariaDBユーザーは多くありません。 移行のためにはこれらのテーブルを対応する必要があるため、移行元サーバーがシーケンスを使用しているかどうかを確認しましょう：
SELECT COUNT(*), TABLE_TYPE FROM information_schema.TABLES GROUP BY table_type;
+----------+------------------+
| COUNT(*) | TABLE_TYPE |
+----------+------------------+
| 117 | BASE TABLE |
| 2 | SEQUENCE |
| 1 | SYSTEM VERSIONED |
| 79 | SYSTEM VIEW |
| 101 | VIEW |
+----------+------------------+
5 rows in set (0.0250 sec)2つのシーケンスがあることがわかります。 通常、シーケンスは列のデフォルト値として使用されます。それらがどのテーブルに関連しているかを調べることができます：
WITH seqlist (a) AS (
SELECT CONCAT('%`',TABLE_SCHEMA,'`.`', TABLE_NAME,'`%') a
FROM information_schema.TABLES
WHERE table_type="SEQUENCE")
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
JOIN seqlist WHERE COLUMN_DEFAULT LIKE seqlist.a;
+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| t5 | a |
| t6 | id |
+------------+-------------+
2 rows in set (0.023 sec)これらのテーブルを手動で修正する必要があります。そうしないと、ロードが失敗します。 シーケンスの場合、エラー メッセージは次のようになります：
ERROR: [Worker003] Error processing table `mydatabase`.`t6`: MySQL Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nextval(`mydatabase`.`s3`),
`b` int(11) DEFAULT NULL,
`future` datetime DEFA' at line 2: CREATE TABLE IF NOT EXISTS `t6` (
`id` int(11) NOT NULL DEFAULT nextval(`mydatabase`.`s3`),
`b` int(11) DEFAULT NULL,
`future` datetime DEFAULT (current_timestamp() + interval 2 month),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ERROR: [Worker003] While executing DDL script for `mydatabase`.`t6`: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nextval(`mydatabase`.`s3`),
`b` int(11) DEFAULT NULL,
`future` datetime DEFA' at line 2
ERROR: Aborting load...エラーに対応する為にダンプから出力されたSQLファイルを直接修正し、"You have an error in your SQL syntax"に対応する必要があります。
たとえば、ファイル mydatabase@t5.sql では、次のようになります：
CREATE TABLE IF NOT EXISTS `t5` (
`a` int(11) NOT NULL DEFAULT nextval(`mydatabase`.`s1`),対応結果：
CREATE TABLE IF NOT EXISTS `t5` (
`a` int(11) NOT NULL auto_increment,MariaDBシステムバージョン管理テーブル（system-versioned tables）は どのように移行すれば良いでしょうか?
先ほどのtable_typeを取得したクエリ ー(FAQ の質問) から、1 つのSYSTEM VERSIONEDテーブルもあることがわかります。これらのタイプのテーブルは、MySQL 8.0ではサポートされていません。 これらは変更しないとダンププロセス中において無視されます。
そのようなテーブルから最新のデータを移行する場合は、バージョン管理を削除する必要があります。
System Versionedテーブルのリストを取得するには、次のクエリーを実行します：
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_TYPE='system versioned';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mydatabase | t |
+--------------+------------+
1 row in set (0.0090 sec)次のステートメントでバージョン管理情報を削除しましょう；
ALTER TABLE mydatabase.t DROP SYSTEM VERSIONING;
Query OK, 0 rows affected (0.0269 sec)