MariaDB 教程 / 综合实战:订单事务、最小权限与备份恢复

本次综合练习建立订单数据库,以受限账户把库存、订单和明细作为一个事务处理,再备份已经提交的状态并在另一空库验证恢复。结构、业务变更、权限及恢复结果都要分别核对,不能用一条“执行成功”消息代替全部检查。

明确操作角色与可以观察的完成条件

使用全新的 final48_lab 学习库,与真实订单、支付服务分开。区分管理员、能管理练习结构的 course_user,以及仅能完成指定业务流程的应用账户。本篇在备份前提交新订单 105,恢复后的基线必须包含这笔已提交交易。

阶段 完成条件
结构 四张 InnoDB 表,带引用和输入约束
新订单 商品 1 库存 20 → 18;订单 105 商品金额 7000
权限 所需读取、订单与明细插入、库存列更新
恢复 独立空库中四表行数为 4、4、5、7
业务核对 已付款商品金额 46000,完整字段与约束一致

先理解事务与 COMMIT、ROLLBACK 的边界和实际账户的允许与拒绝操作。真实应用还需要参数绑定、输入验证、身份认证与请求去重,本篇人工监督的固定输入流程不能直接等同于完整支付系统。

备份和授权依据为 MariaDB 的 mariadb-dump 文档与 GRANT 文档。每种角色应在对应会话执行代码,不要一直用管理员连接来判断应用权限。

建立专用数据库与完整初始样本

管理员先检查数据库和账户清单,确认 final48_lab、final48_restore 及 'final48_app'@'127.0.0.1' 未被使用。名称冲突时另选练习名并同步修改引用,不覆盖现有对象。以下建库、建表与初始数据在管理员的 MariaDB 客户端执行:

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);

保留原始韩文 UTF-8 字符串。商品 1~4 分别为笔记本、圆珠笔、马克杯和台灯;客户 1、3 的城市值为首尔,2、4 分别为釜山与大田。比较结果时使用稳定编号,执行时不要只改部分字符串。

由管理员为已存在的 course_user 增加本练习库权限。ALL 只限专用学习数据库,不是生产账户的通用建议:

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

初始行数应为客户 4、商品 4、订单 4、明细 6。检查主外键及数量、价格约束,区分商品当前目录价格与明细中保存的购买时单价 unit_price。初始已付款商品合计为 39000,不含运费与取消订单。

创建仅能完成订单流程的应用账户

下面代码由管理员执行,先把示例密码替换为唯一练习密码。所需能力是读取商品等本库数据、插入订单和明细、更新库存列;不需要删结构、管理账户、更新价格或删除订单。SHOW GRANTS 的原始输出可能包含认证信息,不要公开保存。

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';

在操作系统终端运行连接命令,在密码提示处输入新密码;按实际环境调整地址与端口:

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

新会话先执行 SELECT CURRENT_USER(); 和商品查询,确认真正使用 final48_app@127.0.0.1。未授权的 ALTER TABLE 应被拒绝,价格列更新也不在 UPDATE(stock) 范围内。正式应用还能按真实表、列与角色设计继续缩小读取范围。

检查库存后,把订单与明细一起提交

以下代码在应用账户的新连接中执行。FOR UPDATE 锁定当前商品行,条件更新在库存足够时才扣减两件。必须立即检查受影响行数:

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 时,才运行下一块。如果为零或发生错误,在当前连接执行 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;

未改变的样本应得到订单 105 的商品金额 7000.00。任何 INSERT、外键或其他语句失败时,停止后续操作并回滚整次尝试;一条语句失败不等于此前扣库存已经自动撤销。确认预留成功、订单和明细正确后,才选择 COMMIT:

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

提交后,商品 1 库存为 18,订单五行、明细七行。之后的 ROLLBACK 不能撤销已经提交的这笔交易。再次使用编号 105 会产生主键重复;如果此前又扣过库存,应回滚该失败尝试。真实请求需要去重与幂等设计,不能简单改用 IGNORE 或 REPLACE 隐藏重复问题。

备份已提交状态,并恢复到另一个空库

使用具有备份所需能力的 course_user,不能把受限应用账户当作备份管理员。Windows PowerShell 中准备新文件,检查命令退出状态、文件大小和哈希,并审查文件中的 USE、CREATE DATABASE、DROP 或跨库引用:

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 客户端连接空目标,启用 SOURCE 遇错停止:

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

进入客户端后先确认 DATABASE() 正确、SHOW TABLES 为空,再读取可信备份文件:

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

InnoDB 备份的一致性与并发 DDL 条件仍然适用;独立数据库恢复验证说明了工具兼容性和部分失败。这个转储不重建全部用户或服务器配置,也不要自动把应用账户改为连接验证库。

Ubuntu 客户端使用下面的 Bash 备份方案,与 Windows 方案二选一。子 shell 将限制性 umask 和禁止覆盖选项限定在这次操作:

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
)

同名文件存在时应失败;命令失败留下的部分文件应保留诊断,改用新名称重试,不要继续恢复。非零文件大小和 SHA-256 并不能证明可恢复性。

Ubuntu 恢复连接如下,随后在 SQL 客户端按实际登录名和绝对文件路径执行 SOURCE,示例用户名 learner 不应照搬:

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;

一个目标只执行一种平台的恢复方案。SOURCE 的遇错停止只阻止文件后续语句,不能撤销之前已创建对象和已提交数据;失败后修正原因并换新的空目标重新验证。不要用 --force 跳过错误后当作成功。

TLS 出错时,按官方 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,已付款商品金额 46000.00,商品 1 库存 18。原有 39000 加上新订单 7000 得到该金额,不包含运费和取消金额。按键比较全部字段,检查主外键、数量与价格约束及重要 JOIN 查询,不能只看行数或一条成功状态。

还应分别验证非法外键、重复主键和未经授权修改被拒绝,以及库存不足时未产生新订单。应用账户在原库的权限不应自动扩展到恢复库。保留版本、转储哈希、核对查询与输出、实际授权范围;关闭练习连接后,再依据归属与保存要求决定清理。本篇不自动删除源库、备份文件或验证库。