Web3層システムをAWSで作ってみる#4（EC2 DB編）

リハビリ&社内勉強用のために作成します。

用意するもの

・PC
・インターネット
・TeraTarm
・WinSCP ※ファイル転送時

用語

用語	意味
MySQL	DBサーバ用のソフトウェア（MW）

ゴール

今回はWeb3層システムをAWSで作ってみる #3（APサーバ編）の内容に追加して、新規のEC2にてDBサーバを作成しTomcatと連携させます。
なお、今回作成するDBサーバはインターネット接続の必要がない（不要なアクセスを防ぎたい）サーバになるので、本来はPrivateSubnetに配備します。が、準備が面倒なのでPublicSubnetに配置します。PrivateSubnetにサーバ作成したい方は以下「追記１」の方法で準備可能です。

手順

１．EC2の作成

まず試しにEC2を作成してみましょう。手順はWeb3層構造のシステムをAWSで作ってみる #2（Webサーバ編）を参照してください。EC2は以下設定値としてください。

項目	設定値
Amazon マシンイメージ (AMI)	Amazon Linux 2 AMI (HVM), SSD Volume Type
インスタンスタイプの選択	t2.micro
ネットワーク	vpc_yh_20210501
サブネット	PublicSubnet01_20210501
自動割り当てパブリック IP	有効

なお、今回はセキュリティグループを特に作成しません。デフォルトのセキュリティグループでもssh接続は許可されており、今回作成するDBサーバは他のポートからアクセスできるためです。

１．MySQLのインストール

AmazonLinuxには標準でMariaDBというDB用MWが入っていますが、利用しないので削除します。

[ec2-user@ip-10-0-1-112 ~]$ yum list installed | grep maria
mariadb-libs.x86_64                   1:5.5.68-1.amzn2                 installed
[ec2-user@ip-10-0-1-112 ~]$ sudo yum -y remove mariadb-libs.x86_64
#complete!と出ればOK

次にMySQLのリポジトリをインストールします。

[ec2-user@ip-10-0-1-112 ~]$sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm -y
#complete!と出ればOK
[ec2-user@ip-10-0-1-181 ~]$ sudo ls -ltr /etc/yum.repos.d/
#mysqlのレポジトリを確認

次にMySQLのインストールを行うが、デフォルトのMySQL8(8.0)をそのままインストールすると関連パッケージが足りないと怒られたので、MySQL5.7(57)をインストールするようyumの設定を書き換えてからインストールする。

[ec2-user@ip-10-0-1-181 ~]$ sudo yum-config-manager --disable mysql80-community
[ec2-user@ip-10-0-1-181 ~]$ sudo yum-config-manager --enable mysql57-community
[ec2-user@ip-10-0-1-181 ~]$ yum info mysql-community-server
From repo   : mysql57-community
[ec2-user@ip-10-0-1-181 ~]$ sudo yum install mysql-community-server -y
#complete!と出ればOK
[ec2-user@ip-10-0-1-181 ~]$ mysqld --version
mysqld  Ver 5.7.34 for Linux on x86_64 (MySQL Community Server (GPL))
#バージョン確認

試しに起動停止してみる。

[ec2-user@ip-10-0-1-181 ~]$ sudo  systemctl status mysqld
#Active: inactive (dead)を確認。
[ec2-user@ip-10-0-1-181 ~]$ sudo  systemctl start mysqld
[ec2-user@ip-10-0-1-181 ~]$ sudo  systemctl status mysqld
#Active: active (running) を確認。
[ec2-user@ip-10-0-1-181 ~]$ sudo  systemctl stop mysqld
[ec2-user@ip-10-0-1-181 ~]$ sudo  systemctl status mysqld
#Active: inactive (dead)を確認。
[ec2-user@ip-10-0-1-181 ~]$ sudo systemctl enable mysqld
#ついでにOS起動時の自動起動設定も入れておく

２．MySQLへのログイン

標準だと文字コードがItalicになっている。日本語が使えるようutf8に修正。

sudo vi /etc/my.cnf
#以下を追記
sudo mysqld --verbose --help > /dev/null
#記述内容の確認

my.cnf

character_set_server=utf8

MySQLを起動する。

[ec2-user@ip-10-0-1-181 ~]$ sudo  systemctl start mysqld
[ec2-user@ip-10-0-1-181 ~]$ sudo  systemctl status mysqld
#Active: active (running) を確認。

初期パスワードはログ参照。

[ec2-user@ip-10-0-1-181 ~]$ sudo cat /var/log/mysqld.log | grep password
2021-05-04T15:47:58.310443Z 1 [Note] A temporary password is generated for root@localhost: kNAHGj=0=3?S

PWを変更

[ec2-user@ip-10-0-1-181 ~]$ mysql -u root
> use mysql;
> ALTER USER 'root'@'localhost' identified BY 'password';
（PASSWORD関数はMySQL８では廃止されている様。）

文字コードがutf8になっていることを確認。

mysql> show variables like '%chara%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

３．DBの作成

複数のDB（データベース）の中に、複数のTBL（テーブル）が存在する。
まずは新規にDBを作成してみる。

既存のDBを見て

mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

そこに新規DBを追加してみる

mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

利用するDBを変更。

mysql> use testdb;

４．TBL作成・編集

適当にTBLを作成してみる。

