MariaDBを作ってPHPから記事を表示する

🎯 今回のゴール

musikyun専用のMariaDBを作り、 PHPから記事データを取得してブラウザへ表示します。

ただ接続するだけではなく、 「PHPとMariaDBの間で何が起きているのか」 「なぜDB専用ユーザーを作るのか」 「なぜPermission deniedになったのか」 まで確認します。

今回作る仕組み

ブラウザ
↓ HTTPS
nginx
↓
PHP-FPM
↓
PHP
↓
PDO
↓
MariaDB
↓
articles
↓
記事データ
↓
HTML
↓
ブラウザ
📘 旧musikyunでは?
旧musikyunでは、記事データの保存をSupabaseが担当していました。

今回はそのうちのDatabase部分を、 VPS上のMariaDBへ置き換えていきます。

STEP 1|既存のデータベースを確認する

sudo mysql

MariaDBへ管理者として入り、既存のデータベースを確認しました。

SHOW DATABASES;
📘 SQLを読む
SHOW = 情報を表示する。
DATABASES = MariaDBに存在するデータベース。

→ musikyunという名前のDBがすでに存在しないことを確認してから、 新しいDBを作ります。
📘 なぜ先に確認する?
いきなり作成するのではなく、 既存環境と名前が衝突しないことを確認してから変更します。

「確認 → 変更 → 再確認」は、 サーバーやDBを扱うときの基本的な流れです。

STEP 2|musikyun専用DBを作る

CREATE DATABASE musikyun
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
📘 SQLを読む
CREATE DATABASE = 新しいデータベースを作成します。
musikyun = 今回作るDBの名前です。
CHARACTER SET utf8mb4 = 日本語や絵文字などを扱える文字コードを指定します。
COLLATE = 文字列を比較・並び替えするときのルールを指定します。

作成したDBを選択します。

USE musikyun;
📘 USEとは?
「これからどのデータベースを操作するのか」を選択するSQLです。

実行後の表示が MariaDB [musikyun]> になれば、musikyun DBを操作している状態です。

STEP 3|articlesテーブルを作る

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    youtube_url VARCHAR(500) DEFAULT NULL,
    image_url VARCHAR(500) DEFAULT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    slug VARCHAR(255) DEFAULT NULL,
    author_id INT UNSIGNED DEFAULT NULL
);
📘 テーブルとは?
DBの中でデータを保存する「表」です。

articlesは記事を保存する表で、 1行が1記事、各カラムが記事の情報になります。
📘 主な型と指定を読む
INT = 整数。
UNSIGNED = 負の値を使わない整数。
AUTO_INCREMENT = 1、2、3…と自動採番。
PRIMARY KEY = その行を識別する主キー。
VARCHAR(255) = 最大255文字の可変長文字列。
TEXT = 長い文章を保存する型。
NOT NULL = NULLを許可しない。
DEFAULT = 値を省略した場合の初期値。
TIMESTAMP = 日時を扱う型です。
📘 statusの初期値
DEFAULT 'draft'としているため、 statusを指定せず記事を作ると、自動的に下書きになります。

旧musikyunの draft / in_review / needs_revision / published / closed という状態管理を、今後このカラムで再現します。

作成後、テーブルの構造を確認しました。

DESCRIBE articles;
📘 なぜDESCRIBEする?
CREATE TABLEが成功しただけでは、 自分が意図した構造になっているかまでは確認できません。

→ 作成したあとに、実際の結果を確認します。

STEP 4|テスト記事を保存する

INSERT INTO articles (
    title,
    content
) VALUES (
    'テスト記事',
    'musikyunのMariaDB接続テストです。'
);
📘 INSERTを読む
INSERT INTO articles = articlesへ新しい行を追加する。
title, content = 今回値を入れるカラム。
VALUES = 実際に保存する値です。

今回はtitleとcontentしか指定していません。

title / content を入力
↓
MariaDB
↓
idを自動採番
statusをdraftにする
created_atへ現在日時を入れる
↓
articlesへ保存

保存されたデータを確認します。

SELECT * FROM articles;
📘 SELECTを読む
SELECT = データを取得する。
* = すべてのカラム。
FROM articles = articlesテーブルから取得する。

実際に id = 1、 status = draft、 created_atに日時が入ったことを確認しました。

STEP 5|musikyun専用DBユーザーを作る

CREATE USER 'musikyun_user'@'localhost'
IDENTIFIED BY 'ここには実際のパスワードを書かない';
📘 なぜrootをPHPから使わない?
MariaDBのrootは管理者です。
musikyun以外のDBまで操作できる強い権限を持っています。

