🍕

【AWS】リードレプリカをつかって安全に解析できる環境をつくろう

に公開
AWS
SQL
EC2
データ解析
RDS
tech

こんな方のために書きました

  • データ分析用のデータベースの基盤構築をしたい
  • AWS を使いこなせるようになりたい

ゴール

  • AWS RDS のリードレプリカの作成
  • 特定条件のデータのみ参照できるユーザーを作成

例として、従業員の名前、部署、給与のデータテーブルがマスターとしてある時、部署の情報だけを読み取り専用で限られたユーザーがアクセスできるようにしてみます。

必要なもの

項目 内容
AWS アカウント
IAM ユーザー AmazonRDSFullAccess, AmazonEC2FullAccess
RDS（MySQLまたはPostgreSQL） マスターDB＋リードレプリカ構成
サンプルデータ 社員リストテーブル
EC2 サーバー MySQL クライアントとして利用
SQL クライアント MySQL Workbench、psql、またはCLI など
ネットワーク構成 VPC、サブネット、SG(セキュリティグループ)でDBに接続可能な設定

こんな構成になります

  • RDS マスター DB：書き込み・更新可能（管理者専用）
  • RDS リードレプリカ：参照専用（Replicationで同期）
  • EC2 / SQL Client：マスターには接続不可、レプリカには制限付きで接続可能

手順概要

  1. マスター DB 作成
  2. サンプルデータ投入
  3. EC2 インスタンス作成（クライアント用）
  4. リードレプリカ作成
  5. アクセス権制御
  6. 動作確認

本題は4からなので、1~3は構築できそうであれば、読み飛ばしてしまって大丈夫です。

1. マスターDB作成

では早速スタートです。
AWS のコンソールにログインし必要なサービスの準備を行います。

image.png

まずは RDS でデータベースを作成します。
image.png

「Aurora and RDS」で"データベースを作成"を選択

以下のように設定します。これ以外の項目は基本的にはデフォルトで大丈夫です。

  • エンジン：MySQL 8.0.XX
  • テンプレート：無料枠利用
  • インスタンス識別子：rr-master
  • ユーザー名：admin
  • 接続：
    • VPC：default
    • パブリックアクセス：あり
    • VPC セキュリティグループ：新規作成（セキュリティグループ名：db-master-sg）
      • インバウンドルール：
        • ポート：3306 → 自分のグローバルIPのみ許可

image.png

"データベースを作成"を押します。しばらくするとrr-masterというデータベースがつくられます。

データベースを"接続とセキュリティ"に記載されているエンドポイントを使うので、どこかにコピーしておきます。

image.png

2. サンプルデータ投入

続いて、サンプルデータをつくるために、ローカル環境に SQL を準備します。

MySQL クライアントをインストールする方法は以下の通りです。

何かしらのバージョンが表示されればOKです。

mysql --version

こちらのコマンドを実行しパスワードを入れると先ほどのデータベースに接続できます。

mysql -h <master-endpoint> -u admin -p

こんな表示があれば OK!

Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql>

データベースをつくっていきます。以下を打ち込んでください。
ある会社のスタッフ名、部署、給与のデータがある状態です。

