こんにちは。株式会社協栄情報クラウド事業本部のきおかです。
今回はAmazon RDSにMySQLでデータテーブルを作成する際に、Amazon EC2インスタンス内のローカルファイルからテーブルを作成する方法をご紹介します。今回はcsvファイルをロードして、データベース内にテーブルを作ります。
構成図
やりたいことはシンプルなので構成もシンプルです。
利用するAWSサービスと当記事での略語
| サービス名 | 略語 |
|---|---|
| Amazon Virtual Private Cloud (VPC) | VPC |
| セキュリティグループ | SG |
| Identity and Access Management (IAM) | IAM |
| Amazon Elastic Compute Cloud (EC2) | EC2 |
| Amazon Relational Database Service (RDS) | RDS |
| RDS for MySQL | MySQL |
| AWS Command Line Interface (AWS CLI) | CLI |
| AWS Key Management Service (AWS KMS) | KMS |
準備
名前はわかりやすくユニークであるようにつけましょう。
また、下記の表に書いている「設定項目」は設定項目を網羅しているわけではなく、デフォルト表示でないものと設定が必要なものを記載しています。
VPC作成
| AZ | パブリックサブネット | プライベートサブネット |
|---|---|---|
| 1個 | 1個 | 1個 |
セキュリティグループ作成
| 名前 | タイプ | ソース |
|---|---|---|
| SG-EC2 | SSH | MYIP |
| SG-RDS | MySQL/Aurora | SG-EC2 |
IAM作成
EC2のAMIをAmazon Linux 2023にして、セッションマネージャーで操作したいので、以下のポリシーをアタッチします。
EC2にセッションマネージャーで接続する方法はこちらのAWS公式ドキュメントで紹介されています。
| ロール名 |
|---|
| IRL-EC2 |
| ポリシー名 |
|---|
| AmazonSSMManagedInstanceCore |
EC2作成
| 設定項目 | 設定値 |
|---|---|
| 名前とタグ | test-EC2 |
| AMI | Amazon Linux 2023 (64ビット x86) |
| インスタンスタイプ | t2.micro |
| キーペア | 自分が作成したもの |
| ネットワーク | 作成したVPC |
| サブネット | パブリックサブネット |
| パブリックIPの割り当て | 有効化 |
| セキュリティグループ | SG-EC2 |
| IAMインスタンスプロフィール | IRL-EC2 |
EC2セットアップ
bash -i
sudo su ec2-user
cd /
# デフォルトでインストールされているパッケージを最新のバージョンに更新する。-yは、すべてのプロンプトに自動的に"yes"で答えてもらうためのオプションです。
sudo dnf update -y
# RHEL9（※）のパッケージをインストール
sudo dnf -y localinstall https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
# インストールしたパッケージからmysqlをインストール
sudo dnf install mysql※RHEL9 (Red Hat Enterprise Linux 9): Red Hat社が提供するエンタープライズ向けのLinuxディストリビューションの一つ。Amazon Linux 2023がFedora34, 35, 36のコンポーネントが含まれています。また、MySQLに慣れていたので何とかMySQLをインストールするためにRHEL9のパッケージインストールで対応しましたが、MySQLの派生版であるMariaDBでやった方がいいかもですね。MariaDBでの記事作成も考えます。
RDS作成
標準作成を選択してください。
| 設定項目 | 設定値 | 備考 |
|---|---|---|
| エンジンタイプ | MySQL (MySQL 8.0.32) | |
| テンプレート | 無料利用枠 | 今回はマルチAZ構成にはしないので、このハンズオン内では無料で使えるであろう無料利用枠を選択。 |
| 可用性と耐久性 | 単一のDBインスタンス | 無料利用枠を選択すると自動的または強制的に「単一のDBインスタンス」が選択されます。 |
| DBインスタンス識別子 | test01-db | |
| マスターユーザー名 | admin | MySQLにログインする際に使用する名前。 |
| マスターパスワード | 任意のものを設定 | |
| DBインスタンスクラス | バースト可能クラス(db.t3.micro) | 無料利用和鵜を選択すると、「db.t2.micro」か「db.t3.micro」を選べます。MySQLであれば、どちらも750時間/1か月は無料で利用できます。 |
| ストレージタイプ | 汎用SSD(gp3) | 選択できるもののうち、最も安くコスパもいいものを選択しました。 |
| ストレージ割り当て | 20GiB | 無料利用枠で使える20GBに設定しました。 |
| ストレージの自動スケーリング | 「ストレージの自動スケーリングを有効にする」のチェックを外す | |
| コンピューティングリソース | 「EC2コンピューティングリソースに接続」を選択 | |
| EC2インスタンス | test-EC2 | |
| DBサブネットグループ | 自動セットアップ | |
| VPCセキュリティグループ | SG-RDS | |
| データベース認証 | 「パスワード認証」を選択 | |
| 最初のデータベース名 | test01 | |
| バックアップ | 「自動バックアップを有効にします」のチェックを外す | |
| AWS KMS キー | 作成したものを選択 | データの暗号化のために使用します。 |
EC2からRDSに入る
$ mysql -h RDSエンドポイント -P 3306 -u admin -p
-> RDS構築時に設定したマスターパスワードを入力
csvファイルをロードする用のテーブルを予め作っておく
# RDS構築時に作成したデータベースを確認
mysql> show databases;
# RDS構築時に作成設定したデータベースtest01に入る
mysql> use test01;
# テーブルを確認（未作成なのでemptyのはず）
mysql> show tables;
# csvファイルをロードする用のテーブルを作成
mysql> CREATE TABLE 作りたいテーブル名 ( カラム１ データタイプ オプション, カラム２ データタイプ オプション, ..., カラムN データタイプ オプション);
# 下記のような成功コードが出たら作成完了です
Query OK, 0 rows affected (0.03 sec)下記に私のテーブル作成例を記載しました。
mysql> CREATE TABLE infra_terms (インフラ用語 varchar(255) NOT NULL, ジャンル varchar(255) NOT NULL, 説明 text(3000) NOT NULL);
csvファイル作成
私はWindowsを使っていたので、メモ帳で作成しました。保存する際に、UTF-8コードで保存して、その後scpコマンドでEC2インスタンスに送ってください。
csvのフォーマットは以下の通りです。
カラムはmysqlでテーブルを作成する際に作るので、実際はcsvファイルには記載しません。
(カラム１,カラム2,...,カラムN)
データ1-1,データ1-2,データ1-3
...
データn-1,データn-2,データn-3私はインフラ用語のクイズアプリを作りたいので、以下のようにしました。
...
SLA,災害,SLA(Service Level Agreement)は、サービスの提供事業者とその利用者の間で結ばれる、サービスのレベル（定義、範囲、内容、達成目標等）に関する合意サービス水準、サービス品質保証など、サービスの定義や通信速度、利用停止時間の上限などの保証項目を定め、そのサービス品質がSLAの保証値を下回った場合には利用料金の減額などが行われます。
...
条件確認
mysql操作
# データベースを表示
mysql> show databases;
# mysqlデータベースを選択
mysql> use mysql;
# ユーザーを表示
mysql> select User, Host from user;
# 設定されているユーザー情報全てを見やすく表示
# \Gはテーブルを縦に表示して見やすく表示するためのオプションです。
mysql> select * from user\G;上記コードで、adminとrdsadminとその他mysql.sysなどがあることを確認します。最後のSQL文を実行することで、rdsadminには全ての権限が設定されており、adminにはShutdown_priv、File_priv、Super_priv、Create_Table_Spaceのみ権限がないことがわかります。rdsadminはAWS側がRDS内の更新などを行う際に使うものらしく、我々はログインできません。また、与えられていない権限はほかのユーザーに付与することはできないので、ルートユーザーであるadminにFile権限がなく、ほかのユーザーに付与して解決することもできません。
LOAD FILEを実行したいのですが、FILE権限が与えられていないのでこのままだと実行できません。
念のため、LOAD DATAを実行してみます。
mysql> LOAD DATA LOCAL INFILE 'csvのパス' INTO TABLE 先ほど作成したテーブル名 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';案の定、エラーが出ました。
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
EC2からRDSにcsvファイルをアップロードしてデータテーブルを作成
調査
何としてもLOAD DATAを実行したいです。こちらのStackOverflowの記事に、--local-infile=1のフラグを付ければいいとありました。MySQLの公式ドキュメントを見るとより詳しく書かれていました。
以下、抜粋です。
”mysql クライアントの場合、ローカルデータロード機能は、MySQL クライアントライブラリにコンパイルされたデフォルトで決定されます。 明示的に無効または有効にするには、--local-infile=0 または --local-infile[=1]オプションを使用します。”
実行
ということで、mysqlにログインする際にオプションで--local-infile=1を追加します。
$ mysql -h RDSエンドポイント -P 3306 -u admin -p --local-infile=1
-> RDS構築時に設定したマスターパスワードを入力mysqlログイン後、
# 先ほどのLOAD DATAクエリを実行
mysql> LOAD DATA LOCAL INFILE 'csvのパス名' INTO TABLE 先ほど作成したテーブル名 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
# 下記のような成功コードが出たら完了です
Query OK, 115 rows affected (0.02 sec)
Records: 115 Deleted: 0 Skipped: 0 Warnings: 0下記クエリでどんなテーブルになったか確認します。
# テーブル内のデータ全てを見やすく表示
mysql> select * from 先ほど作成したテーブル名\G;csvで作成したように、適切なカラムに適切なデータがそれぞれ格納されていれば、csvファイルからのテーブル作成は完了です。
余談
CLIで以下を入力すると、デフォルトの設定値を見ることができて、その中に--local-infileの値も見ることができます。
$ mysql --help一般的なコマンドヘルプの後に、以下のカラムから設定が表示されます。
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
----------------------------------------- --------------------------------この中にlocal-infileがあり、FALSE(つまり0)がありました。
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
----------------------------------------- --------------------------------
...
local-infile FALSE
...また、この中には以下のようなものもあり、変更次第で色々楽しめそうです。
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
----------------------------------------- --------------------------------
...
prompt mysql>
...
まとめ
今回は、EC2からRDSにcsvファイルをアップロードしてデータテーブルを作成してみました。
デフォルト設定のままLOAD DATAクエリを実行しようとすると、File権限が与えられておらず、エラーが出ました。
ログイン時に–local-infileの値を1にすることでLOAD DATAを一時的に実行することができました。
次回はLAMP環境かNode.jsを使って、RDSのDBインスタンスからデータを取ってきてクイズを出してくれるWEBアプリを作ってみたいと思います。
ご精読ありがとうございました。