[Oracle Cloud] MDS 同士の Inbound Replication を試してみた

# はじめに

[前の記事](https://qiita.com/sugimount/items/8d9c1441fa2307403feb)では、Compute Instance と MySQL Database Service 間のレプリケーションを試してみました。今回の記事は、MySQL Database Service 同時のレプリケーションを試してみます。

Document には明記されていない構成ですが、技術的にできるかどうか試してみました。手順を備忘録的に残しておきます。



# MDS 2台用意

MySQL Database Service で、MySQL インスタンスを2台用意します

![1611398778368.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/86ebe402-0c42-f014-3480-239e7c53dec1.png)



# Source : テストデータ作成

作った Source の MySQL にテストデータを作成します
`sugitest` スキーマ作成

```
mysql> create database sugitest;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sugitest           |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>
```



`sample` テーブルの作成

```
use sugitest;

CREATE TABLE sample(
 id INT(11) NOT NULL AUTO_INCREMENT,
 value INT(5) NOT NULL DEFAULT 0,
 PRIMARY KEY (id)
);

INSERT INTO sample(value) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
```



# Source : レプリケーションユーザーの設定

ユーザー作成

```
CREATE USER rpluser001@'%' IDENTIFIED BY 'Rpl001#!' REQUIRE SSL;
```



REPLICATION SLAVE の権限付与

```
GRANT REPLICATION SLAVE on *.* to rpluser001@'%';
```



# Source : Dump ファイルを Export

MDS を作成した時に指定したユーザーで、MySQL Shell を使って接続

```
mysqlsh admin@mdssource01.mysqlsubnet01.vcn.oraclevcn.com
```



何も考えず、MDS インスタンス全体の Dump を取得するとエラーになります

```
 MySQL  10.0.12.41:3306 ssl  JS > util.dumpInstance("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]})
Acquiring global read lock
NOTE: Error acquiring global read lock: MySQL Error 1227 (42000): Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation
WARNING: The current user lacks privileges to acquire a global read lock using 'FLUSH TABLES WITH READ LOCK'. Falling back to LOCK TABLES...
Locking instance for backup
ERROR: Could not acquire backup lock: MySQL Error 1227 (42000): Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation
ERROR: Unable to acquire global read lock neither table read locks.
Global read lock has been released
Util.dumpInstance: Unable to lock tables: MySQL Error 1227 (42000): Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation (RuntimeError)
 MySQL  10.0.12.41:3306 ssl  JS >
```



`BACKUP_ADMIN` 権限を付与しろ言われますが、MDS では付与出来ません

```
mysql> grant BACKUP_ADMIN on sugitest.* to sugi01@'%';
ERROR 3619 (HY000): Illegal privilege level specified for BACKUP_ADMIN
mysql>
```



回避策は、`"consistent":"false"` を指定して一貫性のないバックアップを取得します。一貫性のないバックアップなので、本番稼働中の MDS から直接指定するのは非推奨です。本番稼働している場合は、Snapshot から新たな MDS を作成して、ワークロードが動いていない状態で一貫性のない dumpInstance をするといいでしょう。

なお、dumpInstance は、MDS 新規作成時に指定したユーザーでないと、エラーになるので注意してください

```
util.dumpInstance("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"], "consistent":"false"})
```



実行例

```
 MySQL  10.0.12.41:3306 ssl  JS > util.dumpInstance("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"], "consistent":"false"})
Gathering information - done
WARNING: The dumped value of gtid_executed is not guaranteed to be consistent
Checking for compatibility with MySQL Database Service 8.0.23
NOTE: User 'admin'@'%' had restricted privilege (PROXY) removed
NOTE: User 'ociadmin'@'127.0.0.1' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, PROXY, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: User 'ocirpl'@'%' had restricted privileges (GROUP_REPLICATION_ADMIN, RELOAD, REPLICATION_SLAVE_ADMIN, SERVICE_CONNECTION_ADMIN, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN) removed
NOTE: User 'sugi01'@'%' had restricted privilege (ALL PRIVILEGES) removed
NOTE: Database sugitest had unsupported ENCRYPTION option commented out
Compatibility issues with MySQL Database Service 8.0.23 were found and repaired. Please review the changes made before loading them.
Writing global DDL files
Writing users DDL
Preparing data dump for table `sugitest`.`sample`
Writing DDL for schema `sugitest`
Data dump for table `sugitest`.`sample` will be chunked using column `id`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for table `sugitest`.`sample`
Data dump for table `sugitest`.`sample` will be written to 1 file
1 thds dumping - 100% (10 rows / ~10 rows), 5.00 rows/s, 22.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 42 bytes
Compressed data size: 0 bytes
Compression ratio: 42.0
Rows written: 10
Bytes written: 0 bytes
Average uncompressed throughput: 22.08 B/s
Average compressed throughput: 0.00 B/s
 MySQL  10.0.12.41:3306 ssl  JS >
```



# Replica : Dumpfile を Import

Replica 用 MDS に Dumpfile を Import します。MySQL Shell で接続

```
mysqlsh admin@mdsreplica01.mysqlsubnet01.vcn.oraclevcn.com
```



Dumpfile を Import

```
util.loadDump("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4})
```



実行例

```
 MySQL  mdsreplica01.mysqlsubnet01.vcn.oraclevcn.com:3306 ssl  JS > util.loadDump("MDStoMDS1", {osBucketName: "mysqldump", osNamespace: "orasejapan", threads: 4})
Loading DDL and Data from OCI ObjectStorage bucket=mysqldump, prefix='MDStoMDS1' using 4 threads.
Opening dump...
Target is MySQL 8.0.23-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.23-cloud
Fetching dump data from remote location...
Fetching 1 table metadata files for schema `sugitest`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `sugitest`
[Worker000] Executing DDL script for `sugitest`.`sample`
[Worker001] sugitest@sample@@0.tsv.zst: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL

1 chunks (10 rows, 42 bytes) for 1 tables in 1 schemas were loaded in 1 sec (avg throughput 42.00 B/s)
0 warnings were reported during the load.
 MySQL  mdsreplica01.mysqlsubnet01.vcn.oraclevcn.com:3306 ssl  JS >
```



GTID ベースのレプリケーションでは、どこまでトランザクションをレプリケーションしたかを、GTID で管理しています。MySQL Shell のユーティリティでは、Replica 側で、GTID 関連の設定が必要です。
Object Storage 内の json メタデータから、`gtidExecuted`を取得してメモします。

Object Storage の`@.json` ファイルをダウンロードします。

![1611402456110.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/caca09cf-9c4b-a00f-04da-b9336497f605.png)



この `@.json` ファイルの中身は以下。この中の `gtidExecuted` をメモっておく

```
{
    "dumper": "mysqlsh Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))",
    "version": "1.0.2",
    "origin": "dumpInstance",
    "schemas": [
        "sugitest"
    ],
    "basenames": {
        "sugitest": "sugitest"
    },
    "users": [
        "'admin'@'%'",
        "'administrator'@'%'",
        "'ociadmin'@'127.0.0.1'",
        "'ocirpl'@'%'",
        "'rpluser001'@'%'",
        "'sugi01'@'%'"
    ],
    "defaultCharacterSet": "utf8mb4",
    "tzUtc": true,
    "bytesPerChunk": 64000000,
    "user": "admin",
    "hostname": "mysql-client01",
    "server": "pzrhc2uodjtnf8yi",
    "serverVersion": "8.0.23-cloud",
    "gtidExecuted": "e37d8e20-5d61-11eb-9d40-0200170045b9:1-10",
    "gtidExecutedInconsistent": true,
    "consistent": false,
    "mdsCompatibility": true,
    "begin": "2021-01-23 11:38:01"
}
```



メモった値を使って、gtid_purged を行います。Replica に　MySQL Client (MySQL Shell じゃないよ) を使って接続します。次にメモった値を使ってコマンドを発行します

```
mysql> call sys.set_gtid_purged("e37d8e20-5d61-11eb-9d40-0200170045b9:1-10");
Query OK, 0 rows affected (0.00 sec)

mysql>
```



確認

```
mysql> select @@gtid_purged;
+-------------------------------------------+
| @@gtid_purged                             |
+-------------------------------------------+
| e37d8e20-5d61-11eb-9d40-0200170045b9:1-10 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

```



memo : エラーになったとき

```
mysql> call sys.set_gtid_purged("0e1a6a53-5cac-11eb-8d54-02001701e97e:1-10");
ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
```



memo : 次の gtid_purged が空白じゃないとエラーになる

```
mysql> show global variables like 'GTID%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                              |
+----------------------------------+------------------------------------------------------------------------------------+
| gtid_executed                    | 0e1a6a53-5cac-11eb-8d54-02001701e97e:1-5,
cbcefcc6-5cb0-11eb-8761-020017004a1e:1-7 |
| gtid_executed_compression_period | 0                                                                                  |
| gtid_mode                        | ON                                                                                 |
| gtid_owned                       |                                                                                    |
| gtid_purged                      | 0e1a6a53-5cac-11eb-8d54-02001701e97e:1-3                                           |
+----------------------------------+------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

mysql>
```



# Replica : Channel の作成

OCI Console 上で、Create Channel を行います

![1611402642850.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/9f51c88a-f8b0-b7a9-0e14-b5d6a029677b.png)



Source で作成したレプリケーション用のユーザーを指定します

![1611402762146.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/aea8da57-f7cf-422e-4452-2cffdab61a5a.png)



Create Channel

![1611402791203.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/2d17f1c4-61a9-dd4d-58c9-cac023fc1c73.png)



Creating になりますが、一定時間後 Active になります

![1611402863099.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/c587341e-1947-87dd-5ab9-22bbed042f10.png)



# レプリケーション確認

レプリケーションされているか確認するために、Source にテストデータを入れてみます

```
mysql -h mdssource01.mysqlsubnet01.vcn.oraclevcn.com -u admin -p
```



現在のデータ

```
mysql> select * from sugitest.sample;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
|  4 |     4 |
|  5 |     5 |
|  6 |     6 |
|  7 |     7 |
|  8 |     8 |
|  9 |     9 |
| 10 |    10 |
+----+-------+
10 rows in set (0.00 sec)

mysql>
```



データのインサート

```
INSERT INTO sugitest.sample(value) VALUES (11);
```



replica 先

```
mysql -h mdsreplica01.mysqlsubnet01.vcn.oraclevcn.com -u admin -p
```



Replica されている

```
mysql> select * from sugitest.sample;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
|  4 |     4 |
|  5 |     5 |
|  6 |     6 |
|  7 |     7 |
|  8 |     8 |
|  9 |     9 |
| 10 |    10 |
| 11 |    11 |
+----+-------+
11 rows in set (0.00 sec)

mysql>
```





# 参考URL

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html