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

これは MySQL Advent Calendar 2023 の 13 日目の記事です。

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

MySQLの勉強をリファレンスを読みながらやってみることにした話

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

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

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

ポイント

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 絡みのパラメータをいじって…と考えていると詰みそう

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

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

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

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

7 Inbound Replication（MySQL HeatWave on AWS）

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

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

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

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

いずれも MySQL HeatWave on AWS の公式ガイドで指定されたとおりにしています。

binlog_format : ROW
enforce_gtid_consistency : ON
gtid-mode : ON

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

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

パブリックアクセス可能な DB クラスタ／インスタンスを運用するのはアンチパターンです。
最低限 Aurora に GuardDuty RDS Protection を組み合わせるなどしてセキュリティリスクに備えましょう。

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

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

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

ソース DB でレプリケーションユーザを作成
- 7.3 Creating a Replication User On a Source Server（MySQL HeatWave on AWS）
  - TLS 接続必須
適当に DB（スキーマ）などを作成
公式ガイドの指示に従いソース DB のデータをダンプ

データをダンプ

JS > util.dumpSchemas(['tpch'], '/home/ec2-user/repl_test', {ocimds: true})

今回は、過去の MySQL HeatWave on AWS 関連記事で使ってきたこちらのデータを使いました。

5. サンプルデータベースの構築（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 アドレス

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

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

ソース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 アドレスを追加

レプリカ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」で追加

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

ダンプロードユーティリティを使ってロード

データをロード

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スキーマを指定

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

発生した問題

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

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

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'で飛ばそうとするとエラーが発生

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

このあたりの技が封印されてしまったので、初期設定をクリアしても運用が厳しそう
- レプリカ DB で試行錯誤するうちにレプリカ DB 側の GTID が先に進んでしまった際にどうしようもなくなった
  - 仕方なく DB インスタンス作り直し…

試行錯誤の末に

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

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
  ]
]

どこかで見覚えが…
- 検索したらこの記事が出てきた

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

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

レプリケーション成功

「State」が「Active」に

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

データ（行）を追加してみる

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 で確認

しかし

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

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.

ということらしいので、あまり文句は言えないのですが。

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

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

【MySQL】InnoDBはどのようにデータの破損を検出しているのか