OCIのMySQLでレプリケーションを使用してみました！

2024年11月28日 Oracle Cloud

1 はじめに
2 レプリケーションとは
3 作成してみた
4 おわりに

はじめに

みなさん、こんにちは。
Oracle Cloud Infrastructure 検証チームです。

今回は、OCIの入門編チュートリアルにもある
MySQL Database Serviceでレプリケーションを使用するを実際に検証し、
構築してみましたのでご紹介したいと思います。

以前にも、OCIチュートリアルを検証しています。
OCIでMySQL Database Service(MDS)を作成してみました（クラウドでMySQL Databaseを使う）
OCIのMySQLでHeatWave(高速分析)を試してみました！（MySQLで高速分析を体験する）
OCIのMySQLで高可用性構成を試してみました！（クラウドでMySQL Databaseを高可用性構成で使う）
OCIのMySQLでリードレプリカを構成してみました！（MySQL Databese Serviseでリードレプリカを構成する）
まだ見てないという方は先にそちらもご覧ください。

レプリケーションとは

レプリケーションとは、
ソースサーバーでの更新内容をレプリカサーバーに伝播させる機能のことです。
主に高可用性や負荷分散を実現するために利用されます。

MDSの場合、レプリケーションを利用してソースになることもレプリカになることも可能です。
MDSがソースになる場合のレプリケーションをアウトバウンドレプリケーション、
MDSがレプリカになる場合のレプリケーションをインバウンドレプリケーションと言います。

インバウンドレプリケーションでは、以下の制限事項があります。

行ベースレプリケーションのみサポート
非同期レプリケーションのみサポート
シングルソースレプリケーションのみサポート
mysqlスキーマに対する更新処理はレプリケーションできない

今回、構築検証するレプリケーションの構成図は以下のようになります。

作成してみた

前提として、今回使用するVCNとインスタンスは、すでに作成してあります。

今回の検証の流れは以下の通りです。

MDSの作成
レプリケーション用ユーザーの作成
ソース用MDSのデータをダンプし、レプリカ用MDSへインポート
レプリケーションチャンネルの設定
レプリケーションの動作確認
レプリケーションフィルターの設定
レプリケーションフィルターの動作確認

MDSの作成

まずはソースとなるMDSを作成していきます。
MDSの作成につきましては以前紹介した
クラウドでMySQL Databaseを使うを参考に「source」という名前でソース用のMDSを作成します。
「2. MDSの作成」だけでなく、「3. セキュリティリストの修正(イングレス・ルールの追加)」、
「4. MySQLクライアントのインストール」、「5. 作成したMDSの確認」まで実行し、worldデータベース、world_xデータベースも作成しておきます。

つぎにレプリカとなるMDSを作成していきます。
こちらにつきましても、MDSの作成は以前紹介した
クラウドでMySQL Databaseを使うを参考に「replica」という名前でレプリカ用のMDSを作成します。
レプリカにつきましては「2. MDSの作成」までの実施で問題ありません。

レプリケーション用ユーザーの作成

レプリケーション用のユーザーを作成し、REPLICATION SLAVE権限を付与します。
以下の操作は、ソース用のMDS、レプリカ用のMDSの両方で実行します。

rplユーザーを作成しREPLICATION SLAVE権限を付与

mysql> CREATE USER 'rpl'@'%' IDENTIFIED BY 'MySQL_8.0' REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE on *.* to 'rpl'@'%';
Query OK, 0 rows affected (0.00 sec)

これでレプリケーション用ユーザーの作成が完了いたしました！

ソース用MDSのデータをダンプし、レプリカ用MDSへインポート

ソース用のMDSにMySQL Shellを使用して接続し、インスタンスダンプユーティリティを使用してデータをダンプします。
MDSにデータをインポートするためには、MDSとの互換性を確保したダンプファイルを取得する必要があるため、ocimdsオプションをtrueにしてデータをダンプします。
また、dryRunオプションをtrueにすることでダンプは取得せずにMDSとの互換性チェックの結果を表示できるため、
まずはこれらオプションをtrueにしてインスタンスダンプユーティリティを実行します。

ソース用のMDSにMySQL Shellを使用して接続

[root@testvm1 opc]# mysqlsh -u root -p -h source.sub10080034481.tutorialvcn.oraclevcn.com
Please provide the password for 'root@source.sub10080034481.tutorialvcn.oraclevcn.com': ********
Save password for 'root@source.sub10080034481.tutorialvcn.oraclevcn.com'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.38

Copyright (c) 2016, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@source.sub10080034481.tutorialvcn.oraclevcn.com'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 22
Server version: 8.0.39-cloud MySQL Enterprise - Cloud
No default schema selected; type \use <schema> to set one.
MySQL source.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS >

