第3章 DBサーバーの設計（MySQL）

3-1. 保守ネットワーク経路
3-2. MySQLのインストール
3-3. 新規DBユーザーの作成
3-4. SQLコマンドを練習
3-5. MySQLプロセスの設定変更
3-6. （参考）DBサーバーと暗号化通信する場合
3-7. 自分専用のデータベースを設計する練習
3-8. プログラミング（データ書き込み）
3-9. プログラミング（データ読み込み）
3-10. MySQLデーターベース操作コマンド
3-11. ワークショップ（DB設計）
3-12. おまけ（スキーマ schema）

DBサーバーを初期設定して、実際にデータを読み書きしてみましょう。

3-1. 保守ネットワーク経路

WEBアプリを使うときは、下図緑色のHTTPS通信の経路でアクセスします。
WEBやDBを開発するときは、下図赤色のSSH接続の経路でアクセスします。

SSH接続

PCからLinuxホストにSSH接続するには下記３つを確認します。

  • Linuxユーザー名：（DBサーバーのLinuxユーザー）
  • 秘密鍵を保存する場所：
    • （例）c:\Users\ユーザー名.ssh\key.txt
  • バッチファイル runssh.bat
    • （例）ssh -i .ssh/key.txt ユーザ@db.funnygeekjp.com

3-2. MySQLのインストール

Linuxホスト（OSはUbuntu24.04LTS）にMySQLデータベースをインストールします。

$ sudo apt update　　　　　　　 # リポジトリ情報を更新する
$ sudo apt install mysql-server　　# [MySQL]インストール
$ sudo systemctl status mysql　　# DBが起動しているか検査
$ sudo mysql -u root　　　　　　# DBにログインできるか検査
# DBにログインできたら、[exit]コマンドでDB操作を終了
mysql> exit

3-3. 新規DBユーザーの作成

DBにログインする

$ sudo mysql -u root

DBユーザー作成（他のホストからリモート接続するユーザーの場合）　

mysql> CREATE USER 'ユーザ名'@'%' IDENTIFIED BY 'パスワード';

DBユーザー作成（DBサーバーにローカル接続するユーザーの場合）

mysql> CREATE USER 'ユーザ名'@'localhost' IDENTIFIED BY 'パスワード';

以降、リモート接続するユーザーに対する設定をする
DBユーザーにフル権限を付与する（*.*は、全てのデータベース・全てのテーブルに対して）

mysql> GRANT ALL PRIVILEGES ON *.* TO 'ユーザ名'@'%' WITH GRANT OPTION;

DBユーザーの認証方式をパスワード方式に変更。その後、FLUSHコマンドですぐに適用する。

mysql> ALTER USER 'ユーザ名'@'%' IDENTIFIED WITH mysql_native_password BY 'パスワード';
mysql> FLUSH PRIVILEGES;

rootのリモート接続を無効化、その後、リモートユーザーを削除する。

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'root'@'%';
mysql> DROP USER 'root'@'%';

3-4. SQLコマンドを練習

基本的なSQLコマンドを体験しましょう。
DBにログイン（DBホストにローカル接続する場合）　

$ sudo mysql -u DBユーザ
$ sudo mysql -u root

データベース一覧を表示する

mysql> SHOW DATABASES;

データベースを作成する

# デフォルト文字コードで構わない場合
mysql> CREATE DATABASE データベース名;

# 参考（文字コードと照合順序を指定する場合）
mysql> CREATE DATABASE データベース名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

どのデータベースを操作するのか選択する

mysql> USE データベース名;

選んだデータベースの中にテーブルを作成する

mysql> CREATE TABLE テーブル名 ( 項目 型, 項目 型 );
（例）CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), number INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

レコード追加

mysql> INSERT INTO users (name, number) VALUES ('Kano', 1);

レコード表示

mysql> SELECT * FROM users;

レコード更新

mysql> UPDATE users SET numer = 2 WHERE name = 'Kano';

レコード削除　

mysql> DELETE FROM users WHERE name = 'Kano';

3-5. MySQLプロセスの設定変更

