OCIで簡単な環境を作ってみた(その2)

下記の続きです

OCIで簡単な環境を作ってみた(その1)
https://qiita.com/Cache-Hit/items/994a0095e8965912e677

下記の構成を作成します。
Public SubnetにあるVPNサーバと連携までは前回記事で用意が完了しています。
※疎通用にServerだけは適当に作って起動はしていますが、新たに作成し直します。

![ga検証環境.drawio.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3767488/f26d91f0-80be-da39-915b-782a7f42f74a.png)


# 今回作成する内容

* Serverの作成
* Apacheの設定とWordpressのインストール
* Base Databaseの作成
* データベース作成
* Wordpressの連携と接続確認

最終的にBase Databaseを用いたWordpressをローカルIPで接続をすることで
VPN経由で作成することができたとします。

今回Serverを設定するにあたり、アウトバウンドの通信が取れないので、
一時的にNAT GATEWAYを作成してアタッチする。

# NAT GATEWAY作成

あらかじめNAT GATEWAYという名前で作成しておく。

Private SubnetAのルート・ルールに一時的に追加する。

<table>
  <caption>ルート・ルール</caption>
  <thead>
    <tr>
       <th>宛先</th> <th>ターゲットタイプ</th> <th>ターゲット</th> <th>ルート・タイプ</th>
    </tr>
  </thead>
  <tr>
    <td> 0.0.0.0/0 </td> <td>NATゲートウェイ</td> <td> NAT GATEWAY </td> <td> 静的 </td>
  </tr>

</table>

# Server作成
VPNサーバと同じOSでインスタンスを起動する。

スペックは今回は適当にVM.Standard3.FlexでCPU 2 メモリ15GB にする。
ブートボリュームは最低サイズの50GBで作っておく。


作成後、ローカルIPは10.0.1.18になったので、接続確認をします。

* ping
```
C:\Users\81803>ping 10.0.1.18

10.0.1.18 に ping を送信しています 32 バイトのデータ:
10.0.1.18 からの応答: バイト数 =32 時間 =16ms TTL=63
10.0.1.18 からの応答: バイト数 =32 時間 =13ms TTL=63
10.0.1.18 からの応答: バイト数 =32 時間 =13ms TTL=63
10.0.1.18 からの応答: バイト数 =32 時間 =12ms TTL=63

10.0.1.18 の ping 統計:
    パケット数: 送信 = 4、受信 = 4、損失 = 0 (0% の損失)、
ラウンド トリップの概算時間 (ミリ秒):
    最小 = 12ms、最大 = 16ms、平均 = 13ms

C:\Users\81803>
```

* ssh
```
Activate the web console with: systemctl enable --now cockpit.socket

[opc@server ~]$ sudo su -
[root@server ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 9000 qdisc fq_codel state UP group default qlen 1000
    link/ether 02:00:17:04:ae:0e brd ff:ff:ff:ff:ff:ff
    altname enp0s3
    altname ens3
    inet 10.0.1.18/24 brd 10.0.1.255 scope global dynamic noprefixroute eth0
       valid_lft 86236sec preferred_lft 86236sec
    inet6 fe80::17ff:fe04:ae0e/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
[root@server ~]#
```

* 外部への通信を試みる。

```
[root@server ~]# curl -I google.com
HTTP/1.1 301 Moved Permanently
Location: http://www.google.com/
Content-Type: text/html; charset=UTF-8
Content-Security-Policy-Report-Only: object-src 'none';base-uri 'self';script-src 'nonce-ahzO_m3lZ7Qyt8zirrSMJw' 'strict-dynamic' 'report-sample' 'unsafe-eval' 'unsafe-inline' https: http:;report-uri https://csp.withgoogle.com/csp/gws/other-hp
Date: Fri, 26 Apr 2024 05:47:03 GMT
Expires: Sun, 26 May 2024 05:47:03 GMT
Cache-Control: public, max-age=2592000
Server: gws
Content-Length: 219
X-XSS-Protection: 0
X-Frame-Options: SAMEORIGIN

```