musikyunから必要なのは、 musikyun DBを操作する権限だけです。

→ アプリ専用ユーザーを作り、 必要な範囲だけ権限を与えます。
GRANT ALL PRIVILEGES ON musikyun.*
TO 'musikyun_user'@'localhost';
📘 GRANTを読む
GRANT = 権限を与える。
ON musikyun.* = musikyun DB内のテーブルを対象にする。
TO = 誰へ権限を与えるか。
'musikyun_user'@'localhost' = VPS自身から接続するmusikyun_userです。
📘 認証と認可は別
認証(Authentication) = 「あなたは誰ですか?」を確認すること。
認可(Authorization) = 「あなたは何をしてよいですか?」を決めること。

ユーザー名とパスワードで本人を確認するのが認証。
GRANTで操作できるDBを決めるのが認可です。

専用ユーザー自身で接続できることも確認しました。

mysql -u musikyun_user -p musikyun
📘 コマンドを読む
-u = 接続するユーザーを指定。
-p = パスワード入力を要求する。
musikyun = 接続先DB。

パスワードそのものをコマンドへ書かないため、 コマンド履歴にパスワードを直接残しません。

STEP 6|PHPからPDOでMariaDBへ接続する

PHP側にDB接続処理を作りました。

<?php

$config = require '/etc/musikyun/database.php';

$dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4";

$pdo = new PDO(
    $dsn,
    $config['username'],
    $config['password'],
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);
📘 PDOって何?
PDO = PHP Data Objects。

PHPからMariaDBなどのデータベースへ接続し、 SQLを実行するための仕組みです。

今回は、 PHPとMariaDBの間をつなぐ窓口 と考えると分かりやすいです。
PHP
↓
PDO
↓
MariaDB
📘 $pdoって何?
PHPでは、$から始まる名前は変数です。

$pdoには、 new PDO(...)で作った 「DBへ接続して操作するためのPDOオブジェクト」が入ります。

以後はこの$pdoを使ってSQLを実行できます。
📘 new PDO()では何が起きる?
new = 新しいオブジェクトを作る。
PDO = PHPが用意しているDB接続用クラス。

DSN・ユーザー名・パスワードなどを渡して、 MariaDBへ接続できるPDOオブジェクトを作っています。
📘 DSNとは?
DSN = Data Source Name。

「どの種類のDBへ、どのホストの、どのDB名で接続するか」 を表す接続先情報です。

今回は mysql:でMariaDB/MySQL系、 hostで接続先、 dbnameでDB名、 charsetで文字コードを指定しています。

STEP 7|PHPから記事を取得する

PDOでMariaDBへ接続できたので、 次はarticlesテーブルから記事を取得します。

<?php

require __DIR__ . '/db.php';

$stmt = $pdo->query(
    'SELECT
        id,
        title,
        content,
        status,
        created_at
     FROM articles'
);

$articles = $stmt->fetchAll();
📘 requireとは?
別のPHPファイルを読み込み、 そのファイルの処理を実行します。

今回はdb.phpを読み込むことで、 そこで作った$pdoを db-test.phpから使えるようにしています。
📘 __DIR__とは?
現在のPHPファイルが置かれている ディレクトリを表す特別な定数です。

今回なら、 /var/www/musikyun を基準にしてdb.phpを読み込みます。
📘 ->って何?
->は、 PHPでオブジェクトが持っている機能を使うときに使う記号です。

$pdo->query(...)
↓
「$pdoが持っている query()という機能を使う」 と読めます。
📘 query()とは?
SQLをデータベースへ送り、 実行するPDOの機能です。

今回はSELECTをMariaDBへ送り、 articlesテーブルの記事を取得しています。
📘 fetchAll()とは?
SQLの実行結果をまとめて取得する機能です。

取得した記事はPHPの配列として $articlesへ入ります。
PHP
↓
$pdo->query()
↓
SELECTをMariaDBへ送る
↓
MariaDBが記事を探す
↓
結果をPHPへ返す
↓
fetchAll()
↓
$articles

STEP 8|DB接続情報をWeb公開領域の外へ分離する

最初の接続テストでは、 DBユーザー名やパスワードを /var/www/musikyun/db.php に書いていました。

しかし、 /var/www/musikyunは nginxのWeb公開領域です。

そこで設定ファイルを置く場所を別に作りました。

