MySQL
MySQL: ポピュラーで、軽量かつシンプルとされるSQLの一つ。
MySQLを使って、データベースを更新するために何が必要かをいくつか確認してみる。
MySQLをUbuntuにインストールする
Kubuntu22.04にインストールする
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
sudo apt update
sudo apt install mysql-server
でインストール。
mysql --versionでバージョンを確認すると
mysql Ver 8.0.41-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
と出た。
sudo systemctl start mysql #MySQLサービスの起動
sudo systemctl enable mysql #自動起動を有効化
sudo systemctl status mysql #MySQLの状態確認
を実行すると
● 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で操作するのは嫌なので、一般ユーザーを作る。
sudo mysql -u root
と入力すると以下のテキストが出る
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>
以下で、ユーザーの追加、権限付与、データベース作成、追加ユーザーへのデータベース編集権限の付与
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に入る。
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からダウンロード)してそこからDBにアクセスすることにした。
MySQLサーバーをlocalhost以外からアクセスできるようにする
3306ポートへの問い合わせを可能にする(MySQLよりも上位のホストとしての設定)
MySQLサーバーで
sudo ss -tulnp | grep 3306
を実行すると
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を編集して
#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
に変えて、
sudo systemctl restart mysql
で設定を反映。再度sudo ss -tulnp | grep 3306で確認すると
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サーバーはご家庭イントラネットに置いたものなので、この辺のセキュリティの設定はざる。意図しないユーザーが侵入しそうなネットワークでは、問い合わせ可能なホストはIP等できちんと制限した方が良いはず。
別のホストからMySQLサーバー宛にnc -zv 192.168.xxx.yyy 3306を実行して
Connection to 192.168.xxx.yyy port 3306 [tcp/mysql] succeeded!
が帰ってきたら、ホストとしては3306ポートへの問い合わせはできる。設定がうまくいっていない場合は以下のような出力:
nc: connectx to 192.168.xxx.yyy port 3306 (tcp) failed: Connection refused
MySQLでnewuser
ホストとして、3306ポートへの問い合わせができるようになっても、MySQLとして、ユーザー毎の設定に不備があるとリモートからはDBにアクセスできない。
mysql -u newuser -pでパスワードを入力して以下のコマンドを実行する
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からしかアクセスを受け付けていない。引き続いて
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='%'で任意のホストからのアクセスを許可する設定にしている。この様子は
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でログイン後のコマンド一覧:
SHOW DATABASES; --USE することが出来るDB一覧
SHOW TABLES; --そのDBに登録されているTABLE一覧
この記事をまとめようと思った動機
「データベースなるものは聞いたことがあるし、スプレッドシートを使って、それらしいものに触ったこともある。"SQL"が指すもののなんとなく知っていて、どうもAPIを使って書き換えたり出来るらしい」くらいの粒度の情報は持っていたけれど、
・どうやってテーブルの中身は確認できるのか？
・データはどういう構造で格納されているのか？
を解決するための手続きがわからない状態だった。
chatGPTに色々質問していたら、APIを使わずとも、データベースサーバ上でコマンドライン(CLI)でデータを書き込むなりなんなりの操作ができると知って(逆に言えばそれすら知らなかった)、CLIでデータを操作してみれば、実態がつかめるだろう と判断して、CLIでデータをあれこれ操作してみることにした。