MySQLの設定ファイル（コンフィグ）を編集することで、ポート番号やDB接続最大数などMySQLサーバーの動作を調整できます。
ここでは、WEB/APPサーバーが、DBサーバーにリモート接続するための初期設定をします。

設定ファイルを nano エディタで編集する

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

設定ファイルに、外部サーバーがリモート接続でDBへの接続を受け入れるIPアドレスを追加する。0.0.0.0は全て受入。

bind-address=0.0.0.0

MySQL を再起動して設定を反映させる

$ sudo systemctl restart mysql

（トラブルシューティング）リモート接続が許可されていない場合

$ mysql -u kano -p -h 133.18.110.86
ERROR 2003 (HY000): Can't connect to MySQL server on '133.18.110.86:3306' (111)　リモート接続を許可していない

failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details.
bind-address を複数行書いたことがNG、まとめて0.0.0.0にすること
bind-address = 127.0.0.1
bind-address = 133.18.163.7

3-6. （参考）DBサーバーと暗号化通信する場合

外部サーバーとDBサーバー間の通信を暗号化する場合は、SSL証明書を配置し、SSL通信を有効化する。

SSL証明書(自己署名)の作成

用意する証明書は３つ

  • ca-key.pem　CA認証局の署名用秘密鍵（DBサーバーに保存する）
  • ca.pem　CA認証局の証明書（信頼の証。DBサーバー及び、DBサーバーに接続するリモートサーバーに保存する）
  • server-key.pem　サーバーの秘密鍵（DBサーバーに保存する）
  • server-cert.pem　サーバーの証明書（CA認証局が署名。サーバーが正しいことを証明。DBサーバーに保存する）

CA認証局の秘密鍵を作成［ca-key.pem］
自分で証明書を発行できるようにするために、認証局（CA）の秘密鍵を作成しています。これは証明書を発行するための「署名用の鍵」です。

$ openssl genrsa 2048 > ca-key.pem

CA認証局の証明書を作成（有効期限365日）［ca.pem］
サーバー証明書を信頼できるようにするための「親となる証明書」を作る。CA局の秘密鍵を使って、CAの自己署名証明書を作成しています。

$ openssl req -new -x509 -nodes -days 365 -key ca-key.pem -out ca.pem -subj "/CN=MySQL-CA"

サーバーの秘密鍵を作成［server-key.pem］
サーバーが暗号化通信をするため、DBサーバー用の秘密鍵を作成します。

$ openssl genrsa 2048 > server-key.pem

サーバーの証明書を作成［server-cert.pem］

サーバー証明書署名要求（CSR）を作成

$ openssl req -new -key server-key.pem -out server-req.pem -subj "/CN=MySQL-Server"

CA証明書で署名してサーバー証明書を作成

$ openssl x509 -req -in server-req.pem -days 365 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

ファイルの権限設定

