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




