More than 3 years have passed since last update.

[Oracle Cloud] Amazon Aurora から MDS へ Inbound Replication してみた

Last updated at 2021-01-31Posted at 2021-01-31

はじめに

前の記事では、Oracle Cloud の MySQL マネージドサービス (MySQL Database Service) 同士の、Inbound Replication を検証しました。今回の記事では、AWS の Aurora (MySQL互換) から、Oracle Cloud の MDS へ Inbound Replication への接続を検証してみます。

Oracle Cloud の Document には明記されていない構成ですが、自分の検証した環境では正常に Inbound Replication が出来たので、その手順を残しておきます。

(RDS for MySQL は未検証です)

前提

IPsec VPN で AWS の VPC と OCI の VCN 間で、Private 接続済み
AWS VPC : 10.1.0.0/16
OCI VCN : 10.0.0.0/16

Source : Security Group

Aurora に設定する Security Group を事前に用意しておきます。VPC と Oracle Cloud 間で 3306(MySQL) 接続を許可します。

Source : RDS Prameter groups

Oracle Cloud の MDS では、GTID ベースのレプリケーションのみサポートされているので、Aurora では GTID を有効にしないといけません。Aurora の GTID レプリケーションは、version 2.04 以降の MySQL 5.7 互換でサポートされています。

GTID-based replication is supported for MySQL 5.7-compatible clusters in Aurora MySQL version 2.04 and later. GTID-based replication isn't supported for MySQL 5.6-compatible clusters in Aurora MySQL version 1.

Aurora で、GTID を有効にするには、Parameter group の作成が必要です。5.7 をベースにして、GTID を有効化する Parameter group を作成します。

各パラメータを入れて、Create します。

Edit

gtid で検索して、enforce_gtid_consistency と gtid-mode を ON にします。

binlog_format を MIXEDから、ROW に変更

Save changes を押します。

Source : Aurora 作成

RDS のサービスで、Create database を押します。

各種パラメータ

MySQL 5.7 互換の最新
VPC の Private Subnet に設定
Security Group を適切なものを設定
RDS Public Access を無効
DB parameter は、GTID を有効にしたものを設定

Aurora が作成開始されます。Aurora に書き込み・読み込み用の Endpoint が生成されています。

2つの Endpoint の名前解決を確認します。書き込みも読み込みも両方とも、同一のIPアドレス 10.1.100.113 を指しています。

書き込み Endpoint

[opc@onprem-server01 ~]$ dig aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 3 IN CNAME aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 3 IN A 10.1.100.113

読み込み Endpoint

[opc@onprem-server01 ~]$ dig aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 1 IN CNAME aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 5 IN A 10.1.100.113

10.1.100.113 は、Aurora 配下の Primary Instance です。

Primary Instance の IP アドレスを確認

[opc@onprem-server01 ~]$ dig aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 5 IN A 10.1.100.113

RDS 以外のサーバーから、nc コマンドを使って、3306 ポートにアクセス確認をします。書き込みエンドポイント、読み込みエンドポイントの両方にアクセスしてみます。何やら文字列が返ってくれば、3306 ポートで通信出来ていることがわかります。

書き込み Endpoint

[opc@onprem-server01 ~]$ nc aurora-source01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com 3306
N
5.7.12-log|xl9%q���%gUIomysql_native_password^C
[opc@onprem-server01 ~]$

読み込み Endpoint

[opc@onprem-server01 ~]$ nc aurora-source01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com 3306
N
5.7.12-log0/x
i9b���q5(XHo#-qmysql_native_password
^C
[opc@onprem-server01 ~]$

実際に MySQL Client で接続します。

mysql -u admin -h aurora-source01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com -p

接続例

[opc@onprem-server01 ~]$ mysql -u admin -h aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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 '\h' for help. Type '\c' to clear the current input statement.

mysql>

GTID Mode が有効化されています

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed                    |           |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
9 rows in set (0.01 sec)

mysql>

binlog_format も ROW になっています。

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql>

Source : スキーマ作成

データ移行用のスキーマを作成します

create database sugitest;

無事作成されています

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

mysql>

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

Aurora と MDS 間でレプリケーションするためのユーザーを作成します。Source 側の Aurora でユーザーを作成します。

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

REPLICATION SLAVE の権限付与

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

Source : Dump ファイルを Export

Aurora から Dump ファイルを取得するために、Aurora に MySQL Shell で接続します。(MySQL Shell ではなくて、mysqldump で取得しても大丈夫です。)

mysqlsh admin@aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com

dumpInstance コマンドで、Oracle Cloud Object Storage に Aurora の DumpFile を出力します。

util.dumpInstance("RDStoMDS15", {osBucketName: "mysqldump", osNamespace: "nrefeaanptgn", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]})

