MariaDB講座 / 注文DBの構築・権限・バックアップ・復元を総合確認する

総合実習では注文DBを構築し、制限アカウントで注文と在庫の変更を確認・確定した後、バックアップを別の空DBへ復元します。構造、業務変更、権限と復旧は、個別の結果で確認します。

役割と完了基準を決める

本番の注文・決済とは別のfinal48_labを使います。管理者、構造を管理できる学習者、制限アプリケーションを分けます。商品1の在庫20→18、新注文105の商品額7000、復元先の件数4・4・5・7、paid商品額46000を基準にします。

実装には入力検証、パラメーター、認証と二重処理防止も必要です。トランザクション、Pythonのエラー分岐を確認し、復元とレプリカの確認は別の作業として扱います。

未使用の専用DBと完全な初期データを作る

管理者でfinal48_lab、final48_restore、final48_appが未使用か確認し、既存なら別名を選びます。

CREATE DATABASE final48_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE final48_lab;
CREATE TABLE c48_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c48_order_customer FOREIGN KEY (customer_id)
  REFERENCES c48_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c48_item_order FOREIGN KEY (order_id)
  REFERENCES c48_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c48_item_product FOREIGN KEY (product_id)
  REFERENCES c48_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c48_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c48_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c48_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c48_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

顧客名と都市、商品名は原文の韓国語サンプルを保っています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、IDと数値で結果を照合します。

必要ならcourse_userにこの実習領域を与えます。

GRANT ALL PRIVILEGES ON final48_lab.* TO 'course_user'@'127.0.0.1';

初期件数は顧客4・商品4・注文4・明細6です。購入時単価と現在価格を分け、参照と数量の制約を読みます。

作業に必要な権限だけで接続する

管理者でパスワードの例を固有値に置き換えます。

CREATE USER 'final48_app'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON final48_lab.* TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_orders TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_items TO 'final48_app'@'127.0.0.1';
GRANT UPDATE(stock) ON final48_lab.c48_products TO 'final48_app'@'127.0.0.1';
SHOW GRANTS FOR 'final48_app'@'127.0.0.1';

読取、注文・明細の追加と在庫列の更新を許可し、DDL、ユーザー管理、価格変更や注文削除は与えていません。端末から新接続します。

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=final48_app --password --default-character-set=utf8mb4 final48_lab

CURRENT_USER()と商品読取を確認し、許可していないALTER TABLEが拒否されることも確認します。実運用は必要に応じて表・列・ロールをさらに狭くできます。

在庫確保の件数を確認してから注文する

同じアプリケーション接続で開始します。

USE final48_lab;
START TRANSACTION;
SELECT product_id,price,stock FROM c48_products WHERE product_id=1 FOR UPDATE;
UPDATE c48_products SET stock=stock-2 WHERE product_id=1 AND stock>=2;
SELECT ROW_COUNT() AS reserved_rows;

reserved_rowsが1の場合だけ次へ進みます。0やエラーならROLLBACKして止め、注文・明細を追加しません。

INSERT INTO c48_orders(order_id,customer_id,ordered_at,status,shipping_fee)
VALUES(105,4,'2026-03-01 10:00:00','paid',1000);
INSERT INTO c48_items(order_id,product_id,quantity,unit_price)
VALUES(105,1,2,3500);
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c48_items WHERE order_id=105 GROUP BY order_id;

初期状態ならgoods_totalが7000です。途中でどれか失敗したら、後続を止め全体を取消します。これは既知入力を人が監督する例で、プログラムは分岐を実装する必要があります。すべて確認してから確定します。

COMMIT;
SELECT stock FROM c48_products WHERE product_id=1;
SELECT COUNT(*) FROM c48_orders;
SELECT COUNT(*) FROM c48_items;

在庫18、注文5、明細7です。105の再実行は重複なので、IGNOREやREPLACEで隠さず要求の二重処理を設計します。確定後の通常ROLLBACKは元に戻しません。

確定した状態を新規ファイルへ保存する

制限アプリではなく、バックアップに必要なcourse_userで端末から実行します。未使用のファイル名と終了状態を確認し、ダンプにDB切替命令がないか読みます。

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$courseBackupPath='C:/mariadb-lab/final48-checked.sql'
if (Test-Path -LiteralPath $courseBackupPath) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=$courseBackupPath final48_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed; do not restore this file.' }
Get-Item -LiteralPath $courseBackupPath | Select-Object Name,Length
Get-FileHash -LiteralPath $courseBackupPath -Algorithm SHA256

管理者が未使用の空復元先を作ります。

CREATE DATABASE final48_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON final48_restore.* TO 'course_user'@'127.0.0.1';

接続して空であることを確認してから実行します。

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

SELECT DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/final48-checked.sql;

スナップショットと同時DDLはダンプの条件、クライアント互換性と部分失敗は復元検証で確認します。全ユーザーや設定はこのDBダンプに含まれず、アプリケーションを自動で復元先へ向けません。

Ubuntuの保存と復元は代替を選ぶ

Windows版の代わりに、制限した新規出力を使います。

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/final48-checked.sql"
  if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
      --user=course_user --password --default-character-set=utf8mb4 \
      --single-transaction --quick --skip-lock-tables final48_lab > "$backup_file"; then
    test -s "$backup_file" || exit 1
    sha256sum "$backup_file"
  else
    printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
    exit 1
  fi
)

既存出力なら停止し、失敗ファイルを復元しません。実際のユーザー名と絶対パスへ変えて接続します。

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

SELECT DATABASE();
SHOW TABLES;
SOURCE /home/learner/mariadb-lab/final48-checked.sql;

一対象へ二OSの復元を重ねません。エラー停止は先に反映したものを取消さないため、新しい空対象で再試行します。TLSエラーでは版、名前、信頼CAと必要証明書を調べ、検証を無効にして通さないでください。

元と復元先、拒否される操作を照合する

次を復元先で実行します。

SELECT (SELECT COUNT(*) FROM final48_restore.c48_customers) AS customers,
 (SELECT COUNT(*) FROM final48_restore.c48_products) AS products,
 (SELECT COUNT(*) FROM final48_restore.c48_orders) AS orders_count,
 (SELECT COUNT(*) FROM final48_restore.c48_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM final48_restore.c48_orders o JOIN final48_restore.c48_items i ON i.order_id=o.order_id
WHERE o.status='paid';
SELECT stock FROM final48_restore.c48_products WHERE product_id=1;
SHOW CREATE TABLE final48_restore.c48_items;

件数4・4・5・7、paid商品額46000、商品1の在庫18です。主キーごとの内容と制約、重要なJOINも比較します。外部キー不正、主キー重複、未許可の変更の拒否は別に確認します。

版、ファイルハッシュ、検証SQLと出力、権限範囲を保存します。片付ける場合は接続を閉じ、実習専用の所有と保持要否を確認します。元DB、バックアップや検証DBをこの記事から自動削除しません。

関連する公式リファレンス

• mariadb dump（公式ドキュメント）
• grant（公式ドキュメント）
• securing connections for client and server（公式ドキュメント）