sudo mkdir -p /etc/musikyun
📘 コマンドを読む
mkdir = make directory。 ディレクトリを作るコマンドです。
-p = 必要な親ディレクトリも含めて作成できる指定です。
/etc = Linuxで設定ファイルを置く代表的な場所です。

DB接続情報は次のファイルへ移しました。

/etc/musikyun/database.php
<?php

return [
    'host' => 'localhost',
    'dbname' => 'musikyun',
    'username' => 'musikyun_user',
    'password' => '実際のパスワードは記事に書かない',
];
📘 なぜ分ける?
PHPのプログラムと、 DBパスワードなどの秘密情報を 同じWeb公開領域に置かないためです。

公開するプログラムと 秘密の設定情報を分離します。

STEP 9|Linuxの権限を設定する

設定ファイルは、誰でも読める状態にはしません。

まずPHP-FPMがどのユーザーで動いているか確認しました。

grep -E '^(user|group) =' /etc/php/8.3/fpm/pool.d/www.conf

結果:

user = www-data
group = www-data
📘 www-dataって誰?
人間が普段ログインするユーザーではありません。

WebサーバーからPHPを実行するときに、 PHP-FPMが処理を行うためのLinuxユーザーです。

今回ブラウザからPHPを実行すると、 PHPの処理は主にwww-dataとして動きます。

設定ファイルの所有者とグループを変更しました。

sudo chown root:www-data /etc/musikyun/database.php
📘 chownとは?
change owner。 ファイルの所有者などを変更するコマンドです。

root:www-dataは、
所有者 = root
グループ = www-data
という意味です。

さらにアクセス権を設定しました。

sudo chmod 640 /etc/musikyun/database.php
📘 chmod 640を読む
chmod = change mode。 ファイルのアクセス権を変更します。

6 = 読み取り + 書き込み
4 = 読み取り
0 = 権限なし

つまり今回の640は、

所有者 root → 読み取り・書き込み
グループ www-data → 読み取りのみ
その他 → 権限なし

という設定です。

実際の権限も確認しました。

ls -l /etc/musikyun/database.php
-rw-r----- 1 root www-data ... /etc/musikyun/database.php
📘 -rw-r-----を読む
rw- = 所有者rootは読み書きできる。
r-- = グループwww-dataは読める。
--- = その他のユーザーはアクセスできない。

STEP 10|Permission deniedから権限分離を理解する

設定ファイルを分離したあと、 ターミナルから次のテストを実行しました。

php db-test.php

すると、次のエラーが発生しました。

Permission denied
📘 なぜエラーになった?
このコマンドを実行したのは ubuntuユーザーです。

しかしdatabase.phpの権限は、

root → 読める
www-data → 読める
ubuntu → 読めない

という状態でした。

つまりPHPコードが間違っていたのではなく、 実行しているLinuxユーザーに読み取り権限がなかった ために拒否されたのです。
ubuntu が PHPを実行
↓
database.php を読もうとする
↓
ubuntuには読み取り権限がない
↓
Permission denied

次に、本番のPHP-FPMと同じ www-dataとして実行しました。

sudo -u www-data php db-test.php
📘 sudo -u www-dataとは?
sudoを使って、 指定したユーザーとしてコマンドを実行します。
-u = 実行するユーザーを指定します。

今回は 「www-dataとしてphp db-test.phpを実行する」 という意味です。

今度は記事を正常に取得できました。

www-data が PHPを実行
↓
database.php を読む
↓
www-dataには読み取り権限がある
↓
PDOでMariaDBへ接続
↓
記事取得成功
📘 権限分離とは?
すべてのユーザーに同じ権限を与えるのではなく、 必要な人・プログラムに、必要な権限だけ与える 考え方です。

今回はPHP-FPMのwww-dataには DB設定を読む権限を与え、 その他のユーザーからは読めないようにしました。

STEP 11|db.phpから非公開設定を読み込む

<?php

$config = require '/etc/musikyun/database.php';

$dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4";

$pdo = new PDO(
    $dsn,
    $config['username'],
    $config['password'],
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);
📘 $config = require ...とは?
database.phpは return [...]で設定値を返します。

requireでそのファイルを読み込み、 返された配列を$configで受け取っています。
/etc/musikyun/database.php
↓ return
設定値の配列
↓
$config
↓
new PDO()
↓
MariaDB

STEP 12|記事をブラウザへ表示する

最後にindex.phpから記事を取得し、 HTMLとしてブラウザへ表示しました。

<?php