実行例

 MySQL  aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com:3306 ssl  JS > util.dumpInstance("RDStoMDS15", {osBucketName: "mysqldump", osNamespace: "nrefeaanptgn", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]})
Acquiring global read lock
NOTE: Error acquiring global read lock: MySQL Error 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)
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
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Table locks acquired
Gathering information - done
All transactions have been started
WARNING: The dumped value of gtid_executed is not guaranteed to be consistent
Global read lock has been released
Checking for compatibility with MySQL Database Service 8.0.23
NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade().
NOTE: User 'admin'@'%' had restricted privileges (INVOKE COMPREHEND, INVOKE LAMBDA, INVOKE SAGEMAKER, LOAD FROM S3, RELOAD, SELECT INTO S3) removed
NOTE: User 'rdsadmin'@'localhost' had restricted privileges (CREATE TABLESPACE, FILE, RELOAD, SHUTDOWN, SUPER) removed
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
Running data dump using 4 threads.
Writing DDL for schema `sugitest`
NOTE: Progress information uses estimated values and may not be accurate.
?% (0 rows / ?), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 0
Uncompressed data size: 0 bytes
Compressed data size: 0 bytes
Compression ratio: 0.0
Rows written: 0
Bytes written: 0 bytes
Average uncompressed throughput: 0.00 B/s
Average compressed throughput: 0.00 B/s
 MySQL  aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com:3306 ssl  JS >

Oracle Cloud の Object Storageに出力されている

Replica : MDS 作成

レプリケーション先として、Oracle Cloud の MDS を作成します。

各種パラメータを入れて、Next を押します

各種パラメータを入れて、Next を押します。

Create

Creating となり、約15分後に Available に変化します。

MDS が所属する Subnet で、3306 を Open にしておきます。

Replica : Dumpfile を Import

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

mysqlsh admin@aurora-replica-01.privatesubnet01.onpremis.oraclevcn.com

Dumpfile を Import します

ignoreVersion を true

util.loadDump("RDStoMDS15", {osBucketName: "mysqldump", osNamespace: "nrefeaanptgn", threads: 4, ignoreVersion: true})

実行例

 MySQL  aurora-replica-01.privatesubnet01.onpremis.oraclevcn.com:33060+ ssl  JS > util.loadDump("RDStoMDS15", {osBucketName: "mysqldump", osNamespace: "nrefeaanptgn", threads: 4, ignoreVersion: true})
Loading DDL and Data from OCI ObjectStorage bucket=mysqldump, prefix='RDStoMDS15' using 4 threads.
Opening dump...
Target is MySQL 8.0.23-cloud (MySQL Database Service). Dump was produced from MySQL 5.7.12-log
WARNING: Destination MySQL version is newer than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. The 'ignoreVersion' option is enabled, so loading anyway.
Fetching dump data from remote location...
Fetching 0 table metadata files for schema `sugitest`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `sugitest`
Executing common postamble SQL

No data loaded.
0 warnings were reported during the load.
 MySQL  aurora-replica-01.privatesubnet01.onpremis.oraclevcn.com:33060+ ssl  JS >

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

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

この @.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'@'%'",
        "'rdsadmin'@'localhost'",
        "'rpluser001'@'%'"
    ],
    "defaultCharacterSet": "utf8mb4",
    "tzUtc": true,
    "bytesPerChunk": 64000000,
    "user": "admin",
    "hostname": "onprem-server01",
    "server": "ip-172-23-1-47",
    "serverVersion": "5.7.12-log",
    "gtidExecuted": "bc4ffe98-cad6-372e-a768-51115c2b1e87:1-5",
    "gtidExecutedInconsistent": true,
    "consistent": true,
    "mdsCompatibility": true,
    "begin": "2021-01-31 01:21:11"
}