mysql> USE company;
Database changed
mysql>
mysql> CREATE TABLE employees (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   name VARCHAR(50),
    ->   department VARCHAR(50),
    ->   salary INT
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> INSERT INTO employees (name, department, salary) VALUES
    -> ('Alice', 'Engineering', 7000000),
    -> ('Bob',   'HR',          5000000),
    -> ('Charlie','Engineering',8000000),
    -> ('David', 'Sales',       6000000);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

試しに以下のコマンドで確認してみましょう。

SELECT * FROM employees;

データが格納されていればOKです。
image.png

exit;で終了します。

3. EC2 インスタンス作成（クライアント用）

IAM ロールの作成

まずは IAM でロールをつくります。
AWS コンソールに戻り、「IAM」から"ロールの作成"を選択します。
以下のように設定します。

  • エンティティタイプ：AWS サービス
  • ユースケース：EC2

ポリシー名でAmazonSSMManagedInstanceCoreを入力しチェックを入れます。
ロール名はEC2-SSM-Roleなどとして"ロールを作成"を選択します。

image.png

EC2 インスタンスの起動

「EC2」の"インスタンスを起動"を選択します。

以下のように設定します。

  • 名前：rr-client
  • AMI：Amazon Linux 2023
  • インスタンスタイプ：t3.micro (無料利用枠の対象です)
  • キーペア：今回はなしで続行
  • セキュリティグループ：新規作成ec2-client-sg(自動的に命名される名前でもOKです)
    • インバウンド：
      • SSH (22) → 自分のIP
    • アウトバウンド：特に設定変更なし
  • IAM インスタンスプロフィール：EC2-SSM-Role

image.png

4. リードレプリカ作成

ここからが本番です。リードレプリカを作成します。
と言っても、めちゃくちゃ簡単です。

4-1. セキュリティグループの作成

EC2 からのみ接続できる専用のセキュリティグループをつくります。
AWS コンソールから「VPC」の"セキュリティグループ"から"セキュリティグループを作成"を選択します。

設定は以下の通りです。

  • 名前：db-replica-sg
  • 説明：(例)RDS Read Replica inbound from ec2-client-sg
  • VPC：default
  • インバウンドルール
    • タイプ：MySQL/Aurora
    • ポート：3306
    • ソース：ec2-client-sg
  • アウトバウンド：特に設定変更なし

4-2. リードレプリカの作成

データベースの"アクション"から"リードレプリカの作成"を選びます。

image.png

  • インスタンス識別子：read-replica-db
  • インスタンスクラス：db.t3.micro(この例では最小サイズで問題ないです)
  • 可用性
    • デプロイオプション：シングルAZ
  • 接続
    • VPC：default
    • サブネットグループ：default
    • パブリックアクセス：不可
    • セキュリティグループ：既存
      • 名前：db-replica-sg
      • インバウンドルール：
        • MySQL/Aurora (3306) → ソースに ec2-client-sg を指定
  • データベース認証：パスワード認証

このように、元のデータベースから関連付けられた状態になり、プライマリとレプリカの2種類のロールが存在しています。

image.png

ここで、レプリカの方のエンドポイントをコピーしておきます。

image.png

5. アクセス権制御

マスター側で接続できるデータを制限していきます。
再び、こちらのコマンドでマスターのデータベースに接続します。

mysql -h <master-endpoint> -u admin -p

そして、こちらのデータベース操作を行います。

USE company;

CREATE OR REPLACE VIEW employees_public AS
SELECT department
FROM employees;

CREATE USER 'readonly_user'@'%' IDENTIFIED BY '自分でつくるパスワード';
GRANT SELECT ON company.employees_public TO 'readonly_user'@'%';
FLUSH PRIVILEGES;

ここでは、従業員名と給与のデータはデータ解析者に公開しないように、employees_publicというビューを使って元のテーブルemployeesから部門の情報だけを取り出しています。
そして、employees_publicを読み取り専用にしています。

6. 動作確認

EC2 にログインします。AWS コンソールの「EC2」からインスタンスを選択し、"接続"を選択します。いくつか接続方法がありますが、Session Managerを選びます。

image.png

これで、ブラウザ上でコマンドを実行できるようになりました。
EC2でsqlコマンドが使えるように準備します。

sudo dnf update -y
sudo dnf install -y mariadb105

image.png

続いて、先ほどのレプリカのエンドポイントを使って接続してみます。パスワードは「5. アクセス権制御」の'自分でつくるパスワード'の内容です。

mysql -h <replica-endpoint> -u readonly_user -p

試しに中身を見てみます。

SELECT * FROM company.employees_public;

image.png

レプリカでは部署の情報しか見えていません！

これにて、リードレプリカの作成は終わりですが、色々と試してみましょう。

Q1. 書き込みはできなくなっているのか？

本当にリードレプリカの通り書き込みはできないのでしょうか？

image.png

ちゃんと読み込み専用になっていますね。

Q2. EC2経由でしかリードレプリカにアクセスできないか？

ローカルPCからアクセスしてみます。

mysql -h <read-replica-endpoint> -u readonly_user -p

image.png

こちらもちゃんと閉じた構成になっています。

これで特定のユーザーが使用できる読み取り専用のデータベースができました！リードレプリカ自体はとても簡単に構築できますので是非挑戦してみてください。

参考

https://aws.amazon.com/jp/rds/features/read-replicas/

Discussion