MySQL HeatWave on AWS でインバウンドレプリケーションを試し…てなんとか成功した話

これは **[MySQL Advent Calendar 2023](https://qiita.com/advent-calendar/2023/mysql)** の 13 日目の記事です。

昨日（12 日）は tomomo1015 さんでした。

- **[MySQLの勉強をリファレンスを読みながらやってみることにした話](https://tomomo1015.hatenablog.com/entry/2023/12/12/163307)**

https://tomomo1015.hatenablog.com/entry/2023/12/12/163307

---

わたしの記事は、MySQL HeatWave on AWS で 2023 年 11 月に（16 日・28 日の）2 回のリリースでサポートされたインバウンドレプリケーションを（移動中の新幹線の中などで悪戦苦闘しながら）設定した記録（と感想）です。

:::note
16 日のリリースがインバウンドレプリケーションのサポートで 28 日のリリースが機能強化（チャネルフィルタと遅延レプリケーションのサポート）なのですが、後述するようにチャネルフィルタがないと RDS for MySQL ~~や Aurora MySQL~~ からのレプリケーションは正常に機能しませんでした。

そういう意味もあって「2 回のリリースでサポートされた」と書きました。

**2024/9/12 追記：**
Aurora MySQL（v3）についてはチャネルフィルタは不要でした（だからチャネルフィルタなしで一旦リリースされたんですね）。
:::

## ポイント

- AWS HeatWave on AWS のインバウンドレプリケーションは **GTID 形式のみサポート**
  - 本家 Oracle Cloud では 1 年前の時点で非 GTID 形式（匿名トランザクション）もサポート
- インバウンドレプリケーションは **Web のコンソール「Channels」から設定する必要がある**
  - SQL を使ってレプリケーション設定することは（試した限りでは）不可
    - `REPLICATION_SLAVE_ADMIN`権限およびその`GRANT OPTION`が管理（admin）ユーザに与えられないため
- 通常の MySQL で使える技が封印されている
  - `SET GLOBAL gtid_purged='XXX'`がエラー（権限不足）になる
  - `SET GTID_NEXT='XXX'`はできるが`FLUSH LOGS`や`PURGE BINARY LOGS TO 'XXX'`がエラー（権限不足）になる
    - なので、レプリケーションの初期設定の壁が突破できても **その後の運用管理が難しそう**
- **ソース DB で雑にダンプを取って後から SQL で GTID 絡みのパラメータをいじって…と考えていると詰みそう**

:::note
**2024/1/30 追記：**
2024/1/17 のアップデートで非 GTID 形式（匿名トランザクション）もサポートされました。
https://dev.mysql.com/doc/heatwave-aws/en/relnotes-2024-01-17.html

**2024/9/12 追記：**
非 GTID 形式（匿名トランザクション）の場合の制約（`sql_slave_skip_counter`が使えない）と回避方法、そして GTID 形式でのトランザクションスキップ方法について、中の人（？）が記事にしてくださっています。

https://next4us-ti.hatenablog.com/entry/2024/01/26/153020

**2024/9/24 追記：**
PrivateLink 接続でのインバウンドレプリケーションの記事も書きました。
https://zenn.dev/hmatsu47/articles/heatwave-on-aws-privatelink

:::

## MySQL HeatWave on AWS のインバウンドレプリケーションとは

AWS 内（でなくても良いのですが）のソース DB から HeatWave on AWS の MySQL DB にレプリケーションする機能です。

RDS for MySQL や Aurora MySQL、EC2 上の MySQL をソース DB にすることができます。

- **[7 Inbound Replication](https://dev.mysql.com/doc/heatwave-aws/en/heatwave-aws-inbound-replication.html)（MySQL HeatWave on AWS）**

https://dev.mysql.com/doc/heatwave-aws/en/heatwave-aws-inbound-replication.html

## ここからは試行錯誤の記録

正直 GTID レプリケーションは慣れておらず、そのせいもあって苦戦しました。

結果として、ソース・レプリカとも DB を何度か作り直すハメに…。

### 1. RDS で MySQL 8.0 用のパラメータグループを設定

いずれも [MySQL HeatWave on AWS の公式ガイド](https://dev.mysql.com/doc/heatwave-aws/en/heatwave-aws-source-configuration.html)で指定されたとおりにしています。

- `binlog_format` : `ROW`
- `enforce_gtid_consistency` : `ON`
- `gtid-mode` : `ON`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/fc1b7793-5065-2368-5083-5af1482283d9.png)

### 2. RDS for MySQL 8.0 でソース DB を設定

- エンジンバージョン : 8.0.35
- DB サブネットグループ : パブリックサブネットを指定
- パブリックアクセス : 可能

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/f725bed6-f717-c251-390f-6534e90e7e12.png)

:::note warn
パブリックアクセス可能な DB クラスタ／インスタンスを運用するのはアンチパターンです。
最低限 Aurora に [GuardDuty RDS Protection](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/guard-duty-rds-protection.html) を組み合わせるなどしてセキュリティリスクに備えましょう。
:::

- VPC セキュリティグループ : 新規作成
  - 当初はインバウンドで設定用の EC2 インスタンスのプライベートアドレスを通すようにしておく
  - 後で編集
    - レプリカ HeatWave on AWS のパブリック IP アドレスを追加
- DB パラメータグループ : 手順 1. で作成したものに

![スクリーンショット 2023-12-11 21.04.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/9445f473-46ab-1906-8dfc-b72cb0651c73.png)

- 自動バックアップ有効
- バックアップ保持期間 : 1 以上
  - binlog を出力するため

![スクリーンショット 2023-12-11 21.06.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/bde5214f-8f9c-0e69-4200-1f76b1d9b64e.png)

### 3. ソース DB に任意のデータを入れてスキーマダンプユーティリティでダンプ

- ソース DB でレプリケーションユーザを作成
  - **[7.3 Creating a Replication User On a Source Server](https://dev.mysql.com/doc/heatwave-aws/en/heatwave-aws-create-repl-user.html)（MySQL HeatWave on AWS）**
    - TLS 接続必須
- 適当に DB（スキーマ）などを作成
- [公式ガイド](https://dev.mysql.com/doc/heatwave-aws/en/heatwave-aws-creating-channel.html)の指示に従い[ソース DB のデータをダンプ](https://dev.mysql.com/doc/heatwave-aws/en/heatwave-aws-exporting-data-mysql-shell.html)

```sh:データをダンプ
JS > util.dumpSchemas(['tpch'], '/home/ec2-user/repl_test', {ocimds: true})
```

:::note
今回は、過去の MySQL HeatWave on AWS 関連記事で使ってきた[こちらのデータ](https://oracle-japan.github.io/ocitutorials/beginners/creating-HeatWave/#5-%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AE%E6%A7%8B%E7%AF%89)を使いました。

- **[5. サンプルデータベースの構築](https://oracle-japan.github.io/ocitutorials/beginners/creating-HeatWave/#5-%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AE%E6%A7%8B%E7%AF%89)（OCI チュートリアル その10 - MySQLで高速分析を体験する）**
:::

### 4. AWS HeatWave on AWS でレプリカ DB を設定

- MySQL version : 8.2.0
- Enable inbound connectivity from allowed public IP address ranges : チェック
- Allowed public IP address ranges : 設定用の EC2 インスタンスのパブリック IP アドレス
  - または NAT Gateway or インスタンスのパブリック IP アドレス

![スクリーンショット 2023-12-11 21.08.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/d2b82e40-1c3b-1eeb-6e48-ec26285e2913.png)

### 5. ソースおよびレプリカ DB のパブリック IP アドレスを確認

- CloudShell で`ping`や`dig`などを使って、ソース DB のエンドポイントからパブリック IP アドレスを割り出す
  - 設定用の EC2 から`ping`するとプライベート IP アドレスが返るので、必ず CloudShell で

```sh:ソースDBのパブリックIPアドレス確認（CloudShellから）
$ ping database-1.XXX.ap-northeast-1.rds.amazonaws.com
PING ec2-35-XX-XX-XX.ap-northeast-1.compute.amazonaws.com (35.XX.XX.XX) 56(84) bytes of data.
^C
--- ec2-35-XX-XX-XX.ap-northeast-1.compute.amazonaws.com ping statistics ---
2 packets transmitted, 0 received, 100% packet loss, time 1048ms
```

- 設定用の EC2 か CloudShell で`ping`や`dig`などを使って、レプリカの Hostname からパブリック IP アドレスを割り出す

### 6. 手順 2. の途中で作成したセキュリティグループのインバウンド（許可）に、手順 5. で確認したレプリカ DB のパブリック IP アドレスを追加

```sh:レプリカDBのパブリックIPアドレス確認（CloudShellまたはEC2から）
$ ping fab4XXXX-XXXX-XXXX-XXXX-XXXX28.dbsystem.ap-northeast-1.aws.cloud.mysql.com
PING fab4XXXX-XXXX-XXXX-XXXX-XXXX28.dbsystem.ap-northeast-1.aws.cloud.mysql.com (54.XX.XX.XX) 56(84) bytes of data.
^C
--- fab4XXXX-XXXX-XXXX-XXXX-XXXX28.dbsystem.ap-northeast-1.aws.cloud.mysql.com ping statistics ---
2 packets transmitted, 0 received, 100% packet loss, time 1020ms
```

- 「/32」で追加

![スクリーンショット 2023-12-11 21.11.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/e5f29a33-4d95-3455-6a3a-1e713082b8b7.png)

### 7. レプリカ DB に手順 3. のダンプをロード

- [ダンプロードユーティリティ](https://dev.mysql.com/doc/mysql-shell/8.0/ja/mysql-shell-utilities-load-dump.html)を使ってロード

```sh:データをロード
JS > util.loadDump("repl_test", {dryRun: false, resetProgress:true, ignoreVersion:true, updateGtidSet:"replace"})
```

- ここでミスるとどうしようもなくなる（後述）

### 8. レプリケーションチャネル作成

- Web のコンソールで「MySQL」（下メニュー）→「Channels」（上メニュー）から「Create Channel」
- Source connection
  - Allowed outbound addresses : 手順 5. で確認したソースのパブリック IP アドレスを「/32」で記入
  - Username : 手順 3. で作成したレプリケーションユーザの名前
  - Password : 同・パスワード
  - SSL mode : Required
- Target DB System
  - Channel name : 空欄で OK
  - Applier username : 同じく空欄で OK
  - Select DB System : 手順 4. で設定したレプリカ DB を指定
  - Channel filter options : Custom で `REPLICATE_IGNORE_DB`を使って`mysql`スキーマを指定

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/14cbf0f2-64e9-a0ff-1357-68f01a9cb678.png)

- 実はここが間違っていた（後述）

## 発生した問題

- チャネルの State に「Needs Attention」と表示されてレプリケーションが開始されない
  - この画面だけ見ていても何が悪いのかさっぱり…
    - できればここに`SHOW REPLICA STATUS`や`SELECT * FROM performance_schema.replication_applier_status_by_worker`の結果へのリンクが欲しかった

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/82bfade4-d546-e163-621e-196d6f391b13.png)

- 「Workspaces」から MySQL DB に接続して`SHOW REPLICA STATUS`すると、特定の SQL 文（トランザクション）の実行で止まっていることがわかる

```json:SHOW REPLICA STATUSの結果（抜粋）
[
  [
    "Waiting for source to send event",
    "database-1.XXX.ap-northeast-1.rds.amazonaws.com",
    "rpluser001",
    3306,
    60,
    "mysql-bin-changelog.000033",
    197,
    "relay-log-replication_channel.000002",
    393,
    "mysql-bin-changelog.000004",
    "Yes",
    "No",
    "",
    "",
    "",
    "",
    "",
    "",
    1142,
    "Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'a27cXXXX-XXXX-XXXX-XXXX-XXXXd03f:1' at source log mysql-bin-changelog.000004, end_log_pos 400. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.",
（中略）
    ""
  ]
]
```

- さらにエラーメッセージの指示どおり`performance_schema.replication_applier_status_by_worker`を見ていくと実行できずに停止している SQL 文（とエラーの内容）が判明
  - 例 1 : 権限がない
    - 特殊ユーザを作って何かの権限を付与しようとしている？
  - 例 2 : すでに存在しているテーブルにデータを挿入しようとして主キーが重複している
    - binlog の反映開始ポジション（GTID）がうまく判別できていない？
- 実行できない SQL 文（トランザクション）を`SET GLOBAL gtid_purged='XXX'`で飛ばそうとするとエラーが発生

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/0ee32742-6c2d-7eb8-b62b-b2a4ec77b1d2.png)

- `SET GTID_NEXT='XXX'`はできるが`FLUSH LOGS`や`PURGE BINARY LOGS TO 'XXX'`がエラー（権限不足）になる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/6ee196c3-47ca-f529-1dde-e6469c54ecdf.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/6617da09-5752-5bf7-6ea9-3ac41199a5d4.png)

- [このあたりの技](https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0025)が封印されてしまったので、初期設定をクリアしても運用が厳しそう
  - レプリカ DB で試行錯誤するうちにレプリカ DB 側の GTID が先に進んでしまった際にどうしようもなくなった
    - 仕方なく DB インスタンス作り直し…

:::note
**2024/9/12 追記：**
（前述した）GTID 形式でのトランザクションスキップ方法はこちらの記事に書かれています。

- **[MHSレプリカでトランザクションをスキップする方法 - MHSでGTIDレプリにてトランザクションをスキップするには](https://next4us-ti.hatenablog.com/entry/2024/01/26/153020#MHS%E3%81%A7GTID%E3%83%AC%E3%83%97%E3%83%AA%E3%81%AB%E3%81%A6%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E3%82%92%E3%82%B9%E3%82%AD%E3%83%83%E3%83%97%E3%81%99%E3%82%8B%E3%81%AB%E3%81%AF)（41から始めました）**
:::

## 試行錯誤の末に

- `performance_schema.replication_applier_status_by_worker`の内容が↓のような状況に

```json:SELECT * FROM performance_schema.replication_applier_status_by_workerの結果（抜粋）
[
  [
    "replication_channel",
    1,
    null,
    "OFF",
    3944,
    "Worker 1 failed executing transaction 'a27cXXXX-XXXX-XXXX-XXXX-XXXXad03f:109' at source log mysql-bin-changelog.000062, end_log_pos 543; Error 'The query does not comply with variable require_row_format restrictions.' on query. Default database: ''. Query: 'INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1702299046040) ON DUPLICATE KEY UPDATE value = 1702299046040'",
    "2023-12-11T13:58:28.653029",
（中略）
    null
  ]
]

```

- どこかで見覚えが…
  - 検索したら[この記事](https://blogs.oracle.com/mysql/post/successful-rds-to-oci-mysql-heatwave-migration-with-replication-channel-filters)が出てきた

https://blogs.oracle.com/mysql/post/successful-rds-to-oci-mysql-heatwave-migration-with-replication-channel-filters

### 問題はレプリケーションチャネルフィルタにあった

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/5425a607-0f57-88ef-0a7c-be1de2391144.png)

- 「Custom」のまま選択肢を変えなかったので見逃していたが、テンプレート「AWS RDS MySQL 8.0」があった

### レプリケーション成功

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/e23d8359-93b3-8944-0153-df6f8ace548e.png)

- 「State」が「Active」に

### ソース DB でデータを追加してみる

```sql:データ（行）を追加してみる
mysql> SELECT * FROM tpch.region;
+-------------+-------------+---------------------------------------------------------------------------------------------------------------------+
| R_REGIONKEY | R_NAME      | R_COMMENT                                                                                                           |
+-------------+-------------+---------------------------------------------------------------------------------------------------------------------+
|           0 | AFRICA      | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to  |
|           1 | AMERICA     | hs use ironic, even requests. s                                                                                     |
|           2 | ASIA        | ges. thinly even pinto beans ca                                                                                     |
|           3 | EUROPE      | ly final courts cajole furiously final excuse                                                                       |
|           4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl        |
+-------------+-------------+---------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO tpch.region SET R_REGIONKEY = 999, R_NAME = 'SPACE', R_COMMENT = 'outside the earth';
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM tpch.region;
+-------------+-------------+---------------------------------------------------------------------------------------------------------------------+
| R_REGIONKEY | R_NAME      | R_COMMENT                                                                                                           |
+-------------+-------------+---------------------------------------------------------------------------------------------------------------------+
|           0 | AFRICA      | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to  |
|           1 | AMERICA     | hs use ironic, even requests. s                                                                                     |
|           2 | ASIA        | ges. thinly even pinto beans ca                                                                                     |
|           3 | EUROPE      | ly final courts cajole furiously final excuse                                                                       |
|           4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl        |
|         999 | SPACE       | outside the earth                                                                                                   |
+-------------+-------------+---------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
```

### レプリケーション DB で確認

![スクリーンショット 2023-12-12 0.38.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/e1b5aa6e-2aaa-de8f-41a2-b35bc295f723.png)

## しかし

先に記した問題のほか、ソース DB のクラスタ構成・インスタンスタイプ変更やレプリカ DB を止めて再起動したときなどにパブリック IP アドレスが変わってしまうといちいち設定を変えないといけない（またはフルオープン←絶対にやりたくない）とか、現状の使用感はいまいちですね…。

:::note
Oracle Cloud としては
> Inbound replication is not a managed functionality. You are responsible for configuring and maintaining the channel, and for ensuring that the traffic between source and replica is properly configured.

[ということらしい](https://dev.mysql.com/doc/heatwave-aws/en/heatwave-aws-inbound-replication1.html)ので、あまり文句は言えないのですが。
:::

現状、目立った動きが見えない（でも内部的にはリリースに向けて動いているという話もある？）PrivateLink 対応で使いやすくなることに期待、ですかね。

---

明日（14 日）は ryuichi1208 さんです。

- **[【MySQL】InnoDBはどのようにデータの破損を検出しているのか](https://ryuichi1208.hateblo.jp/entry/2023/12/14/000000)**

https://ryuichi1208.hateblo.jp/entry/2023/12/14/000000