メモった値を使って、MDS 側で gtid_purged を行います。MDS に MySQL Client (MySQL Shell じゃないよ) を使って接続します。

mysql -u admin -h aurora-replica-01.privatesubnet01.onpremis.oraclevcn.com -p

gtid_purged が空白なことを確認します

mysql> select @@gtid_purged;
+---------------+
| @@gtid_purged |
+---------------+
|               |
+---------------+
1 row in set (0.00 sec)

mysql>

別の変数でも確認します。

mysql> show global variables like 'GTID%';
+----------------------------------+----------------------------------------+
| Variable_name                    | Value                                  |
+----------------------------------+----------------------------------------+
| gtid_executed                    | efd1edbe-6364-11eb-a0b6-020017015d95:1 |
| gtid_executed_compression_period | 0                                      |
| gtid_mode                        | ON                                     |
| gtid_owned                       |                                        |
| gtid_purged                      |                                        |
+----------------------------------+----------------------------------------+
5 rows in set (0.00 sec)

mysql>

メモって来た値を使って、sys.set_gtid_purged を発行します。

実行例

mysql> call sys.set_gtid_purged("bc4ffe98-cad6-372e-a768-51115c2b1e87:1-5");
Query OK, 0 rows affected (0.00 sec)

mysql>

確認1

mysql> select @@gtid_purged;
+------------------------------------------+
| @@gtid_purged                            |
+------------------------------------------+
| bc4ffe98-cad6-372e-a768-51115c2b1e87:1-5 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>

確認2

mysql> show global variables like 'GTID%';
+----------------------------------+----------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                            |
+----------------------------------+----------------------------------------------------------------------------------+
| gtid_executed                    | bc4ffe98-cad6-372e-a768-51115c2b1e87:1-5,
efd1edbe-6364-11eb-a0b6-020017015d95:1 |
| gtid_executed_compression_period | 0                                                                                |
| gtid_mode                        | ON                                                                               |
| gtid_owned                       |                                                                                  |
| gtid_purged                      | bc4ffe98-cad6-372e-a768-51115c2b1e87:1-5                                         |
+----------------------------------+----------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

Replica : Channel の作成

OCI Console 上で、レプリケーションを行うために、Create Channel を行います。

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

aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com

rpluser001
Rpl001#!
SSL無し

Create Channel

Creating になります。

無事にレプリケーションが構成されると、Actice Status に変わります。

正常時の SHOW SLAVE STATUS の結果はこんな感じです。レプリケーションがエラーになったときには、このコマンドでエラーメッセージを拾えるので、見てみましょう。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com
                  Master_User: rpluser001
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000003
          Read_Master_Log_Pos: 1113
               Relay_Log_File: relay-log-replication_channel.000002
                Relay_Log_Pos: 508
        Relay_Master_Log_File: mysql-bin-changelog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1113
              Relay_Log_Space: 731
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5974158
                  Master_UUID: bc4ffe98-cad6-372e-a768-51115c2b1e87
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 0
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:1-5,
efd1edbe-6364-11eb-a0b6-020017015d95:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: replication_channel
           Master_TLS_Version: TLSv1.2,TLSv1.3
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace: mysql
1 row in set, 1 warning (0.00 sec)

mysql>

レプリケーション確認

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

mysql -u admin -h aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com -p

レプリケーション元の Aurora で、適当にテーブルを作成します。

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);

Aurora 上でテストデータが生成されています。

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>

replica 先である MDS へデータがコピーされているか確認します

mysql -h aurora-replica-01.privatesubnet01.onpremis.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>

Aurora の各種イベント発生検証

Aurora 側で、フェールオーバーや Aurora Replica の追加など、各種イベントが発生します。MDS によるレプリケーションがこれらに追従し、継続してレプリケーションされるか確認します。

Aurora Replica の追加

まず、Aurora は今は Single-AZ 構成になっているので、Multi-AZ 構成にしてみます。読み込み用の Endpoint の FQDN は変わりませんが、Aレコードが指し示すIPアドレスが変化するので、それに MDS のレプリケーションが追従するか確認します。

Singile-AZ の時の Read Endpoint です。10.1.100.113 を指しています。

