MySQLを使って実施したい検証作業があるのですが、課金が発生しないようAlways FreeのAmpere A1インスタンスにMySQL Serverをインストールしてみました。実施予定の検証は、Oracle Cloudのデータベース・ツールの接続を使ったMySQLサーバーへの接続なので、SSL化が必須です。
米国オラクル社のKris Riceさんのブログ記事MySQL And Lets Encryptを参照しています。MySQLはほとんど触ったことがないので、基本的な作業も分かりませんでした。そういった作業も含めて、記事にします。
仮想クラウド・ネットワークは事前に作成しておきます。
Ampere A1インスタンスの作成
Oracle Cloudのコンソールのインスタンスのコンピュートを開き、インスタンスの作成を実行します。
DNSへのホスト名とIPアドレスの登録
コンピュート・インスタンスの準備
sudo dnf -y update
[opc@mysql ~]$ sudo dnf -y update
Failed to set locale, defaulting to C.UTF-8
Ksplice for Oracle Linux 8 (aarch64) 4.0 MB/s | 369 kB 00:00
MySQL 8.0 for Oracle Linux 8 (aarch64) 26 MB/s | 2.6 MB 00:00
MySQL 8.0 Tools Community for Oracle Linux 8 (aarch64) 4.7 MB/s | 228 kB 00:00
MySQL 8.0 Connectors Community for Oracle Linux 8 (aarch64) 380 kB/s | 23 kB 00:00
Oracle Software for OCI users on Oracle Linux 8 (aarch64) 96 MB/s | 42 MB 00:00
[中略]
tuned-profiles-oci-2.18.0-2.0.1.el8_6.1.noarch
tuned-profiles-oci-recommend-2.18.0-2.0.1.el8_6.1.noarch
vim-common-2:8.0.1763-19.0.1.el8_6.2.aarch64
vim-enhanced-2:8.0.1763-19.0.1.el8_6.2.aarch64
vim-filesystem-2:8.0.1763-19.0.1.el8_6.2.noarch
vim-minimal-2:8.0.1763-19.0.1.el8_6.2.aarch64
Installed:
kernel-uek-5.4.17-2136.309.5.el8uek.aarch64 kernel-uek-devel-5.4.17-2136.309.5.el8uek.aarch64
Complete!
[opc@mysql ~]$
ファイアウォールの設定を変更します。ポート80、443、3306、33060への接続許可を与えます。
sudo firewall-cmd --add-port=80/tcp
sudo firewall-cmd --add-port=33060/tcp
sudo firewall-cmd --runtime-to-permanent
sudo firewall-cmd --reload
sudo firewall-cmd --list-all
[opc@mysql ~]$ sudo firewall-cmd --add-port=80/tcp
success
[opc@mysql ~]$ sudo firewall-cmd --add-port=443/tcp
success
[opc@mysql ~]$ sudo firewall-cmd --add-port=3306/tcp
success
[opc@mysql ~]$ sudo firewall-cmd --add-port=33060/tcp
success
[opc@mysql ~]$ sudo firewall-cmd --runtime-to-permanent
success
[opc@mysql ~]$ sudo firewall-cmd --reload
success
[opc@mysql ~]$ sudo firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: enp0s3
sources:
services: ssh
ports: 80/tcp 443/tcp 3306/tcp 33060/tcp
protocols:
forward: no
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
[opc@mysql ~]$
[opc@mysql ~]$ sudo dnf -y --enablerepo ol8_developer_EPEL install certbot
Failed to set locale, defaulting to C.UTF-8
Oracle Linux 8 EPEL Packages for Development (aarch64) 88 MB/s | 31 MB 00:00
Last metadata expiration check: 0:00:07 ago on Fri Aug 5 06:38:43 2022.
Dependencies resolved.
======================================================================================================
Package Arch Version Repository Size
======================================================================================================
Installing:
certbot noarch 1.22.0-1.el8 ol8_developer_EPEL 55 k
Installing dependencies:
python3-acme noarch 1.22.0-1.el8 ol8_developer_EPEL 97 k
python3-certbot noarch 1.22.0-1.el8 ol8_developer_EPEL 427 k
python3-configargparse noarch 0.14.0-6.el8 ol8_developer_EPEL 37 k
python3-distro noarch 1.4.0-2.module+el8.3.0+7694+550a8252 ol8_appstream 37 k
python3-josepy noarch 1.9.0-1.el8 ol8_developer_EPEL 104 k
python3-parsedatetime noarch 2.5-1.el8 ol8_developer_EPEL 80 k
python3-pyrfc3339 noarch 1.1-1.el8 ol8_developer_EPEL 20 k
python3-requests-toolbelt noarch 0.9.1-4.el8 ol8_developer_EPEL 92 k
python3-zope-component noarch 4.3.0-8.el8 ol8_developer_EPEL 314 k
python3-zope-event noarch 4.2.0-12.el8 ol8_developer_EPEL 211 k
python3-zope-interface aarch64 4.6.0-1.el8 ol8_developer_EPEL 159 k
Transaction Summary
======================================================================================================
Install 12 Packages
Total download size: 1.6 M
[中略]
Installed:
certbot-1.22.0-1.el8.noarch
python3-acme-1.22.0-1.el8.noarch
python3-certbot-1.22.0-1.el8.noarch
python3-configargparse-0.14.0-6.el8.noarch
python3-distro-1.4.0-2.module+el8.3.0+7694+550a8252.noarch
python3-josepy-1.9.0-1.el8.noarch
python3-parsedatetime-2.5-1.el8.noarch
python3-pyrfc3339-1.1-1.el8.noarch
python3-requests-toolbelt-0.9.1-4.el8.noarch
python3-zope-component-4.3.0-8.el8.noarch
python3-zope-event-4.2.0-12.el8.noarch
python3-zope-interface-4.6.0-1.el8.aarch64
Complete!
[opc@mysql ~]$
Certbotを実行して、サーバー証明書を取得します。
sudo certbot certonly --standalone[opc@mysql ~]$ sudo certbot certonly --standalone
Saving debug log to /var/log/letsencrypt/letsencrypt.log
Enter email address (used for urgent renewal and security notices)
(Enter 'c' to cancel): メール・アドレスの入力
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Please read the Terms of Service at
https://letsencrypt.org/documents/LE-SA-v1.2-November-15-2017.pdf. You must
agree in order to register with the ACME server. Do you agree?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
(Y)es/(N)o: Y
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Would you be willing, once your first certificate is successfully issued, to
share your email address with the Electronic Frontier Foundation, a founding
partner of the Let's Encrypt project and the non-profit organization that
develops Certbot? We'd like to send you email about our work encrypting the web,
EFF news, campaigns, and ways to support digital freedom.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
(Y)es/(N)o: N
Account registered.
Please enter the domain name(s) you would like on your certificate (comma and/or
space separated) (Enter 'c' to cancel): mysql.mydomain.dev
Requesting a certificate for mysql.mydomain.dev
Successfully received certificate.
Certificate is saved at: /etc/letsencrypt/live/mysql.mydomain.dev/fullchain.pem
Key is saved at: /etc/letsencrypt/live/mysql.mydomain.dev/privkey.pem
This certificate expires on 2022-11-03.
These files will be updated when the certificate renews.
Certbot has set up a scheduled task to automatically renew this certificate in the background.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
If you like Certbot, please consider supporting our work by:
* Donating to ISRG / Let's Encrypt: https://letsencrypt.org/donate
* Donating to EFF: https://eff.org/donate-le
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
[opc@mysql ~]$
コマンドが終了すると、/etc/letsencrypt/live/ホスト名/以下に、MySQLをSSL化するために必要な、秘密キー（privkey.pem）、サーバー証明書（cert.pem）、CA証明書（chain.pem、fullchain.pem）のファイルが作成されています。
sudo shutdown -r now
MySQL Serverのインストールとセットアップ
[opc@mysql ~]$ sudo dnf -y install mysql-server
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:25:45 ago on Fri Aug 5 06:24:29 2022.
Dependencies resolved.
======================================================================================================
Package Arch Version Repository Size
======================================================================================================
Installing:
mysql-server aarch64 8.0.26-1.module+el8.4.0+20311+30d12931 ol8_appstream 30 M
Installing dependencies:
mariadb-connector-c-config noarch 3.1.11-2.el8_3 ol8_appstream 15 k
mecab aarch64 0.996-1.module+el8.0.0+5253+1dce7bb2.9 ol8_appstream 367 k
mysql aarch64 8.0.26-1.module+el8.4.0+20311+30d12931 ol8_appstream 13 M
mysql-common aarch64 8.0.26-1.module+el8.4.0+20311+30d12931 ol8_appstream 134 k
mysql-errmsg aarch64 8.0.26-1.module+el8.4.0+20311+30d12931 ol8_appstream 598 k
protobuf-lite aarch64 3.5.0-13.el8 ol8_appstream 129 k
Enabling module streams:
mysql 8.0
Transaction Summary
======================================================================================================
Install 7 Packages
[中略]
Installed:
mariadb-connector-c-config-3.1.11-2.el8_3.noarch
mecab-0.996-1.module+el8.0.0+5253+1dce7bb2.9.aarch64
mysql-8.0.26-1.module+el8.4.0+20311+30d12931.aarch64
mysql-common-8.0.26-1.module+el8.4.0+20311+30d12931.aarch64
mysql-errmsg-8.0.26-1.module+el8.4.0+20311+30d12931.aarch64
mysql-server-8.0.26-1.module+el8.4.0+20311+30d12931.aarch64
protobuf-lite-3.5.0-13.el8.aarch64
Complete!
[opc@mysql ~]$
MySQLを起動し、ユーザーrootにて接続できることを確認します。
[opc@mysql ~]$ sudo systemctl start mysqld
[opc@mysql ~]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, 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> status
--------------
mysql Ver 8.0.26 for Linux on aarch64 (Source distribution)
Connection id: 11
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.26 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 3 min 3 sec
Threads: 2 Questions: 16 Slow queries: 0 Opens: 120 Flush tables: 3 Open tables: 36 Queries per second avg: 0.087
--------------
mysql> exit
Bye
[opc@mysql ~]$
サンプル・データベースsakilaをインストールします。
unzip sakila-db.zip
[opc@mysql ~]$ curl -OL http://downloads.mysql.com/docs/sakila-db.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
100 712k 100 712k 0 0 1509k 0 --:--:-- --:--:-- --:--:-- 1509k
[opc@mysql ~]$ unzip sakila-db.zip
Archive: sakila-db.zip
creating: sakila-db/
inflating: sakila-db/sakila-data.sql
inflating: sakila-db/sakila-schema.sql
inflating: sakila-db/sakila.mwb
[opc@mysql ~]$
sakila-schema.sqlとsakila-data.sqlを実行します。
[opc@mysql ~]$ cd sakila-db
[opc@mysql sakila-db]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, 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> source sakila-schema.sql;
Query OK, 0 rows affected (0.00 sec)
[中略]
Query OK, 0 rows affected (0.00 sec)
mysql> source sakila-data.sql;
Query OK, 0 rows affected (0.00 sec)
[中略]
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> use sakila;
Database changed
mysql> select * from film;
+---------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+--------------------------------------------------------+---------------------+
| film_id | title | description | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | special_features | last_update |
+---------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+--------------------------------------------------------+---------------------+
| 1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies | 2006 | 1 | NULL | 6 | 0.99 | 86 | 20.99 | PG | Deleted Scenes,Behind the Scenes | 2006-02-15 05:03:42 |
| 2 | ACE GOLDFINGER | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China | 2006 | 1 | NULL | 3 | 4.99 | 48 | 12.99 | G | Trailers,D
[中略]
| 1000 | ZORRO ARK | A Intrepid Panorama of a Mad Scientist And a Boy who must Redeem a Boy in A Monastery | 2006 | 1 | NULL | 3 | 4.99 | 50 | 18.99 | NC-17 | Trailers,Commentaries,Behind the Scenes | 2006-02-15 05:03:42 |
+---------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+--------------------------------------------------------+---------------------+
1000 rows in set (0.00 sec)
mysql> exit
Bye
[opc@mysql sakila-db]$
create user 'gateway_user' identified by 'パスワード';
grant all privileges on *.* to 'gateway_user'@'%' with grant option;
exit
[opc@mysql ~]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, 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> create user 'gateway_user' identified by 'パスワード';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'gateway_user'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[opc@mysql ~]$
gateway_userのバスワードは、Oracle Cloudのボールト・サービスにシークレットとして登録します。データベース・ツールの接続は、ボールトに登録されたシークレットから、MySQLへの接続に使用するユーザーgateway_userのパスワードを取り出します。
MySQLのSSL化
mkdir /etc/mysql
cp /etc/letsencrypt/live/mysql.mydomain.dev/*.pem /etc/mysql/
chown -R mysql /etc/mysql
[opc@mysql ~]$ sudo -s
[root@mysql opc]# mkdir /etc/mysql
[root@mysql opc]# cp /etc/letsencrypt/live/mysql.mydomain.dev/*.pem /etc/mysql/
[root@mysql opc]# chown -R mysql /etc/mysql
[root@mysql opc]#
MySQLの設定ファイル/etc/my.cnf.d/mysql-server.cnfを更新します。
ssl_ca=/etc/mysql/chain.pem
ssl_cert=/etc/mysql/cert.pem
ssl_key=/etc/mysql/privkey.pem
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
# SSL
ssl_ca=/etc/mysql/chain.pem
ssl_cert=/etc/mysql/cert.pem
ssl_key=/etc/mysql/privkey.pem
sudo systemctl restart mysqld
[opc@mysql ~]$ sudo systemctl restart mysqld
[opc@mysql ~]$
ルートCAの証明書をダウンロードします。
curl -sO https://curl.se/ca/cacert.pem
[opc@mysql ~]$ curl -sO https://curl.se/ca/cacert.pem
[opc@mysql ~]$
status
exit
[opc@mysql ~]$ mysql -u gateway_user -p --ssl-ca=cacert.pem
Enter password: パスワード
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, 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.
You are enforcing ssl connection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> status
--------------
mysql Ver 8.0.26 for Linux on aarch64 (Source distribution)
Connection id: 37
Current database:
Current user: gateway_user@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.26 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 3 min 1 sec
Threads: 2 Questions: 5 Slow queries: 0 Opens: 117 Flush tables: 3 Open tables: 36 Queries per second avg: 0.027
--------------
mysql> exit
Bye
[opc@mysql ~]$
最後にユーザーrootにパスワードを設定します。
[opc@mysql ~]$ mysqladmin -u root password
New password: パスワード
Confirm new password: パスワード
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[opc@mysql ~]$
必要に応じて自動起動の設定を行います。
[opc@mysql ~]$ sudo systemctl enable mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[opc@mysql ~]$
以上でMySQLの構成は完了です。
データベース・ツールの接続の構成
データベース・ツールの接続よりAutonomous Databaseに接続する
上記の記事で作成したマスター暗号キーADBPROTKEYを、今回の作業でも使用します。