準備ができたので設定をしていきます。

* SELINUX
```
[root@server ~]# vi  /etc/selinux/config
[root@server ~]# getenforce
Enforcing
[root@server ~]# grep "SELINUX=" /etc/selinux/config
# SELINUX= can take one of these three values:
```

```ruby:firewalld停止と自動起動無効化
[root@server ~]# systemctl stop firewalld
[root@server ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
```

```ruby:dnf updateとreboot
[root@server ~]# dnf update -y
略
[root@server ~]# reboot
```


```ruby:必要な最低限をインストール
[root@server ~]# dnf install curl net-tools sysstat bind-utils yum-utils dstat wget rpm-build make git lsof telnet unzip

```


```ruby:Apacheのインストール
[root@server ~]# dnf install httpd httpd-devel mailx

```


```ruby:自動起動
[root@server ~]# systemctl enable httpd
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
```

今回はローカル内のみかつHTTPのみで接続するため、
適当にバーチャルホストを設定します。

```ruby:ディレクトリ作成
[root@server ~]# mkdir -p /var/www/vhosts/example.domain.com/public_html
[root@server ~]# echo example.domain.com > /var/www/vhosts/example.domain.com/public_html/index.html
[root@server ~]# chown apache:apache /var/www/ -R
[root@server ~]# find /var/www/vhosts -type d |xargs -I@ chmod 775 @
[root@server ~]# find /var/www/vhosts -type f |xargs -I@ chmod 664 @
[root@server ~]#

```

```ruby:バーチャルホスト作成
[root@server ~]# vi /etc/httpd/conf.d/example.domain.com.conf
[root@server ~]# cat /etc/httpd/conf.d/example.domain.com.conf
<VirtualHost *:80>
    ServerName  example.domain.com
    ServerAlias www.example.domain.com
    DocumentRoot "/var/www/vhosts/example.domain.com/public_html"
    ErrorLog  /var/log/httpd/example.domain.com.error_log
    CustomLog /var/log/httpd/example.domain.com.access_log combined

    <Directory "/var/www/vhosts/example.domain.com/public_html" >
        Options FollowSymLinks ExecCGI
        AllowOverride All
        Require all granted

    </Directory>
 </VirtualHost>
[root@server ~]#
```

```ruby:/etc/httpd/conf.d/common.confを用意
[root@server ~]# cat /etc/httpd/conf.d/common.conf
ServerTokens Prod
ServerSignature Off
TraceEnable Off
FileETag none

KeepAlive On
MaxKeepAliveRequests 60
KeepAliveTimeout 3

#event


<IfModule prefork.c>
StartServers         8
MinSpareServers      5
MaxSpareServers      20
ServerLimit          256
MaxRequestWorkers    256
MaxRequestsPerChild  4000


#</IfModule>

#<IfModule mpm_event_module>
#StartServers           2
#ServerLimit            16
#MinSpareThreads        50
#MaxSpareThreads        250
#ThreadLimit            25
#ThreadsPerChild        25
#MaxRequestWorkers      400
#MaxConnectionsPerChild 400
#</IfModule>

#RemoteIPHeader X-Forwarded-For
#RemoteIPInternalProxy 10.0.0.0/16

LanguagePriority ja en ca cs da de el eo es et fr he hr it ko ltz nl nn no pl pt pt-BR ru sv zh-CN zh-TW

Header set X-XSS-Protection "1; mode=block"
Header set X-Content-Type-Options nosniff
#Header append X-Frame-Options SAMEORIGIN
#Header add Content-Security-Policy "upgrade-insecure-requests"

DirectoryIndex index.php index.html
```

```ruby:preforkに切り替え
[root@server ~]# vi /etc/httpd/conf.modules.d/00-mpm.conf
[root@server ~]# cat /etc/httpd/conf.modules.d/00-mpm.conf |grep -v -e '^\s*#' -e '^\s*$'
LoadModule mpm_prefork_module modules/mod_mpm_prefork.so
```