[opc@onprem-server01 ~]$ dig aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 30 IN CNAME aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 30 IN A 10.1.100.113

ついでに、関係ないですが、Write Endpoint も確認します。10.1.100.113 を指しています。

[opc@onprem-server01 ~]$ dig aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 30 IN CNAME aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 30 IN A 10.1.100.113

Aurora の構成上でも、Multi-AZ が No になっています。

Add reader を押して、Aurora Replica を追加して、Multi-AZ 構成にします。

名前など適当にいれて、Aurora Replica を構成します。

Creating になります。

Aurora クラスター上でも、Multi-AZ が 2 Zones になっています。

読み込み Endpoint が、10.1.100.113から 10.1.101.130 に切り替わっています。 (書き込み Endpoint は変化なし)

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 0 IN CNAME aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.101.130

これは、追加した Aurora Replica の Instance IP アドレスに変化しています。

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2;; ANSWER SECTION:
aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.101.130

追加した Aurora Replica の Endpoint

では、MDS からのレプリケーション先が指し占めす IP アドレスが変化しましたが、継続してレプリケーションされているか確認します。

Aurora にテストデータを書き込みます。

mysql> INSERT INTO sugitest.sample(value) VALUES (11);
Query OK, 1 row affected (0.01 sec)

mysql>

MDS へ正常にレプリケーションされています。

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>

SHOW SLAVE STATUS コマンドでも、異常ありません。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com
                  Master_User: rpluser001
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000003
          Read_Master_Log_Pos: 1992
               Relay_Log_File: relay-log-replication_channel.000002
                Relay_Log_Pos: 1387
        Relay_Master_Log_File: mysql-bin-changelog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1992
              Relay_Log_Space: 1610
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5974158
                  Master_UUID: bc4ffe98-cad6-372e-a768-51115c2b1e87
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 0
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:6-8
            Executed_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:1-8,
efd1edbe-6364-11eb-a0b6-020017015d95:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: replication_channel
           Master_TLS_Version: TLSv1.2,TLSv1.3
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace: mysql
1 row in set, 1 warning (0.01 sec)

mysql>

手動 Failover

では、Aurora が Multi-AZ になったので、手動で Failover をしてみます。その前に、DNS レコードで Endpoint を確認してみましょう。

書き込み Endpoint

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN CNAME aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.100.113

読み込み Endpoint

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 0 IN CNAME aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.101.130

それでは、Failover 操作をします。Endpoint が指し示す IP アドレスが入れ替わります。

Failover

約1分後、Failover がされており、Writer と Reader が入れ替わっています

DNSも切り替わっています。

書き込み

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN CNAME aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.101.130

読み込み

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 0 IN CNAME aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.100.113

では、レプリケーションが正常に構成されているか、Aurora でテストデータを書き込みます。

INSERT INTO sugitest.sample(value) VALUES (12);

MDS 側へ、正常にレプリケーションされています。

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 |
| 12 |    12 |
+----+-------+
12 rows in set (0.00 sec)

mysql>

SHOW SLAVE STATUS コマンドでも、異常ありません。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com
                  Master_User: rpluser001
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000004
          Read_Master_Log_Pos: 518
               Relay_Log_File: relay-log-replication_channel.000003
                Relay_Log_Pos: 753
        Relay_Master_Log_File: mysql-bin-changelog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 518
              Relay_Log_Space: 2207
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5974158
                  Master_UUID: bc4ffe98-cad6-372e-a768-51115c2b1e87
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 0
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:6-9
            Executed_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:1-9,
efd1edbe-6364-11eb-a0b6-020017015d95:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: replication_channel
           Master_TLS_Version: TLSv1.2,TLSv1.3
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace: mysql
1 row in set, 1 warning (0.00 sec)

mysql>

Aurora Relica の削除

読み込み処理を提供している、Aurora Replica を削除します。DNS レコードで Endpoint を確認してみましょう。

書き込み Endpoint の確認

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN CNAME aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.101.130

読み込み Endpoint の確認

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 0 IN CNAME aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-1.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.100.113

Aurora Replica の Delete

delete me

Deleting

書き込み Endpoint は変化ありません。

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN CNAME aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.101.130