mysql> CREATE TABLE users ( num INT(5) AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), yomi VARCHAR(255), year INT(4));
Query OK, 0 rows affected (0.01 sec)

行を追加してみる。

mysql> insert into users(name, yomi, year) values('新垣 結衣','あらがき ゆい',1988),('石原 さとみ','いしはら さとみ',1986);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql>  select * from users;
+-----+------------------+------------------------+------+
| num | name             | yomi                   | year |
+-----+------------------+------------------------+------+
|   1 | 新垣 結衣        | あらがき ゆい          | 1988 |
|   2 | 石原 さとみ      | いしはら さとみ        | 1986 |
+-----+------------------+------------------------+------+
2 rows in set (0.00 sec)

行を削除してみる。

mysql> DELETE FROM users WHERE name = '石原 さとみ';
Query OK, 1 row affected (0.00 sec)

mysql>  select * from users;
+-----+---------------+---------------------+------+
| num | name          | yomi                | year |
+-----+---------------+---------------------+------+
|   1 | 新垣 結衣     | あらがき ゆい       | 1988 |
+-----+---------------+---------------------+------+
1 row in set (0.00 sec)

ユーザ作成してリモート接続権限を付与する。

mysql> CREATE USER 'itskill'@'%' IDENTIFIED BY 'itskill';　
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'itskill'@'%' WITH GRANT  OPTION;

TomcatとDBの連携

次にTomcatの入ったEC2の方にドライバを入れてDBと連携させる。
jarファイルはこちらからダウンロード。MySqlのバージョンとの互換性を要確認（ReadMeのリンク先からMySQLのドキュメントページに飛べる。）。

[ec2-user@ip-10-0-1-181 ~]$sudo cd /tmp
[ec2-user@ip-10-0-1-181 ~]$sudo curl -O https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.12/mysql-connector-java-8.0.12.jar
[ec2-user@ip-10-0-1-181 ~]$sudo mv /tmp/mysql-connector-java-8.0.12.jar /opt/apache-tomcat-9.0.45/lib/
[ec2-user@ip-10-0-1-181 ~]$sudo chown tomcat:tomcat /opt/apache-tomcat-9.0.45/lib/mysql-connector-java-8.0.12.jar 

続いてserver.xmlに設定を追記する。

[ec2-user@ip-10-0-1-181 ~]$sudo vi /opt/apache-tomcat-9.0.45/conf/server.xml

server.xml

<Context docBase="itskill" path="/itskill" reloadable="false" source="orrg.eclipse.jst.jee.server:itskill">
    <Resource   
        auth="Container"driverClassName="com.mysql.cj.jdbc.Driver"
        name="jdbc/datasource"
        type="javax.sql.DataSource"                
        url="jdbc:mysql://[ローカルIP]/testdb"
        connectionProperties="characterEncoding=utf8;rewriteBatchedStatements=true;useSSL=false;requireSSL=false"
        username="[ユーザ名]"
        password="[PW]"
    />
</Context>

最後にTomcatを再起動して設定を反映する。

追記１：PrivateSubnetにおけるNW環境整備

課題とやることを記載します。

課題①：グローバルIPがないからsshアクセスできない！

PublicSubnetに作成したEC2を踏み台に、PrivateSubnetのEC2へssh接続します。PublicSubnetのEC2に秘密鍵を置いてアクセスしたり、ALB経由でアクセスしたりもできますが、セキュリティ観点上微妙なので踏み台がベストプラクティスだと思います。
TeraTerm前提であれば「Amazon EC2」へ、踏み台サーバから「SSHポートフォワード」経由で接続するで可能です。

課題②：EC2からインターネットへ接続できない！

yumなどパッケージ系のインストール時、インターネットに接続できないパターンです。
踏み台EC2側でインストール用ファイルを経由する手もありますが、yumなどのコマンドが使えなくなるのでAWSベストプラクティスらしくNATゲートウェイを作成します。NATゲートウェイはAWSが提供する踏み台サービスで、これをPublicSubnetに置いて、代わりにインターネットに接続してもらいます。
NAT ゲートウェイをセットアップするにはどうすればいいですかや
NAT ゲートウェイを設定してみる - EC2で可能です。

追記2：RDSの場合

見ながらやろう! AWSを始めよう -RDS編-参照。
ポイントは以下の通り。
・yum -y install mysqlでmysql cliantを落とす。
・SG設定は3306でインバウンド通信をWeb側のSG（もしくは任意の場所）に許可設定入れておく。Web側のインバウンドにも3306通信の許可設定を入れておく。
・subnetは事前に2つ以上作っておく。
・接続はmysql -h [endpoint] -P 3306 -u [ユーザ名] -p

参考

①CentOS7にMySQLをインストールして初期設定するまで
②[AWS]EC2(Amazon Linux)にMySQL5.7をインストールする
③基礎MySQL ~その２~ my.cnf (設定ファイル)
https://qiita.com/miriwo/items/eb09c065ee9bb7e8fe06
④Mysql 5.7* パスワードをPolicyに合わせるとめんどくさい件について
⑤CentOS7にMySQL公式リポジトリを使って最新のMySQL8.0をインストール
⑥[CentOS7][MySQL5.7]ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
⑦CentOS7でMySQLにログインできない
⑧外部のホストから接続できるようにする方法

備忘：ログインできなくなった時

以下を参照。
CentOS7でMySQL5.7のパスワードリセット