MySQLAdvent Calendar 2023

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

Last updated at 2024-09-24Posted at 2023-12-12

これは 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 回のリリースでサポートされた」と書きました。

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

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（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 インスタンス作り直し…

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

MHSレプリカでトランザクションをスキップする方法 - MHSでGTIDレプリにてトランザクションをスキップするには（41から始めました）

試行錯誤の末に

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はどのようにデータの破損を検出しているのか

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