読み込み Endpoint は、Aurora Replica が削除されたことにより、10.1.100.113 から 10.1.101.130 へ変わっています。

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 0 IN CNAME aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.101.130

Aurora に書き込みます。

mysql> INSERT INTO sugitest.sample(value) VALUES (13);
Query OK, 1 row affected (0.01 sec)

mysql>

MDS へ正常にレプリケーションされています。

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 |
| 12 |    12 |
| 13 |    13 |
+----+-------+
13 rows in set (0.00 sec)

mysql>

再度、Aurora Replica の追加

Single-AZ 構成に戻ったので、後の検証のために、Aurora Replica を再度追加します。

Primary Instance を削除

では次に、Aurora の Primary Instance を削除してみます。DNS レコードで Endpoint を確認してみましょう。

書き込み Endpoint

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN CNAME aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-2.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.101.130

読み込み Endpoint

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 0 IN CNAME aurora-source-01-instance-3.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-3.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.100.110

Primary Instance を削除

delete

Deleting

MDS の SHOW SLAVE STATUS\Gを確認すると、connecting となっており、再度接続をしています。これは、Failover や Aurora Replica を追加していることにより、Writer の IP アドレスをレプリケーション先として、MDS が保持していたと思われます。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com
                  Master_User: rpluser001
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000004
          Read_Master_Log_Pos: 1042
               Relay_Log_File: relay-log-replication_channel.000003
                Relay_Log_Pos: 1277
        Relay_Master_Log_File: mysql-bin-changelog.000004
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1042
              Relay_Log_Space: 2731
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error reconnecting to master 'rpluser001@aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on 'aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com' (111)
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5974158
                  Master_UUID: bc4ffe98-cad6-372e-a768-51115c2b1e87
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 0
                  Master_Bind:
      Last_IO_Error_Timestamp: 210131 02:48:20
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:6-11
            Executed_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:1-11,
efd1edbe-6364-11eb-a0b6-020017015d95:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: replication_channel
           Master_TLS_Version: TLSv1.2,TLSv1.3
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace: mysql
1 row in set, 1 warning (0.01 sec)

mysql>

MDS のレプリケーションは、読み込みエンドポイントを指定しているため、すぐに再接続されます。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com
                  Master_User: rpluser001
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000005
          Read_Master_Log_Pos: 256
               Relay_Log_File: relay-log-replication_channel.000004
                Relay_Log_Pos: 491
        Relay_Master_Log_File: mysql-bin-changelog.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 256
              Relay_Log_Space: 1835
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5974158
                  Master_UUID: bc4ffe98-cad6-372e-a768-51115c2b1e87
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 0
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:6-11
            Executed_Gtid_Set: bc4ffe98-cad6-372e-a768-51115c2b1e87:1-11,
efd1edbe-6364-11eb-a0b6-020017015d95:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: replication_channel
           Master_TLS_Version: TLSv1.2,TLSv1.3
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace: mysql
1 row in set, 1 warning (0.00 sec)

mysql>

書き込み Endpoint が、IP アドレスが変わっています。

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN CNAME aurora-source-01-instance-3.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-3.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.100.110

読み込み Endpoint

[opc@onprem-server01 ~]$ dig +dnssec @8.8.4.4 aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com A | grep "ANSWER SECTION" -A2
;; ANSWER SECTION:
aurora-source-01.cluster-ro-comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 0 IN CNAME aurora-source-01-instance-3.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com.
aurora-source-01-instance-3.comulzzpkhpz.ap-northeast-1.rds.amazonaws.com. 4 IN A 10.1.100.110

Aurora にテストデータを書き込み

mysql> INSERT INTO sugitest.sample(value) VALUES (15);
Query OK, 1 row affected (0.02 sec)

mysql>

MDS 側で正常にレプリケーションされています

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 |
| 12 |    12 |
| 13 |    13 |
| 14 |    14 |
| 15 |    15 |
+----+-------+
15 rows in set (0.00 sec)

mysql>

参考URL

Using GTID-based replication for Amazon RDS MySQL
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-replication-gtid.html

MySQL ShellのdumpInstance()、dumpSchemas()をAWS RDS＆AuroraMySQLに対して使用する
https://atsuizo.hatenadiary.jp/entry/2020/09/01/090000

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up