require __DIR__ . '/db.php';

$stmt = $pdo->query(
    'SELECT
        id,
        title,
        content,
        status,
        created_at
     FROM articles'
);

$articles = $stmt->fetchAll();

foreach ($articles as $article) {
    echo '<h2>' . htmlspecialchars($article['title'], ENT_QUOTES, 'UTF-8') . '</h2>';
    echo '<p>' . nl2br(htmlspecialchars($article['content'], ENT_QUOTES, 'UTF-8')) . '</p>';
    echo '<p>状態: ' . htmlspecialchars($article['status'], ENT_QUOTES, 'UTF-8') . '</p>';
}
📘 foreachとは?
配列の中身を1件ずつ順番に処理する反復処理です。

$articlesに複数の記事が入っていれば、 1記事ずつ$articleへ取り出して表示します。
📘 htmlspecialchars()はなぜ必要?
DBから取り出した文字列を、 HTMLとしてそのまま解釈させないためです。

たとえば記事タイトルなどにHTMLやスクリプトとして 解釈される文字が含まれていても、 文字として表示する方向へ変換します。

これはXSS対策の基本の一つです。
📘 XSSとは?
Cross-Site Scripting。

Webページへ意図しないスクリプトなどを混入させ、 ブラウザで実行させる攻撃です。

「DBから取得したデータだから安全」とは限りません。 HTMLへ出力するときにも安全性を考えます。
📘 nl2br()とは?
文章中の改行をHTMLの<br>へ変換します。

本文の改行をブラウザでも見えるようにするために使っています。

STEP 13|ブラウザで最終確認する

ブラウザからmusikyunへアクセスしました。

MariaDBへ保存した次の記事が表示されました。

テスト記事

musikyunのMariaDB接続テストです。

状態: draft
🎉 PHP → MariaDB → ブラウザ表示 成功

VPS上のMariaDBへ保存した記事を、 PHPのPDOから取得し、 nginxとPHP-FPMを経由して ブラウザへ表示できました。

📘 全体の処理を追う

ブラウザからHTTPSでアクセス
↓
nginxがリクエストを受け取る
↓
PHP-FPM(www-data)がPHPを実行
↓
index.php
↓
db.php
↓
/etc/musikyun/database.php
↓
PDO
↓
MariaDB
↓
musikyun / articles
↓
記事データをPHPへ返す
↓
foreachで1記事ずつ処理
↓
htmlspecialchars()でHTML出力用に変換
↓
HTML
↓
nginx
↓ HTTPS
ブラウザへ表示

📘 科目Bにつなげて考える

順次処理
設定を読む → DBへ接続する → SQLを実行する → 結果を取得する → HTMLを作る、という順番で処理されます。
反復処理
foreachで、 取得した記事を1件ずつ処理します。
データ構造
fetchAll()で取得した複数の記事を PHPの配列として扱います。
アクセス制御
root / ubuntu / www-dataで 「誰が何を読めるか」が変わります。

プログラムでも、 入力された主体と許可された操作を分けて考えることが重要です。

📘 入力 → 処理 → 出力

【入力】
ブラウザからページをリクエスト

↓

【処理】
nginx
↓
PHP-FPM
↓
PHP
↓
PDO
↓
SELECT
↓
MariaDB
↓
fetchAll()
↓
foreach
↓
HTML生成

↓

【出力】
記事がブラウザに表示される
🎉 今回できたこと

musikyun専用のMariaDBとarticlesテーブルを作成し、 PHPのPDOから記事を取得して ブラウザへ表示できるようになりました。

さらに、DB専用ユーザー、 Web公開領域と秘密情報の分離、 Linuxの所有者・グループ・アクセス権、 PHP-FPMのwww-dataについても確認しました。

途中で発生したPermission deniedも、 「誰がPHPを実行しているのか」という視点から原因を確認できました。

まとめ

今回のポイント

・MariaDBにmusikyun専用DBを作った。
・articlesテーブルへ記事を保存した。
・PHP専用のDBユーザーを作った。
・PDOでPHPとMariaDBを接続した。
・DB接続情報をWeb公開領域の外へ分離した。
・root / ubuntu / www-dataの違いを確認した。
・chownとchmodでファイル権限を設定した。
・Permission deniedの原因を実際に確認した。
・htmlspecialchars()を使って記事をHTMLへ出力した。
・ブラウザからMariaDBの記事を表示できた。
PHP MariaDB PDO SQL Linux PHP-FPM 権限 セキュリティ XSS CMS