OCIコンピュート上にMySQLを作成してみた

この記事では、Oracle Cloud Infrastructure(OCI)のコンピュート上にMySQLを作成する方法を記載します。

## コンピュートの作成

#### OCIでコンピュートを作成する
以下の記事を参考にOCIコンピュートを作成します:
※MySQLがサポートされるプラットフォームで作成します。

https://oracle-japan.github.io/ocitutorials/beginners/creating-compute-instance/

サポートされているプラットフォーム:

[https://www.mysql.com/jp/support/supportedplatforms/database.html](https://www.mysql.com/jp/support/supportedplatforms/database.html)

## MySQLのインストール

Yumリポジトリダウンロードサイト:

[https://dev.mysql.com/downloads/repo/yum/](https://dev.mysql.com/downloads/repo/yum/)

Yumリポジトリの追加:

```sql
sudo yum -y localinstall https://dev.mysql.com/get/mysql84-community-release-el8-1.noarch.rpm
```

```sql
[opc@mysqlsource ~]$ sudo yum -y localinstall https://dev.mysql.com/get/mysql84-community-release-el8-1.noarch.rpm

Ksplice for Oracle Linux 8 (x86_64)              41 MB/s |  10 MB     00:00    
MySQL 8.0 for Oracle Linux 8 (x86_64)            57 MB/s | 4.2 MB     00:00    
MySQL 8.0 Tools Community for Oracle Linux 8 (x  13 MB/s | 906 kB     00:00    
MySQL 8.0 Connectors Community for Oracle Linux 727 kB/s |  34 kB     00:00    
Oracle Software for OCI users on Oracle Linux 8  61 MB/s | 172 MB     00:02    
Oracle Linux 8 BaseOS Latest (x86_64)           135 MB/s |  82 MB     00:00    
Oracle Linux 8 Application Stream (x86_64)       90 MB/s |  65 MB     00:00    
Oracle Linux 8 Addons (x86_64)                   81 MB/s |  23 MB     00:00    
Latest Unbreakable Enterprise Kernel Release 7  111 MB/s |  49 MB     00:00    
mysql84-community-release-el8-1.noarch.rpm       79 kB/s |  15 kB     00:00    
Dependencies resolved.
================================================================================
 Package                        Arch        Version     Repository         Size
================================================================================
Installing:
 mysql84-community-release      noarch      el8-1       @commandline       15 k

Transaction Summary
================================================================================
Install  1 Package

Total size: 15 k
Installed size: 17 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1 
  Installing       : mysql84-community-release-el8-1.noarch                 1/1 
  Running scriptlet: mysql84-community-release-el8-1.noarch                 1/1 
   Warning: native mysql package from platform vendor seems to be enabled.
    Please consider to disable this before installing packages from repo.mysql.com.
    Run: yum module -y disable mysql

  Verifying        : mysql84-community-release-el8-1.noarch                 1/1 

Installed:
  mysql84-community-release-el8-1.noarch                                        

Complete!
```

MySQL Yumリポジトリが正常に追加されたか確認:

```sql
yum repolist enabled | grep "mysql.*-community.*"
```

```sql
[opc@mysqlsource ~]$ yum repolist enabled | grep "mysql.*-community.*"
Failed to set locale, defaulting to C.UTF-8
mysql-8.4-lts-community          MySQL 8.4 LTS Community Server
mysql-connectors-community       MySQL Connectors Community
mysql-tools-8.4-lts-community    MySQL Tools 8.4 LTS Community
```

MySQL Yumリポジトリ内の有効化されているサブリポジトリを確認:

```sql
yum repolist all | grep mysql
```

```sql
[opc@mysqlsource ~]$ yum repolist all | grep mysql
mysql-8.4-lts-community                      MySQL 8.4 LTS Community Se enabled
mysql-8.4-lts-community-debuginfo            MySQL 8.4 LTS Community Se disabled
mysql-8.4-lts-community-source               MySQL 8.4 LTS Community Se disabled
mysql-cluster-8.0-community                  MySQL Cluster 8.0 Communit disabled
mysql-cluster-8.0-community-debuginfo        MySQL Cluster 8.0 Communit disabled
mysql-cluster-8.0-community-source           MySQL Cluster 8.0 Communit disabled
mysql-cluster-8.4-lts-community              MySQL Cluster 8.4 LTS Comm disabled
mysql-cluster-8.4-lts-community-debuginfo    MySQL Cluster 8.4 LTS Comm disabled
mysql-cluster-8.4-lts-community-source       MySQL Cluster 8.4 LTS Comm disabled
mysql-cluster-innovation-community           MySQL Cluster Innovation R disabled
mysql-cluster-innovation-community-debuginfo MySQL Cluster Innovation R disabled
mysql-cluster-innovation-community-source    MySQL Cluster Innovation R disabled
mysql-connectors-community                   MySQL Connectors Community enabled
mysql-connectors-community-debuginfo         MySQL Connectors Community disabled
mysql-connectors-community-source            MySQL Connectors Community disabled
mysql-innovation-community                   MySQL Innovation Release C disabled
mysql-innovation-community-debuginfo         MySQL Innovation Release C disabled
mysql-innovation-community-source            MySQL Innovation Release C disabled
mysql-tools-8.4-lts-community                MySQL Tools 8.4 LTS Commun enabled
mysql-tools-8.4-lts-community-debuginfo      MySQL Tools 8.4 LTS Commun disabled
mysql-tools-8.4-lts-community-source         MySQL Tools 8.4 LTS Commun disabled
mysql-tools-community                        MySQL Tools Community      disabled
mysql-tools-community-debuginfo              MySQL Tools Community - De disabled
mysql-tools-community-source                 MySQL Tools Community - So disabled
mysql-tools-innovation-community             MySQL Tools Innovation Com disabled
mysql-tools-innovation-community-debuginfo   MySQL Tools Innovation Com disabled
mysql-tools-innovation-community-source      MySQL Tools Innovation Com disabled
mysql80-community                            MySQL 8.0 Community Server disabled
mysql80-community-debuginfo                  MySQL 8.0 Community Server disabled
mysql80-community-source                     MySQL 8.0 Community Server disabled
```

デフォルトのMySQLモジュールの無効化:

```sql
sudo yum module disable mysql
```

```sql
[opc@mysqlsource ~]$ sudo yum module disable mysql
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:07:01 ago on Mon Nov 25 08:20:12 2024.
Dependencies resolved.
=============================================================================================================
 Package                  Architecture            Version                     Repository                Size
=============================================================================================================
Disabling modules:
 mysql                                                                                                      

Transaction Summary
=============================================================================================================

Is this ok [y/N]: y
Complete!
```

- Oracle Linux 8 などの EL8-based システムには、デフォルトで有効になっている MySQLモジュールが含まれています。このモジュールを無効にしないかぎり、MySQLリポジトリによって提供されるパッケージがマスクされます。

MySQLのインストール:

```sql
sudo yum install mysql-community-server
```

```sql
[opc@mysqlsource ~]$ sudo yum install mysql-community-server
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:07:33 ago on Mon Nov 25 08:20:12 2024.
Dependencies resolved.
=============================================================================================================
 Package                               Architecture  Version            Repository                      Size
=============================================================================================================
Installing:
 mysql-community-server                x86_64        8.4.3-1.el8        mysql-8.4-lts-community         61 M
Installing dependencies:
 mysql-community-client                x86_64        8.4.3-1.el8        mysql-8.4-lts-community         15 M
 mysql-community-client-plugins        x86_64        8.4.3-1.el8        mysql-8.4-lts-community        4.6 M
 mysql-community-common                x86_64        8.4.3-1.el8        mysql-8.4-lts-community        691 k
 mysql-community-icu-data-files        x86_64        8.4.3-1.el8        mysql-8.4-lts-community        2.2 M
 mysql-community-libs                  x86_64        8.4.3-1.el8        mysql-8.4-lts-community        1.5 M

Transaction Summary
=============================================================================================================
Install  6 Packages

Total download size: 85 M
Installed size: 393 M
Is this ok [y/N]: y
Downloading Packages:
(1/6): mysql-community-common-8.4.3-1.el8.x86_64.rpm                         5.2 MB/s | 691 kB     00:00    
(2/6): mysql-community-icu-data-files-8.4.3-1.el8.x86_64.rpm                  54 MB/s | 2.2 MB     00:00    
(3/6): mysql-community-client-plugins-8.4.3-1.el8.x86_64.rpm                  22 MB/s | 4.6 MB     00:00    
(4/6): mysql-community-libs-8.4.3-1.el8.x86_64.rpm                            33 MB/s | 1.5 MB     00:00    
(5/6): mysql-community-client-8.4.3-1.el8.x86_64.rpm                          44 MB/s |  15 MB     00:00    
(6/6): mysql-community-server-8.4.3-1.el8.x86_64.rpm                         117 MB/s |  61 MB     00:00    
-------------------------------------------------------------------------------------------------------------
Total                                                                        115 MB/s |  85 MB     00:00     
MySQL 8.4 LTS Community Server                                               3.0 MB/s | 3.1 kB     00:00    
Importing GPG key 0xA8D3785C:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: BCA4 3417 C3B4 85DD 128E C6D4 B7B3 B788 A8D3 785C
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                     1/1 
  Installing       : mysql-community-common-8.4.3-1.el8.x86_64                                           1/6 
  Installing       : mysql-community-client-plugins-8.4.3-1.el8.x86_64                                   2/6 
  Installing       : mysql-community-libs-8.4.3-1.el8.x86_64                                             3/6 
  Running scriptlet: mysql-community-libs-8.4.3-1.el8.x86_64                                             3/6 
  Installing       : mysql-community-client-8.4.3-1.el8.x86_64                                           4/6 
  Installing       : mysql-community-icu-data-files-8.4.3-1.el8.x86_64                                   5/6 
  Running scriptlet: mysql-community-server-8.4.3-1.el8.x86_64                                           6/6 
  Installing       : mysql-community-server-8.4.3-1.el8.x86_64                                           6/6 
  Running scriptlet: mysql-community-server-8.4.3-1.el8.x86_64                                           6/6 
  Verifying        : mysql-community-client-8.4.3-1.el8.x86_64                                           1/6 
  Verifying        : mysql-community-client-plugins-8.4.3-1.el8.x86_64                                   2/6 
  Verifying        : mysql-community-common-8.4.3-1.el8.x86_64                                           3/6 
  Verifying        : mysql-community-icu-data-files-8.4.3-1.el8.x86_64                                   4/6 
  Verifying        : mysql-community-libs-8.4.3-1.el8.x86_64                                             5/6 
  Verifying        : mysql-community-server-8.4.3-1.el8.x86_64                                           6/6 

Installed:
  mysql-community-client-8.4.3-1.el8.x86_64         mysql-community-client-plugins-8.4.3-1.el8.x86_64        
  mysql-community-common-8.4.3-1.el8.x86_64         mysql-community-icu-data-files-8.4.3-1.el8.x86_64        
  mysql-community-libs-8.4.3-1.el8.x86_64           mysql-community-server-8.4.3-1.el8.x86_64                

Complete!
```

MySQL Serverのステータス確認:

```sql
systemctl status mysqld
```

```sql
[opc@mysqlsource ~]$ systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
```

MySQL Serverの起動:

```sql
sudo systemctl start mysqld
```

MySQL Serverのステータス確認:

```sql
[opc@mysqlsource ~]$ systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2024-11-25 08:30:00 GMT; 10s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 46913 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 46986 (mysqld)
   Status: "Server is operational"
    Tasks: 35 (limit: 409064)
   Memory: 467.8M
   CGroup: /system.slice/mysqld.service
           └─46986 /usr/sbin/mysqld

Nov 25 08:29:55 mysqlsource systemd[1]: Starting MySQL Server...
Nov 25 08:30:00 mysqlsource systemd[1]: Started MySQL Server.
```

MySQLを自動起動するように設定:

```sql
sudo systemctl enable mysqld
```

## データベースの作成

MySQLのrootユーザのパスワードを確認(初回ログイン):

```sql
sudo cat /var/log/mysqld.log | grep root
```

```sql
[opc@mysqlsource ~]$ sudo cat /var/log/mysqld.log | grep root
2024-11-25T08:29:58.182481Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ce5#gEgNgCMX
```

rootユーザでログイン:

```sql
mysql -u root -p
```


```sql
[opc@mysqlsource ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.3

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.
```

rootユーザのパスワードを変更する:

```sql
SET PASSWORD = 'WelCome123#123#';
```

```sql
mysql> SET PASSWORD = 'WelCome123#123#';
Query OK, 0 rows affected (0.01 sec)
```

新規ユーザを作成:

```sql
create user 'testuser'@'%' identified by 'WelCome123#123#';
```

* 外部アクセスを許可するIPを絞る場合は「%」部分に任意のアドレスを指定する

新規ユーザに権限を付与する:

```sql
grant all privileges on *.* to 'testuser'@'%';
```

新規ユーザでログインする(ローカル接続):

```sql
mysql -u testuser -p
```

```sql
[opc@mysqlsource ~]$ mysql -u testuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.4.3 MySQL Community Server - GPL

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.
```

データベースを作成する:

```sql
create database testdb;
```

作成したデータベースを確認:

```sql
show databases;
```

```sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
```

使用するデータベースを選択する:

```sql
use testdb;
```

```sql
mysql> use testdb;
Database changed
```

使用中のデータベースの確認:

```sql
select database();
```


```sql
mysql> select database();
+------------+
| database() |
+------------+
| testdb     |
+------------+
1 row in set (0.00 sec)
```

## テーブルの作成

テーブルの確認:

```sql
show tables;
```


```sql
mysql> show tables;
Empty set (0.00 sec)
```

テーブルを作成する:

```sql
create table employees (ID varchar(3), Name varchar(10), primary key(ID));
```


```sql
mysql> create table employees (ID varchar(3), Name varchar(10), primary key(ID));
Query OK, 0 rows affected (0.02 sec)
```

確認する:

```sql
mysql> show tables;
+--------------------+
| Tables_in_sourcedb |
+--------------------+
| employees          |
+--------------------+
1 row in set (0.00 sec)

mysql> describe employees;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | varchar(3)  | NO   | PRI | NULL    |       |
| Name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from employees;
Empty set (0.01 sec)
```

行を追加する:

```sql
insert into employees (ID, Name) values ('1', 'James');
```


```sql
mysql> insert into employees (ID, Name) values ('1', 'James');
Query OK, 1 row affected (0.00 sec)
```

確認する:

```sql
mysql> select * from employees;
+----+-------+
| ID | Name  |
+----+-------+
| 1  | James |
+----+-------+
1 row in set (0.00 sec)
```

## (任意)テーブルおよびデータベースの削除
データベースとテーブルの削除:
```sql
drop table testuser;
```

```sql
drop database testdb;
```


以上