MySQLことはじめ

# MySQL

[MySQL](https://www.mysql.com/jp/): ポピュラーで、軽量かつシンプルとされるSQLの一つ。

MySQLを使って、データベースを更新するために何が必要かをいくつか確認してみる。

# MySQLをUbuntuにインストールする
Kubuntu22.04にインストールする

```:/etc/os-release
RETTY_NAME="Ubuntu 22.04.5 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.5 LTS (Jammy Jellyfish)"
VERSION_CODENAME=jammy
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=jammy
```

```bash:
sudo apt update
sudo apt install mysql-server
```
でインストール。
`mysql --version`でバージョンを確認すると
```console:
mysql  Ver 8.0.41-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
```
と出た。
```bash
sudo systemctl start mysql #MySQLサービスの起動
sudo systemctl enable mysql #自動起動を有効化
sudo systemctl status mysql #MySQLの状態確認
```
を実行すると
```console
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:>
     Active: active (running) since Thu 2025-03-20 23:23:19 JST; 1min 49s ago
   Main PID: 456361 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 38223)
     Memory: 364.4M
        CPU: 1.830s
     CGroup: /system.slice/mysql.service
             └─456361 /usr/sbin/mysqld

 3月 20 23:23:18 shinohara-Kubuntu systemd[1]: Starting MySQL Community Serve>
 3月 20 23:23:19 shinohara-Kubuntu systemd[1]: Started MySQL Community Server.
```
と出たので、起動している。

# 一般ユーザーの追加
いつもrootで操作するのは嫌なので、一般ユーザーを作る。
```bash:
sudo mysql -u root
```
と入力すると以下のテキストが出る
```console:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.41-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2025, 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> 
```
以下で、ユーザーの追加、権限付与、データベース作成、追加ユーザーへのデータベース編集権限の付与
```sql
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'yourpassword'; -- newuser を localhost に yourpassword で追加
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION; --localhost の newuser に全ての権限を与える
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'localhost'; --newdb.*への全ての権限を localhost の newuser に与える
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES; --権限付与の実行
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DATABASE newdb; -- 新しいデータベース newdb を作成
Query OK, 1 row affected (0.02 sec)

mysql> EXIT; --MySQLを終了
```
ここまでくれば、`newuser`で`newdb`の編集が可能なはず。

# 一般ユーザーでデータベースにデータを追加してみる
コンソールで`mysql -u newuser -p`を入力して、パスワードを入力してmysqlに入る。

```sql
mysql> USE newdb; --newdbを利用する
Database changed
mysql> CREATE TABLE users (
    ->     id INT AUTO_INCREMENT PRIMARY KEY, --PRIMARY KEYとしてidを設定
    ->     name VARCHAR(255) NOT NULL, --文字列、NULLは許さない
    ->     age INT, --整数
    ->     email VARCHAR(255) UNIQUE --異なるPRIMARY KEYで同一変数の被りを許さない(但し NULL は除く)
    -> ); --新たなテーブル users を作成
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO users (name, age, email) VALUES 
    ->     ('Alice', 30, 'alice@example.com'),
    ->     ('Bob', 25, 'bob@example.com'),
    ->     ('Charlie', 35, 'charlie@example.com'),
    ->     ('David', 28, 'david@example.com'); -- テーブル users に4件のデータを挿入
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM users; --上で追加したデータを確認
+----+---------+------+---------------------+
| id | name    | age  | email               |
+----+---------+------+---------------------+
|  1 | Alice   |   30 | alice@example.com   |
|  2 | Bob     |   25 | bob@example.com     |
|  3 | Charlie |   35 | charlie@example.com |
|  4 | David   |   28 | david@example.com   |
+----+---------+------+---------------------+
4 rows in set (0.01 sec)
```

# MySQL Workbenchをリモート(≠ローカル)から使う
MySQLを入れたホスト(`shinohara-kubuntu(仮名)`)に`sudo snap install mysql-workbench-community`でMySQL Workbenchを入れて起動したが、うまくGUIがネットワーク越しに描画されなかった。MySQLが動くホストとは別のホスト(`shinohara-mac(仮名)`)にMySQL Workbenchをインストール([MySQL :: MySQL Workbench](https://www.mysql.com/jp/products/workbench/)からダウンロード)してそこからDBにアクセスすることにした。

## MySQLサーバーをlocalhost以外からアクセスできるようにする
### 3306ポートへの問い合わせを可能にする(MySQLよりも上位のホストとしての設定)
MySQLサーバーで
```bash
sudo ss -tulnp | grep 3306
```
を実行すると
```console
tcp   LISTEN 0      70                            127.0.0.1:33060      0.0.0.0:*    users:(("mysqld",pid=456361,fd=21))        
tcp   LISTEN 0      151                           127.0.0.1:3306       0.0.0.0:*    users:(("mysqld",pid=456361,fd=23))        
```
となっていた。これは127.0.0.1のlocalhostだけからの問い合わせを受け付けていることを示している。
`/etc/mysql/mysql.conf.d/mysqld.cnf`を編集して
```conf:/etc/mysql/mysql.conf.d/mysqld.cnf(抜粋) 
#bind-address		= 127.0.0.1
#mysqlx-bind-address	= 127.0.0.1
bind-address            = 0.0.0.0
mysqlx-bind-address     = 0.0.0.0
```
に変えて、
```bash
sudo systemctl restart mysql
```
で設定を反映。再度`sudo ss -tulnp | grep 3306`で確認すると
```console
tcp   LISTEN 0      70                              0.0.0.0:33060      0.0.0.0:*    users:(("mysqld",pid=466470,fd=21))        
tcp   LISTEN 0      151                             0.0.0.0:3306       0.0.0.0:*    users:(("mysqld",pid=466470,fd=4))         
```
となって、0.0.0.0の任意のホストから3306に問い合わせを受ける設定にできた。このMySQLサーバーはご家庭イントラネットに置いたものなので、この辺のセキュリティの設定はざる。<font color="red">意図しないユーザーが侵入しそうなネットワークでは、問い合わせ可能なホストはIP等できちんと制限した方が良いはず。</font>

別のホストからMySQLサーバー宛に`nc -zv 192.168.xxx.yyy 3306`を実行して
```console:成功例
Connection to 192.168.xxx.yyy port 3306 [tcp/mysql] succeeded!
```
が帰ってきたら、ホストとしては3306ポートへの問い合わせはできる。設定がうまくいっていない場合は以下のような出力:
```console:失敗例
nc: connectx to 192.168.xxx.yyy port 3306 (tcp) failed: Connection refused
```


### MySQLで`newuser`
ホストとして、3306ポートへの問い合わせができるようになっても、MySQLとして、ユーザー毎の設定に不備があるとリモートからはDBにアクセスできない。

`mysql -u newuser -p`でパスワードを入力して以下のコマンドを実行する
```sql
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| newuser          | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
```
この状態だと、任意のユーザーは`localhost`からしかアクセスを受け付けていない。引き続いて
```sql
mysql> UPDATE mysql.user SET Host='%' WHERE User="newuser" AND Host='localhost'; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH Privileges;
Query OK, 0 rows affected (0.01 sec)
```
で、`Host='%'`で任意のホストからのアクセスを許可する設定にしている。この様子は
```sql
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| newuser          | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
```
で確認できる。

### MySQL Workbenchで`newdb`を見てみる。
ここまで設定すると、MySQL Workbenchで接続が確認できる。

MySQL Workbench(バージョンは8.0)を起動して、ホーム画面の「MySQL Connections」の右にある⊕のマークからConnection Name(任意), Hostname, Usernameを適当に埋めて接続。パスワードを聞かれるのでMySQLサーバーで設定したものを入力すると、つながる。

左側のウィンドウに`SCHEMAS`があるので、その中から`newdb`をGUI上でクリックして選択する。
真ん中のウィンドウにコンソールがあるので、そこに`SELECT * FROM users;`を入力して雷マーク⚡️をクリックすると、テーブルが表示される。


# コマンド
`mysql -u newuser -p`でログイン後のコマンド一覧:
```sql:
SHOW DATABASES; --USE することが出来るDB一覧
SHOW TABLES; --そのDBに登録されているTABLE一覧
```

# この記事をまとめようと思った動機
「データベースなるものは聞いたことがあるし、スプレッドシートを使って、それらしいものに触ったこともある。"SQL"が指すもののなんとなく知っていて、どうもAPIを使って書き換えたり出来るらしい」くらいの粒度の情報は持っていたけれど、
・どうやってテーブルの中身は確認できるのか？
・データはどういう構造で格納されているのか？
を解決するための手続きがわからない状態だった。
chatGPTに色々質問していたら、APIを使わずとも、データベースサーバ上でコマンドライン(CLI)でデータを書き込むなりなんなりの操作ができると知って(逆に言えばそれすら知らなかった)、CLIでデータを操作してみれば、実態がつかめるだろう と判断して、CLIでデータをあれこれ操作してみることにした。