$ sudo chown mysql:mysql /etc/mysql/ssl/*.pem
$ sudo chmod 600 /etc/mysql/ssl/server-key.pem

MySQLコンフィグに追加する

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON

リモートから接続する

$ mysql -u ユーザ名 -p -h db.hogehoge.com --ssl-ca=/path/to/ca.pem

SSL接続されているか検査　

$ SHOW STATUS LIKE 'Ssl_cipher';
# 応答が空でなければSSL接続の成功

3-7. 自分専用のデータベースを設計する練習

どんなアプリを作りますか？
そのアプリは、どんなデータ項目が必要なのか設計しましょう。

  • データベース名：
  • テーブル名：
  • カラム名：
id名称（型）名称（型）名称（型）
データレコード

型の例：

  • ID：　 id INT AUTO_INCREMENT PRIMARY KEY
  • テキスト：　 name VARCHAR(100),
  • 数値：　 number INT,
  • 日付：　created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

3-8. プログラミング（データ書き込み）

プログラミング言語「JavaScript」で、データを書き込みするコードを書きます。

前提： Node.js, npm, がインストールされていること

# nvmをダウンロードしてインストールする：
$ curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.40.3/install.sh | bash

# シェルを再起動する代わりに実行する
$ \. "$HOME/.nvm/nvm.sh"

# Node.jsをダウンロードしてインストールする：
$ nvm install 24

# Node.jsのバージョンを確認する：
$ node -v # "v24.8.0"が表示される。

# npmのバージョンを確認する：
$ npm -v # "11.6.0"が表示される。

前提：下記パラメータは、自分のデータベースにあわせて変更します。

  • DBサーバーのIPアドレス：＿
  • DBユーザー名：＿
  • DBユーザーのパスワード：＿
  • データベース名：＿
  • SQLクエリ文：＿
    （例）sql = ‘INSERT INTO frends (name, number) VALUES (“kano”, 12)’;

インストール（Node.js用のMySQLモジュール）

$ npm install mysql

もし、mysqlでErrorが発生する場合は、mysql2を使うと解消する場合があります。

$ npm install mysql2

# プログラムは下記に変える
// 外部モジュールの定義
const mysql = require('mysql2');

コーディング

アプリ用ディレクトリを作ります

$ mkdir ~/app
$ cd app

新しいファイルをnanoエディタで作成します

$ nano db-write.js

新しいファイル［db-write.js］に下記コードを記述します

// 外部モジュールの定義
const mysql = require('mysql');

// MySQL接続（SSLなし）
function connectDatabase() {
  const db = mysql.createConnection({
    host: 'DBサーバーのIPアドレス',
    user: 'DBユーザー名',
    password: 'DBユーザーのパスワード',
    database: 'データベース名'
  });

  db.connect((err) => {
    if (err) {
      console.error('データベース接続失敗: ' + err.message);
      throw err;
    }
  });

  return db;
}

// 接続終了処理
function closeDatabase(db) {
  db.end((err) => {
    if (err) {
      console.error('データベース切断失敗: ' + err.message);
    }
  });
}

// DB書き込み実行
function main() {
  const db = connectDatabase();

  const sql = 'SQLクエリ文';
  db.query(sql, (err, result) => {
    if (err) {
      console.error('SQL失敗: (' + sql + ') ' + err.message);
    } else {
      console.log('書き込み成功: insertId = ' + result.insertId);
    }
    closeDatabase(db);
  });
}

main();

プログラムを実行

node.jsで実行します。はじめて実行するときは［mysql］モジュールがないエラーになります。

$ node db-write.js

正常にDBに書き込みができたら「書き込み成功: レコード番号」が表示されます。

3-9. プログラミング（データ読み込み）

プログラミング言語「JavaScript」で、データを読み込み、画面に表示するコードを書きます。

前提：下記パラメータは、自分のデータベースにあわせて変更します。

  • SQLクエリ文：＿
    （例）sql = ‘SELECT * FROM frends‘;

コーディング

アプリ用ディレクトリに移動します

$ cd app

新しいファイルをnanoエディタで作成します

$ nano db-read.js

新しいファイル［db-read.js］に下記コードを記述します

const mysql = require('mysql');

// MySQL接続（SSLなし）
function connectDatabase() {
  const db = mysql.createConnection({
    host: 'DBサーバーのIPアドレス',
    user: 'DBユーザー名',
    password: 'DBユーザーのパスワード',
    database: 'データベース名'
  });

  db.connect((err) => {
    if (err) {
      console.error('データベース接続失敗: ' + err.message);
      throw err;
    }
  });

  return db;
}

// 接続終了処理
function closeDatabase(db) {
  db.end((err) => {
    if (err) {
      console.error('データベース切断失敗: ' + err.message);
    }
  });
}

// DB読み込み実行
function main() {
  const db = connectDatabase();

  const sql = 'SQLクエリ文';
  db.query(sql, (err, results) => {
    if (err) {
      console.error('SELECT失敗: ' + err.message);
    } else {
      console.log('読み込み成功:');
      results.forEach((row) => {
        console.log(`ID: ${row.id}, Name: ${row.name}, Number: ${row.number}`);
      });
    }
    closeDatabase(db);
  });
}

main();

プログラムを実行

node.jsで実行します。はじめて実行するときは［mysql］モジュールがないエラーになります。

$ node db-read.js

正常にDBを読み取り、レコードが表示されます。

3-10. MySQLデーターベース操作コマンド

データベースにパスワード付きで接続する

$ sudo mysql -u root -p

外部のDBサーバーにアクセスする場合：
$ mysql -u ユーザー名 -p -h サーバー名 -P ポート番号

データベース一覧を表示する

mysql> SHOW DATABASES;

使うデータベースを選ぶ

mysql> USE データベース名;

テーブル一覧を表示する

mysql> SHOW TABLES;

テーブル内のデータを全て表示してみる

mysql> SELECT * from テーブル名;

データベース操作を終了する

mysql> EXIT;

新しいテーブルを作る

mysql> CREATE TABLE テーブル名 ( 項目名 データ種類 );

（例）mysql> CREATE TABLE kanogame ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL,   life INT NOT NULL, message VARCHAR(255) NOT NULL, PRIMARY KEY (id) );

id:自動入力
VARCHAR():文字(文字数)
INT:数字
NOT NULL:空白はだめ
PRIMARY KEY:唯一のキーID

テーブルを削除する

mysql> DROP TABLE テーブル名;

テーブルのカラム構造を表示する

mysql> describe テーブル名;

カラムを追加する

mysql> ALTER TABLE テーブル名 ADD COLUMN カラム名 型;

（Create）データ[レコード]を追加する

書式> INSERT INTO mydata (項目1, 項目2) VALUES (データ1, データ2);
例＿> INSERT INTO mydata (id, name, data) VALUES (23, '好きな食べ物', 'チャーハン');
IDが自動採番キーの場合
例＿> INSERT INTO mydata (name, data) VALUES ('好きな食べ物', 'チャーハン');

（Read）データ[レコード]を読む

書式> SELECT 項目1, 項目2 FROM テーブル名 WHERE 検索条件;
例　> SELECT * FROM mydata;
例　> SELECT * FROM mydata WHERE id=20;
例　> SELECT name FROM mydata WHERE id=20;

（Update）データ[レコード]を書き換える

書式> UPDATE テーブル名 SET 項目=値 WHERE 条件式;
例　> UPDATE mydata SET data='ポテトスープ' WHERE id=24;
例　> UPDATE mydata SET name='好きな食べ物', data='ポテトスープ' WHERE id=24;
条件式が無い場合、全てのレコードが対象になるので気をつけて

（Delete）データ[レコード]を削除する

※データが消えるので要注意
書式> DELETE FROM テーブル名 WHERE 条件式
例（全てのレコード削除）> DELETE FROM myTable;
例　> DELETE FROM my data WHERE id=22;

ユーザーを編集するコマンド

ユーザー一覧を表示する

SELECT host, user, password FROM mysql.user;

ユーザーを追加する

書式> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

追加したユーザーに全てのDB操作権限を与える。[USAGE:権限なし], [SELECT, CREATE, UPDATE, INSERT, DELETE]

書式> grant all privileges on *.* to testuser@localhost IDENTIFIED BY 'password';

記載しないコマンド

  • データベースのダンプをとる（DBのデータをファイルに出力する／バックアップする）コマンド
  • ダンプからデータを取り込むリストアコマンド

レコード追加したとき自動でIDを付与する

この例では [userid] はデータ項目であり、自動で付与するIDではない。

データベースにログインして、データベースを選択後、新しいデータ項目を追加する

■データベースにログインする（パスワード: raspberry）
$ sudo mysql –u root –p

■データベース一覧
　> show databases;

■データベースを選ぶ
　> use データベース名;

■テーブル一覧
　> show tables;

■テーブルのデータを読み込む
　> select * from テーブル名;

追加したレコードに、重複しないIDを自動で付与する

 > ALTER TABLE テーブル名 ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

3-11. ワークショップ（DB設計）

アプリのデータ項目を設計してください。どんなデータを保存すればよいでしょうか。
例えばメモ帳ならば、メモと備考、あとは検索用IDと登録日付です。

mysql> describe memo_kano;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| memo    | varchar(255) | NO   |     | NULL    |                |
| remark  | varchar(255) | NO   |     | NULL    |                |
| flag    | int          | NO   |     | NULL    |                |
| duedate | date         | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

3-12. おまけ（スキーマ schema）

データベースの構造や設計図をスキーマと呼びます。スキーマには、テーブル名、カラム名・型、インデックスやビューを含みます。

コメントを残す