```ruby:ServerName編集
[root@server ~]# vi /etc/httpd/conf/httpd.conf
[root@server ~]# grep ^ServerName /etc/httpd/conf/httpd.conf
ServerName www.example.com:80

```

```ruby:起動
[root@server ~]# httpd -t
Syntax OK

[root@server ~]# systemctl start httpd
[root@server ~]#

```

```ruby:ローテート設定
[root@server ~]# vi /etc/logrotate.d/httpd
[root@server ~]# cat /etc/logrotate.d/httpd
 /var/log/httpd/*log {
    daily
    rotate 30
    missingok
    notifempty
    sharedscripts
    compress
    postrotate
        /bin/systemctl reload httpd.service > /dev/null 2>/dev/null || true
    endscript
}
```

クライアントからアクセス確認
![ブラウザ.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3767488/30f0d917-40bf-9b40-38d2-a294ef1efc42.png)


```ruby:ログ確認
[root@server ~]# cat /var/log/httpd/example.domain.com.access_log
10.0.0.53 - - [26/Apr/2024:06:22:54 +0000] "GET / HTTP/1.1" 200 19 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36"
10.0.0.53 - - [26/Apr/2024:06:22:54 +0000] "GET /favicon.ico HTTP/1.1" 404 196 "http://10.0.1.18/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36"
10.0.0.53 - - [26/Apr/2024:06:23:46 +0000] "-" 408 - "-" "-"
```
VPN経由で接続ができていることが確認できた。


# PHPのインストール
```ruby:今何が入るか確認
[root@server ~]# dnf module list  php
Last metadata expiration check: 0:30:41 ago on Fri 26 Apr 2024 05:59:55 AM UTC.
AlmaLinux 8 - AppStream
Name            Stream            Profiles                             Summary
php             7.2 [d]           common [d], devel, minimal           PHP scripting language
php             7.3               common [d], devel, minimal           PHP scripting language
php             7.4               common [d], devel, minimal           PHP scripting language
php             8.0               common [d], devel, minimal           PHP scripting language

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
```

今回はOS標準レポジトリから8.0を入れる。
8.0を入れておく
```ruby:
[root@server ~]# dnf module install php:8.0
Last metadata expiration check: 0:33:21 ago on Fri 26 Apr 2024 05:59:55 AM UTC.
Dependencies resolved.
====================================================================================================================================================
 Package                         Architecture          Version                                                       Repository                Size
====================================================================================================================================================
Installing group/module packages:
 php-cli                         x86_64                8.0.30-1.module_el8.8.0+3655+3335cff6                         appstream                3.1 M
 php-common                      x86_64                8.0.30-1.module_el8.8.0+3655+3335cff6                         appstream                725 k
 php-fpm                         x86_64                8.0.30-1.module_el8.8.0+3655+3335cff6                         appstream                1.6 M
 php-mbstring                    x86_64                8.0.30-1.module_el8.8.0+3655+3335cff6                         appstream                481 k
 php-xml                         x86_64                8.0.30-1.module_el8.8.0+3655+3335cff6                         appstream                176 k
Installing dependencies:
 libxslt                         x86_64                1.1.32-6.el8                                                  baseos                   248 k
 nginx-filesystem                noarch                1:1.14.1-9.module_el8.3.0+2165+af250afe.alma                  appstream                 23 k
 oniguruma                       x86_64                6.8.2-2.1.el8_9                                               appstream                187 k
Installing module profiles:
 php/common
Enabling module streams:
 nginx                                                 1.14
 php                                                   8.0

Transaction Summary
====================================================================================================================================================
Install  8 Packages

Total download size: 6.6 M
Installed size: 29 M
Is this ok [y/N]:
```

