MySQL Database Serviceへ移行してみよう
Oracle Cloud Infrastructure(以下OCI) の ピュアMySQL & フルマネージド なDBaaSである MySQL Database Service(旧 MySQL Cloud Service) へ移行してみよう、ということで今回試してみました。
MySQL Database Service(以下MDS) へ移行することで様々なメリットを享受できます。
- MySQLベンダであるOracle MySQLチームによるサポート、開発、メンテナンス
- バックアップ・リカバリの自動化
- 暗黙的なストレージ暗号化
- OSも含めた自動的なアップデート・セキュリティパッチの適用
- 監視・監査機能の提供
- Heatwaveの利用(超高速分析エンジン) -> DataWareHouseとしての利用も可能!
中でもHeatwaveは昨年末にリリースされたインメモリ&列志向なデータベースエンジンで、Oracle社でもイチオシの機能です。
近いうちに本機能についてもご紹介する予定です。
HeatWaveは、MySQLデータベースサービスへのアドオンです。
OracleCloudInfrastructure用に最適化された高性能でスケーラブルなインメモリ分析処理エンジンを提供します。
お客様は、ETLを必要とせず、アプリケーションを変更せずに、MySQLデータベースに保存されているデータに対してHeatWaveを実行できます。
アプリケーションは、標準のMySQLプロトコルを介してHeatWaveにアクセスするだけで、一般的な管理アクションは自動化され、統合され、
OCI Webコンソール、REST API、CLI、またはDevOpsツールを介してアクセスできます。
HeatWaveクエリは、MySQLデータベースよりも桁違いに高速化されます。
日本語のドキュメントではまだHeatwaveについての記載がありませんでしたので、原文の翻訳になりますが、桁違いに高速化されますという一文に自信を感じます。
さて、以前より、バックアップデータを使用したMDSインスタンスを作成することは可能でしたが、インバウンドレプリケーション機能 がリリースされた ことで、より実践的なマイグレーションが可能となったように思います。
オフィシャルのドキュメントで紹介されているmysqlshを使用したデータエクスポート・インポート と、インバウンドレプリケーションを組み合わせて、オンプレのMySQLからMDSへのマイグレーションを実施してみます。
Terraformを使用した検証環境の構築
検証に利用する環境の構成図は以下となります。
概略としては以下のとおりです。
- MDSなどを配置するためのネットワーク関連リソース(VCN/Subnet/RouteTable/SecurityList/InternetGateway)
- オンプレミスのMySQLバックアップを配置するためのオブジェクトストレージ
- MySQL Database Service
- オンプレミス, OCI間でレプリケーションを接続するためのOpenVPNサーバが稼働するComputeインスタンス
- OCI サブネット内部からMDSへ操作を行うためのComputeインスタンス
今回OpenVPNサーバを除くリソースについてはTerraformを使用して作成しました。
terraformの設定ファイルは以下となります。
|
1 2 3 4 |
$ cd oci-mds-terraform $ terraform init $ terraform plan $ terraform apply |
GUIコンソールを使用した作成方法については、弊社過去ブログ記事で紹介しておりますので、合わせてご確認ください。
MySQL Database Service のインスタンスを作成してみる
オンプレミス-MDS 接続ユーザの作成
クライアント用として作成したComputeインスタンスにMySQL Clientをインストールし、MDSにテスト接続用ユーザを作成します。
|
1 2 3 4 |
$ ssh opc@<Public IP> $ yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm $ yum -y install mysql-shell $ mysqlsh mysqlx://adminUser:<password>@192.168.1.10:33306 --sql "CREATE USER test@`%` IDENTIFIED BY 'P@ssw0rd';" |
オンプレミスのMySQLへデータロード
検証用のMySQLサーバはDockerを使用して用意しました。
テスト用ユーザ、レプリケーション用ユーザを作成し、world databaseのデータをロードしています。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ mkdir initdb.d $ wget -O initdb.d/world.sql.gz https://downloads.mysql.com/docs/world.sql.gz $ cat > user.sql <<-EOF CREATE USER IF NOT EXISTS repl@`%` IDENTIFIED BY 'P@ssw0rd'; GRANT REPLICATION SLAVE ON *.* TO repl@`%`; CREATE USER IF NOT EXISTS test@`%` IDENTIFIED BY 'P@ssw0rd'; GRANT ALL ON *.* TO test@`%`; EOF $ docker run --rm -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=P@ssw0rd \ -v ${PWD}/initdb.d:/docker-entrypoint-initdb.d \ mysql --gtid-mode=ON --log-bin --server-id=100 --enforce-gtid-consistency=ON |
また、オンプレミス側にもクライアントをインストールしておきます。
|
1 2 3 4 5 6 7 8 9 |
$ yum -y install https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm $ yum -y install mysql-shell $ mysqlsh --sql -uroot -pP@ssw0rd -h127.0.0.1 --sql -e "show databases" Database information_schema mysql performance_schema sys world |
MySQL Shellを使用したデータエクスポート
ドキュメントに紹介されている通り、MySQL ShellのdumpInstance()/loadDump()を使用し、ベースとなるデータをMDSにインポートしていきます。
なお、OCI Object Storageへダンプファイルを送信する場合、OCI CLI構成ファイルを適切に設定しておく必要があります。
|
1 2 |
$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)" $ oci setup config |
dumpInstanceの実行結果は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
$ mysqlsh -uroot -pP@ssw0rd -h127.0.0.1 MySQL JS> util.dumpInstance("OnpremiseBackup", {osBucketName: "test-bkt", osNamespace: "nro5qchj5wtp", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]}); Acquiring global read lock Global read lock acquired All transactions have been started Locking instance for backup Global read lock has been released Checking for compatibility with MySQL Database Service 8.0.22 NOTE: User 'root'@'%' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, APPLICATION_PASSWORD_ADMIN, AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, RESOURCE_GROUP_ADMIN, RESOURCE_GROUP_USER, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN, XA_RECOVER_ADMIN) removed NOTE: User 'root'@'localhost' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, APPLICATION_PASSWORD_ADMIN, AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, RESOURCE_GROUP_ADMIN, RESOURCE_GROUP_USER, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN, XA_RECOVER_ADMIN, PROXY) removed NOTE: Database world had unsupported ENCRYPTION option commented out Compatibility issues with MySQL Database Service 8.0.22 were found and repaired. Please review the changes made before loading them. Writing global DDL files Writing users DDL Writing DDL for table `world`.`city` Writing DDL for schema `world` Writing DDL for table `world`.`country` Writing DDL for table `world`.`countrylanguage` Preparing data dump for table `world`.`city` Data dump for table `world`.`city` will be chunked using column `ID` Preparing data dump for table `world`.`country` Data dump for table `world`.`country` will be chunked using column `Code` Preparing data dump for table `world`.`countrylanguage` Data dump for table `world`.`countrylanguage` will be chunked using column `CountryCode` Running data dump using 4 threads. NOTE: Progress information uses estimated values and may not be accurate. Data dump for table `world`.`city` will be written to 1 file Data dump for table `world`.`country` will be written to 1 file Data dump for table `world`.`countrylanguage` will be written to 1 file 1 thds dumping - 2200% (5.30K rows / ~241 rows), 1.39K rows/s, 49.62 KB/s uncompressed, 0.00 B/s compressed Duration: 00:00:01s Schemas dumped: 1 Tables dumped: 3 Uncompressed data size: 194.62 KB Compressed data size: 62.42 KB Compression ratio: 3.1 Rows written: 5302 Bytes written: 62.42 KB Average uncompressed throughput: 102.79 KB/s Average compressed throughput: 32.97 KB/s |
Acquiring global read lock～から始まる箇所を見てわかるように、 dumpInstance() ではデフォルトで consistent: true が設定されているため、実行開始時点で Global Read Lock を取得します。
全てのバックアップスレッドのトランザクションが開始した時点で開放され、バックアップロックに切り替えられます。
トランザクションが開始するまでですので、通常ごく短期間ですが、DMLが停止するという点にご注意ください。
またバックアップロック中はDDLのみブロックされます。
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
consistent: [ true | false ] Enable (true) or disable (false) consistent data dumps by locking the instance for backup during the dump.
The default is true.
When true is set, the utility sets a global read lock using the FLUSH TABLES WITH READ LOCK statement.
The transaction for each thread is started using the statements SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ and START TRANSACTION WITH CONSISTENT SNAPSHOT.
When all threads have started their transactions, the instance is locked for backup and the global read lock is released.
CLIで確認すると、正常にバックアップが存在していました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$ oci os object list --bucket-name test-bkt --prefix OnpremiseBackup --limit 1 { "data": [ { "etag": null, "md5": "qI02zKbDNOlZr+eGgcxMfA==", "name": "OnpremiseBackup/@.done.json", "size": 381, "time-created": "2021-01-07T08:06:39.259000+00:00", "time-modified": null } ], "next-start-with": "OnpremiseBackup/@.json", "prefixes": [] } |
MySQL Shellを使用したデータインポート
すでにOCI上にエクスポートされたバックアップを使用し、loadDump()を使用してMDSにデータをインポートします。
この作業はClient用のComputeインスタンスから行います。
エクスポート時と同様に、この場合もOCI CLIの設定ファイルを作成する必要がありますのでご注意ください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
$ ssh opc@<Public IP> $ export LANG=ja_JP.UTF-8 $ mysqlsh mysqlx://adminUser:<password>@192.168.1.10:33306 MySQL JS> util.loadDump("OnpremiseBackup", {osBucketName: "test-bkt", osNamespace: "nro5qchj5wtp", threads: 4, progressFile: "mds-load-progress.log"}) Loading DDL and Data from OCI ObjectStorage bucket=test-bkt, prefix='OnpremiseBackup' using 4 threads. Opening dump... Target is MySQL 8.0.22-u4-cloud. Dump was produced from MySQL 8.0.22 Fetching dump data from remote location... Fetching 3 table metadata files for schema `world`... Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `world` [Worker000] Executing DDL script for `world`.`country` [Worker001] Executing DDL script for `world`.`city` [Worker002] Executing DDL script for `world`.`countrylanguage` [Worker000] world@country@@0.tsv.zst: Records: 239 Deleted: 0 Skipped: 0 Warnings: 0 [Worker001] world@countrylanguage@@0.tsv.zst: Records: 984 Deleted: 0 Skipped: 0 Warnings: 0 [Worker003] world@city@@0.tsv.zst: Records: 4079 Deleted: 0 Skipped: 0 Warnings: 0 Executing common postamble SQL 3 chunks (5.30K rows, 194.62 KB) for 3 tables in 1 schemas were loaded in 1 sec (avg throughput 194.62 KB/s) 0 warnings were reported during the load. MySQL JS> \sql Switching to SQL mode... Commands end with ; MySQL SQL> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 5 rows in set (0.0015 sec) |
なお、MySQL Shellのドキュメントに記載されている通り、loadDump()ではダンプしたバックアップに含まれるGTIDをインポート先に自動的に適用しません。
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
The dump loading utility does not automatically apply the gtid_executed GTID set from the source MySQL instance on the target MySQL instance.
The GTID set is included in the dump metadata from MySQL Shell’s instance dump utility, schema dump utility, or table dump utility, as the gtidExecuted field in the @.json dump file.
To apply these GTIDs on the target MySQL instance for use with replication, from MySQL Shell 8.0.22, use the updateGtidSet option to either append them to or replace the gtid_purged GTID set,
depending on the release of the target MySQL instance.
This is not currently supported on MySQL DB System due to a permissions restriction.
In MySQL Shell 8.0.21, the GTID set can be imported manually, though this is not supported on MySQL DB System.
ダンプ実行時のGTID_EXECUTEDはダンプデータセット内の @.json ファイルに含まれますので確認できます。
|
1 2 |
$ oci os object get --bucket-name test-bkt --name OnpremiseBackup/@.json --file - | grep -i gtidExecuted "gtidExecuted": "31ce7536-515c-11eb-92f6-0242ac110003:1-20", |
MDSの管理ユーザは、現状のところ SUPER や SYSTEM_VARIABLES_ADMIN 権限が付与されておらず、SET GLOBAL GTID_PURGED .. を実行することができませんが、sys.set_gtid_purged() コマンドで同等の操作を実施することが可能です。
|
1 2 3 4 5 6 7 8 |
MySQL SQL> select @@gtid_purged; +------------------------------------------+ | @@gtid_purged | +------------------------------------------+ | baadae1f-514f-11eb-9465-020017010dfa:1-2 | +------------------------------------------+ MySQL SQL> call sys.set_gtid_purged("baadae1f-514f-11eb-9465-020017010dfa:1-2,31ce7536-515c-11eb-92f6-0242ac110003:1-20"); Query OK, 0 rows affected (0.0038 sec) |
VPN接続について
オンプレミスの環境からOCIのSubnetへ接続するためには、VPN接続が必要となります。
MDSからソースデータベースとなるオンプレミスのMySQLへ接続するためにも同様にこれは必須です。
今回はOCI Marketplace上のOpenVPNサーバを使用しました。
この OpenVPN Access Server は 2接続まで 無償で利用が可能です。
https://openvpn.net/access-server/pricing/
ここでは詳しい構築方法については触れませんが、ドキュメントに構築の詳しい手順が記載されておりますので、もし検証される場合は適宜ご確認ください。
移行の作業ではVPN接続が完了しており、MDSからオンプレミスMySQLの3306ポートへアクセス可能な事を前提とします。
インバウンドレプリケーションとは
MDSでは、外部のMySQLをソース、MDSをレプリカとした非同期レプリケーションを構成することが可能です。
インバウンドレプリケーションでは、レプリケーションを開始する際にMDSにチャネル(いわゆるCHANGE MASTER TOで設定する内容相当)を定義する必要があります。
もちろんGUIコンソールからも追加が可能ですが、今回はterraformの設定ファイルで定義しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
resource "oci_mysql_channel" "mds_chan" { source { hostname = var.channel_source_hostname username = var.channel_source_username password = var.channel_source_password source_type = "MYSQL" port = var.channel_source_port ssl_mode = "DISABLED" } target { db_system_id = oci_mysql_mysql_db_system.mds.id target_type = "DBSYSTEM" channel_name = "ch" } display_name = "Test Replicaiton Channel" is_enabled = var.channel_enabled } |
var.channel_enabled は variables.tf で “false” に設定しておりますので、レプリケーション自体はこの時点で開始していません。
レプリケーションの開始
レプリケーションを開始します。
|
1 2 |
$ CHANNEL_ID=$(oci mysql channel list | jq -r '.data[0].id') $ oci mysql channel update-from-mysql --channel-id ${CHANNEL_ID} --is-enabled true |
MDSでステータスを確認すると、非同期レプリケーションが開始されています。
|
1 2 3 |
$ mysqlsh mysqlx://adminUser:<password>@192.168.1.10:33306 --sql -e "show replica status\G" | grep -i "Running:" Replica_IO_Running: Yes Replica_SQL_Running: Yes |
オンプレ側でデータの更新をしてみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ mysql -t -uroot -p -h127.0.0.1 -P3306 <<-EOSQL CREATE DATABASE IF NOT EXISTS test; CREATE TABLE IF NOT EXISTS test.t (i int primary key auto_increment); INSERT INTO test.t values (null); INSERT INTO test.t values (null); INSERT INTO test.t values (null); SELECT * FROM test.t; EOSQL +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ |
MDSでデータを確認することができます。
|
1 2 3 4 5 6 7 8 |
MySQL SQL> select * from test.t; +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ |
以上でオンプレミスのMySQLとMDSのデータは継続的に同期されます。
あとはアプリケーションの接続先の切り替えなどを行えばMDSへの移行は完了です。
まとめ
各環境でのOCI CLIの実行準備さえできていれば、スムーズな移行ができるものと思います。
今回はOpenVPNサーバを使用しましたが、オンプレミス環境が高トラフィックであったり、データ量が非常に多いなどの状況ではよりパフォーマンスのよい OCI VPN Service や、Fast Connect を準備するのがよいでしょう。
まずは、小規模な環境のレプリカとしてMDSのご利用を初めてみてはいかがでしょうか。