インスタンスダンプユーティリティを使用してデータをダンプ(テスト実行)

 MySQL source.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS > util.dumpInstance("/home/opc/mds", {dryRun: true, ocimds: true})
dryRun enabled, no locks will be acquired and no files will be created.
Acquiring global read lock
Global read lock acquired
Initializing - done
2 out of 7 schemas will be dumped and within them 7 tables, 0 views.
2 out of 10 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
NOTE: When migrating to MySQL HeatWave Service, please always use the latest available version of MySQL Shell.
Checking for compatibility with MySQL HeatWave Service 8.0.38
WARNING: User 'root'@'%' has a grant statement on an object which is not included in the dump (REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `root`@`%`)
WARNING: User 'root'@'%' has a grant statement on an object which is not included in the dump (REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_audit`.* FROM `root`@`%`)
WARNING: User 'root'@'%' has a grant statement on an object which is not included in the dump (REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `sys`.* FROM `root`@`%`)
WARNING: User 'root'@'%' has a grant statement on a role `administrator`@`%` which is not included in the dump (GRANT `administrator`@`%` TO `root`@`%` WITH ADMIN OPTION)
ERROR: User 'root'@'%' is granted restricted privileges: PROXY, SHOW_ROUTINE (fix this with 'strip_restricted_grants' compatibility option)
NOTE: Database `world_x` had unsupported ENCRYPTION option commented out
NOTE: Database `world` had unsupported ENCRYPTION option commented out
Compatibility issues with MySQL HeatWave Service 8.0.38 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL.
Validating MySQL HeatWave Service compatibility - done
Util.dumpInstance: While 'Validating MySQL HeatWave Service compatibility': Compatibility issues were found (MYSQLSH 52004)
MySQL source.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS

ERRORが出力されていますが、いずれもMDSでユーザーに付与できない権限が付与されたユーザーが存在することが原因です。
今回の手順では、ユーザー情報はインポートする必要がないため、表示されているメッセージ「fix this with ‘strip_restricted_grants’ compatibility option」に従い、compatibilityに’strip_restricted_grants’を指定して回避します。

先ほど実行したコマンドにcompatibilityオプションを追加し、’strip_restricted_grants’を指定します。
また、dryRunオプションをfalseにして実行します。

インスタンスダンプユーティリティを使用してデータをダンプ

MySQL source.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS > util.dumpInstance("/home/opc/mds", {ocimds: true, compatibility: ["strip_restricted_grants"]})
Acquiring global read lock
Global read lock acquired
Initializing - done
2 out of 7 schemas will be dumped and within them 7 tables, 0 views.
2 out of 10 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
NOTE: When migrating to MySQL HeatWave Service, please always use the latest available version of MySQL Shell.
Checking for compatibility with MySQL HeatWave Service 8.0.38
WARNING: User 'root'@'%' has a grant statement on an object which is not included in the dump (REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `root`@`%`)
WARNING: User 'root'@'%' has a grant statement on an object which is not included in the dump (REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_audit`.* FROM `root`@`%`)
WARNING: User 'root'@'%' has a grant statement on an object which is not included in the dump (REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `sys`.* FROM `root`@`%`)
WARNING: User 'root'@'%' has a grant statement on a role `administrator`@`%` which is not included in the dump (GRANT `administrator`@`%` TO `root`@`%` WITH ADMIN OPTION)
NOTE: User 'root'@'%' had restricted privileges (PROXY, SHOW_ROUTINE) removed
NOTE: Database `world_x` had unsupported ENCRYPTION option commented out
NOTE: Database `world` had unsupported ENCRYPTION option commented out
Compatibility issues with MySQL HeatWave Service 8.0.38 were found and repaired. Please review the changes made before loading them.
Validating MySQL HeatWave Service compatibility - done
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
100% (10.84K rows / ~10.80K 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: 7
Uncompressed data size: 514.21 KB
Compressed data size: 188.59 KB
Compression ratio: 2.7
Rows written: 10843
Bytes written: 188.59 KB
Average uncompressed throughput: 514.21 KB/s
Average compressed throughput: 188.59 KB/s
MySQL source.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS

“/home/opc/mds”配下にダンプされたファイルが出力されていることを確認後、
ダンプロードユーティリティをレプリカサーバーで実行するために、MySQL Shellを使ってレプリカサーバーに接続します。

ダンプファイルの確認

[root@testvm1 opc]# ls mds
@.done.json                      world@countrylanguage@@0.tsv.zst.idx world_x@countryinfo@@0.tsv.zst.idx
@.json                           world@countrylanguage.json           world_x@countryinfo.json
@.post.sql                       world@countrylanguage.sql            world_x@countryinfo.sql
@.sql                            world@country.sql                    world_x@country.json
@.users.sql                      world.json                           world_x@countrylanguage@@0.tsv.zst
world@city@@0.tsv.zst            world.sql                            world_x@countrylanguage@@0.tsv.zst.idx
world@city@@0.tsv.zst.idx        world_x@city@@0.tsv.zst              world_x@countrylanguage.json
world@city.json                  world_x@city@@0.tsv.zst.idx          world_x@countrylanguage.sql
world@city.sql                   world_x@city.json                    world_x@country.sql
world@country@@0.tsv.zst         world_x@city.sql                     world_x.json
world@country@@0.tsv.zst.idx     world_x@country@@0.tsv.zst           world_x.sql
world@country.json               world_x@country@@0.tsv.zst.idx
world@countrylanguage@@0.tsv.zst world_x@countryinfo@@0.tsv.zst

ソース用のMDSにMySQL Shellを使用して接続

[root@testvm1 opc]# mysqlsh -u root -p -h replica.sub10080034481.tutorialvcn.oraclevcn.com
Please provide the password for 'root@replica.sub10080034481.tutorialvcn.oraclevcn.com': ********
Save password for 'root@replica.sub10080034481.tutorialvcn.oraclevcn.com'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.38

Copyright (c) 2016, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@replica.sub10080034481.tutorialvcn.oraclevcn.com'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 23
Server version: 8.0.39-cloud MySQL Enterprise - Cloud
No default schema selected; type \use <schema> to set one.
MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS >

ダンプロードユーティリティを使って、レプリカサーバーにデータをインポートします。
今回の手順ではユーザー情報はインポートする必要がないため、loadUsersオプションはfalseに設定して実行します。
また、ダンプ取得時のソースサーバーのGTIDをレプリカサーバーに設定するためにupdateGtidSetオプションを”append”にして実行します。

レプリカサーバーにデータをインポート

MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS > util.loadDump("/home/opc/mds", {loadUsers: false, updateGtidSet: "append"});
Loading DDL and Data from '/home/opc/mds' using 4 threads.
Opening dump...
Target is MySQL 8.0.39-cloud (MySQL HeatWave Service). Dump was produced from MySQL 8.0.39-cloud
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
2 thds loading / 100% (514.21 KB / 514.21 KB), 1.02 MB/s, 5 / 7 tables done
Recreating indexes - done
Executing common postamble SQL
Appending dumped gtid set to GTID_PURGED
7 chunks (10.84K rows, 514.21 KB) for 7 tables in 2 schemas were loaded in 1 sec (avg throughput 514.21 KB/s)
0 warnings were reported during the load.
MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS >

MySQL ShellをSQLモードに変更し、レプリカサーバーにデータがインポートされたことを確認します。

SQLモードに変更しデータのインポートを確認

MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.

MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl SQL > SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_audit        |
| performance_schema |
| sys                |
| world              |
| world_x            |
+--------------------+
7 rows in set (0.0013 sec)

MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl SQL > USE world;
Default schema set to `world`.
Fetching global names, object names from `world` for auto-completion... Press ^C to stop.

MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl world SQL > SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.0030 sec)

MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl world SQL > USE world_x;
Default schema set to `world_x`.
Fetching global names, object names from `world_x` for auto-completion... Press ^C to stop.

MySQL replica.sub10080034481.tutorialvcn.oraclevcn.com:3306 ssl world_x SQL > SHOW TABLES;
+-------------------+
| Tables_in_world_x |
+-------------------+
| city              |
| country           |
| countryinfo       |
| countrylanguage   |
+-------------------+
4 rows in set (0.0018 sec)

無事にソースMDSのデータをレプリカMDSへインポートすることができました！

レプリケーションチャネルの設定

ソースMDSからレプリカMDSへ接続するためのレプリケーションチャネルの設定を行なっていきます。

コンソールからレプリカ用のMDS(replica)の詳細にアクセス後、左下のチャネルをクリックします。
その後、チャネルの作成をクリックします。

表示された「チャネルの作成」画面で、以下の項目を入力し「チャネルの作成」をクリックします。

チャネルが作成されたことを確認します。

これでレプリケーションチャネルが作成されたことが確認できました！

レプリケーションの動作確認

ソース用のMDSで更新処理を実行し、レプリカ用のMDSに反映されることを確認します。

ソース用のMDSにmysqlコマンドラインクライアントを使用して接続し、rplデータベース、rpl.testテーブルを作成し、データをINSERTします。
その後、レプリカ用のMDSでrpl.testテーブルが存在することを確認し、INSERTされたデータがSELECTできることを確認します。