```ruby:php-fpmを今回は使わないので削除
[root@server ~]# dnf remove php-fpm
Dependencies resolved.
====================================================================================================================================================
 Package                         Architecture          Version                                                      Repository                 Size
====================================================================================================================================================
Removing:
 php-fpm                         x86_64                8.0.30-1.module_el8.8.0+3655+3335cff6                        @appstream                6.2 M
Removing unused dependencies:
 nginx-filesystem                noarch                1:1.14.1-9.module_el8.3.0+2165+af250afe.alma                 @appstream                  0

Transaction Summary
====================================================================================================================================================
Remove  2 Packages

Freed space: 6.2 M
Is this ok [y/N]: y

```

```ruby:追加モジュールをインストール
[root@server ~]# dnf install php-mysqli
Last metadata expiration check: 0:37:40 ago on Fri 26 Apr 2024 05:59:55 AM UTC.
Dependencies resolved.
====================================================================================================================================================
 Package                       Architecture             Version                                                   Repository                   Size
====================================================================================================================================================
Installing:
 php-mysqlnd                   x86_64                   8.0.30-1.module_el8.8.0+3655+3335cff6                     appstream                   192 k
Installing dependencies:
 php-pdo                       x86_64                   8.0.30-1.module_el8.8.0+3655+3335cff6                     appstream                   126 k

Transaction Summary
====================================================================================================================================================
Install  2 Packages

Total download size: 318 k
Installed size: 666 k
```

```ruby:mysqlクライアント用意
[root@server ~]# dnf install  mysql
Last metadata expiration check: 0:40:28 ago on Fri 26 Apr 2024 05:59:55 AM UTC.
Dependencies resolved.
====================================================================================================================================================
 Package                                  Architecture         Version                                                Repository               Size
====================================================================================================================================================
Installing:
 mysql                                    x86_64               8.0.36-1.module_el8.9.0+3735+82bd6c11                  appstream                14 M
Installing dependencies:
 mariadb-connector-c-config               noarch               3.1.11-2.el8_3                                         appstream                14 k
 mysql-common                             x86_64               8.0.36-1.module_el8.9.0+3735+82bd6c11                  appstream               136 k
Enabling module streams:
 mysql                                                         8.0

Transaction Summary
====================================================================================================================================================
Install  3 Packages

Total download size: 15 M
Installed size: 73 M
Is this ok [y/N]:
```

```ruby:Wordpress用意
[root@server ~]# cd /usr/local/src/
[root@server ~]# cd /usr/local/src/
[root@server src]# curl -O https://ja.wordpress.org/latest-ja.zip
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 26.1M  100 26.1M    0     0  9238k      0  0:00:02  0:00:02 --:--:-- 9235k
[root@server src]# unzip latest-ja.zip
Archive:  latest-ja.zip
```

```ruby:examle.comのディレクトリへコピー
[root@server src]# cp -a wordpress/. /var/www/vhosts/example.domain.com/public_html/
[root@server src]#

[root@server src]# ll /var/www/vhosts/example.domain.com/public_html/
total 232
-rw-rw-r--  1 apache apache    19 Apr 26 06:08 index.html
-rw-r--r--  1 root   root     405 Feb  6  2020 index.php
-rw-r--r--  1 root   root   19915 Jan  1 00:02 license.txt
-rw-r--r--  1 root   root    7401 Dec  8 14:13 readme.html
-rw-r--r--  1 root   root    7387 Feb 13 14:19 wp-activate.php
drwxr-xr-x  9 root   root    4096 Apr 12 14:00 wp-admin
-rw-r--r--  1 root   root     351 Feb  6  2020 wp-blog-header.php
-rw-r--r--  1 root   root    2323 Jun 14  2023 wp-comments-post.php
-rw-r--r--  1 root   root    3012 Nov 22 17:44 wp-config-sample.php
drwxr-xr-x  5 root   root      69 Apr 12 14:00 wp-content
-rw-r--r--  1 root   root    5638 May 30  2023 wp-cron.php
drwxr-xr-x 30 root   root   12288 Apr 12 14:00 wp-includes
-rw-r--r--  1 root   root    2502 Nov 26  2022 wp-links-opml.php
-rw-r--r--  1 root   root    3927 Jul 16  2023 wp-load.php
-rw-r--r--  1 root   root   50917 Jan 16 17:31 wp-login.php
-rw-r--r--  1 root   root    8525 Sep 16  2023 wp-mail.php
-rw-r--r--  1 root   root   28427 Mar  2 10:47 wp-settings.php
-rw-r--r--  1 root   root   34385 Jun 19  2023 wp-signup.php
-rw-r--r--  1 root   root    4885 Jun 22  2023 wp-trackback.php
-rw-r--r--  1 root   root    3246 Mar  2 13:49 xmlrpc.php
[root@server src]#
```

