Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@sugimount(sugi mount)

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

Last updated at Posted at 2021-01-23

はじめに

前の記事では、Compute Instance と MySQL Database Service 間のレプリケーションを試してみました。今回の記事は、MySQL Database Service 同時のレプリケーションを試してみます。

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

MDS 2台用意

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

1611398778368.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

この @.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

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

1611402762146.png

Create Channel

1611402791203.png

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

1611402863099.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

4
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?