レプリカ用のMDSでrplデータベースを確認する。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_audit        |
| performance_schema |
| sys                |
| world              |
| world_x            |
+--------------------+
7 rows in set (0.01 sec)

現時点では未作成のため、rplデータベースは存在していません。

ソース用のMDSでrplデータベース、rpl.testテーブルを作成し、データをINSERTする。

mysql> CREATE DATABASE rpl;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE rpl.test(id int AUTO_INCREMENT, col1 CHAR(10), PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO rpl.test VALUES(1, "TEST");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM rpl.test;
+----+------+
| id | col1 |
+----+------+
| 1  | TEST |
+----+------+
1 row in set (0.00 sec)

ソース用のMDSでrpl.testテーブルが作成され、データが存在することを確認できました。

レプリカ用のMDSでデータが伝播していることを確認する

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_audit        |
| performance_schema |
| rpl                |
| sys                |
| world              |
| world_x            |
+--------------------+
8 rows in set (0.00 sec)

mysql> USE rpl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;
+---------------+
| Tables_in_rpl |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM rpl.test;
+----+------+
| id | col1 |
+----+------+
| 1  | TEST |
+----+------+
1 row in set (0.00 sec)

ソースMDSで作成した内容がレプリカMDSに伝播していることが確認できました！

レプリケーションフィルターの設定

レプリケーションフィルター(チャネルフィルター)を設定し、特定のデータベース/テーブルのみレプリケーションできることを確認します。
まずはレプリケーションフィルターを設定します。

コンソールからレプリカ用のMDS(Replica)の詳細にアクセス後、左下のチャネルをクリックします。その後、TestChannelをクリックします。

編集をクリックし、「チャネル・フィルタ」部分の「タイプ」のプルダウンからREPLICATE_DO_DBを、「値」にworldと入力し、変更の保存をクリックします。

レプリケーションフィルターの動作確認

ひとつ前のステップで設定したレプリケーションフィルター(チャネルフィルター)の動作を確認します。
REPLICATE_DO_DBにworldデータベースを指定しているため、worldデータベースに対する更新のみがレプリケーションされることを確認します。
まずはrpl.testテーブルに更新を加え伝播されないことを確認します。

ソース用のMDSでrpl.testテーブルを更新します。

mysql> UPDATE rpl.test SET col1='FILTER' WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM rpl.test;
+----+--------+
| id | col1   |
+----+--------+
| 1  | FILTER |
+----+--------+
1 row in set (0.00 sec)

レプリカ用のMDSで上記更新が反映されていないことを確認します。

mysql> SELECT * FROM rpl.test;
+----+------+
| id | col1 |
+----+------+
| 1  | TEST |
+----+------+
1 row in set (0.00 sec)

rplデータベースへの更新はレプリカMDSへ伝播しないことを確認できました。
次にworldデータベースへ更新を行ないレプリカMDSへ伝播されることを確認いたします。

レプリカ用のMDSで更新前のworld.cityテーブルを確認します。

mysql> SELECT * FROM world.city WHERE ID=1538;
+------+------+-------------+----------+------------+
| ID   | Name | CountryCode | District | Population |
+------+------+-------------+----------+------------+
| 1538 | Kobe | JPN         | Hyogo    | 1425139    |
+------+------+-------------+----------+------------+
1 row in set (0.00 sec)

ソース用のMDSでworld.cityテーブルを更新します。

mysql> SELECT * FROM world.city WHERE ID=1538;
+------+------+-------------+----------+------------+
| ID   | Name | CountryCode | District | Population |
+------+------+-------------+----------+------------+
| 1538 | Kobe | JPN         | Hyogo    | 1425139    |
+------+------+-------------+----------+------------+
1 row in set (0.00 sec)

mysql> UPDATE world.city SET Population=1506516 WHERE ID=1538;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM world.city WHERE ID=1538;
+------+------+-------------+----------+------------+
| ID   | Name | CountryCode | District | Population |
+------+------+-------------+----------+------------+
| 1538 | Kobe | JPN         | Hyogo    | 1506516    |
+------+------+-------------+----------+------------+
1 row in set (0.00 sec)

レプリカ用のMDSで上記更新が反映されていることを確認します。

mysql> SELECT * FROM world.city WHERE ID=1538;
+------+------+-------------+----------+------------+
| ID   | Name | CountryCode | District | Population |
+------+------+-------------+----------+------------+
| 1538 | Kobe | JPN         | Hyogo    | 1506516    |
+------+------+-------------+----------+------------+
1 row in set (0.00 sec)

レプリケーションフィルターが正常に機能していることが確認できました！