```ruby:htmlが優先されても邪魔なので削除
[root@server src]# rm /var/www/vhosts/example.domain.com/public_html/index.html
rm: remove regular file '/var/www/vhosts/example.domain.com/public_html/index.html'? y
```

```ruby:権限編集
[root@server ~]# chown apache:apache /var/www/ -R
[root@server ~]# find /var/www/vhosts -type d |xargs -I@ chmod 775 @
[root@server ~]# find /var/www/vhosts -type f |xargs -I@ chmod 664 @
[root@server ~]#
```

あとはデータベースができてからする。

# MySQL HeatWave構築
今MySQL使えるのがビッグデータ向けのMySQL HeatWaveにして作成しました。

下記スペックで作成

シェイプ:MySQL.2
ECPU数:2
メモリー:16 GB
ストレージ・サイズ:50 GB
MySQLバージョン:8.0.36 - バグ修正


 また、PrivateSubnetのルートルールに
 サービス・ゲートウェイを追加する。


<table>
  <caption>ルート・ルール</caption>
  <thead>
    <tr>
       <th>宛先</th> <th>ターゲットタイプ</th> <th>ターゲット</th> <th>ルート・タイプ</th>
    </tr>
  </thead>
  <tr>
    <td> 0.0.0.0/0 </td> <td>NATゲートウェイ</td> <td> NAT GATEWAY </td> <td> 静的 </td>
  </tr>
  <tr>
    <td> All NRT Services In Oracle Services Network </td> <td>サービス・ゲートウェイ</td> <td> Service Gateway </td> <td> 静的 </td>
  </tr>
</table>




```ruby:接続確認
[root@server src]# mysql -h 10.0.2.4 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.36-u2-cloud MySQL Enterprise - Cloud

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
```

```ruby:権限確認
mysql> show grants \G
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 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, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,CONNECTION_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,REPLICATION_APPLIER,ROLE_ADMIN,SHOW_ROUTINE,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `root`@`%`
*************************** 4. row ***************************
Grants for root@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_audit`.* FROM `root`@`%`
*************************** 5. row ***************************
Grants for root@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `sys`.* FROM `root`@`%`
*************************** 6. row ***************************
Grants for root@%: GRANT PROXY ON ``@`` TO `root`@`%` WITH GRANT OPTION
*************************** 7. row ***************************
Grants for root@%: GRANT `administrator`@`%` TO `root`@`%` WITH ADMIN OPTION
7 rows in set (0.00 sec)
```

```ruby:DB確認
mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_audit        |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>



```

```ruby:今回コンソールで作成時に設定したユーザはroot
mysql> SELECT User, Host FROM mysql.user;
+--------------------+-----------+
| User               | Host      |
+--------------------+-----------+
| administrator      | %         |
| ocirpl             | %         |
| root               | %         |
| ociadmin           | 127.0.0.1 |
| ocidbm             | 127.0.0.1 |
| mysql.infoschema   | localhost |
| mysql.session      | localhost |
| mysql.sys          | localhost |
| oracle-cloud-agent | localhost |
+--------------------+-----------+
9 rows in set (0.00 sec)
```



```ruby:
```

```ruby:
```

```ruby:
```

```ruby:
```


```ruby:
```

```ruby:
```

```ruby:
```

```ruby:
```


```ruby:
```

```ruby:
```

```ruby:
```

```ruby:
```


```ruby:
```

```ruby:
```

```ruby:
```

```ruby:
```