简介面向南京邮电大学数据库系统课程实验二这份实验报告围绕 DBMS 的数据库保护展开适合正在完成同类实验或复习 MySQL 事务与权限管理的计算机专业学生。报告以安全控制和并发控制为主线包含用户 U1/U2 创建与权限分配、GRANT/REVOKE 授权回收、事务提交与回滚、共享锁/排他锁下的多用户并发修改等完整验证过程并结合 emp 表用例展示操作结果与实验小结。压缩包共 1 个 doc 文档体积约 1.3MB内容为可直接参考的完整报告文本。已有 152 人学习下载对需要快速理解数据库保护机制、撰写实验报告或准备上机考核的读者是一份结构清晰、可直接对照实践的参考资料。1. 数据库保护实验先把数据保住再谈优化数据库、MySQL 这类实验里真正容易翻车的往往不是建表或写 SQL而是数据库保护。这份某高校数据库系统实验报告二主题恰好是 DBMS 的数据库保护权限控制、完整性约束、事务并发与备份恢复。实际业务里最常见的事故——一条 UPDATE 忘加 WHERE 导致全表被改写、两个事务同时扣库存导致超卖、磁盘故障后数据找不回来——全都是保护机制没做到位。适合正在补数据库原理的从业者也适合被数据库课程设计折腾的同学照着实验把保护机制完整跑一遍比零散看文档更落地。2. 安全性与完整性控制把“谁能碰”和“数据长什么样”先定死2.1 权限模型为什么业务账号不该用 root很多人做实验时图省事直接用 root 连数据库但这恰恰绕过了数据库保护的第一道门。MySQL 的账号由 user 和 host 共同定义root 拥有全部权限一旦连接串泄露任何拿到它的人都能改表、删库、改权限。实验报告里要求单独创建业务账号只给某个库的增删改查权限不给 DROP、不给全局权限这就是最小权限原则。CREATE USER app_userlocalhost IDENTIFIED BY Str0ng_Pass; GRANT SELECT, INSERT, UPDATE, DELETE ON schooldb.* TO app_userlocalhost; SHOW GRANTS FOR app_userlocalhost;这段 SQL 先创建账号再把 schooldb 库的四个 DML 权限授给它。注意localhost限制了只能本机登录如果应用部署在另一台机器应写具体的网段比如10.10.0.%不要图省事写%。schooldb.*是库级权限比表级权限好维护又比全局权限安全。MySQL 8.0 里有个语法变化经常让人踩坑早期版本 GRANT 语句能顺便创建用户8.0 之后必须先有 CREATE USER否则直接报错。收回权限用 REVOKEREVOKE DELETE ON schooldb.* FROM app_userlocalhost;执行后app_user再执行 DELETE 会报ERROR 1142 (42000): DELETE command denied这正是实验要求的“越权操作被拒绝”现象。还有一个老生常谈的认知要澄清如果用的是 CREATE USER、GRANT、REVOKE 这类 DCL 语句权限变更会自动生效不需要 FLUSH PRIVILEGES。只有绕过 SQL 直接改mysql.user表时才需要手动 flush实验里尽量别走那条路。2.2 完整性约束数据库层的约束才是最后的兜底权限管住了“谁”完整性约束管住“数据能不能长成这样”。实验报告里会把约束分成几类实体完整性靠主键参照完整性靠外键域完整性靠数据类型和 CHECK用户自定义完整性靠触发器。代码里做校验不是不行但应用层校验有漏洞换一个客户端直连数据库或者并发窗口里两条请求同时通过校验就失效了。数据库层的约束是最后一道兜底。实验要求建表时就把约束建全而不是等数据出问题再补。CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT CHECK (age 0 AND age 120) ); CREATE TABLE course ( id INT PRIMARY KEY, title VARCHAR(100) NOT NULL ); CREATE TABLE score ( id INT PRIMARY KEY, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE, CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(id) );CHECK (age 0 AND age 120)把年龄字段限定在合理范围外键保证 score 里的 student_id 和 course_id 必须真实存在。ON DELETE CASCADE表示删除学生时他的成绩记录一起删掉避免出现悬空引用。这里有一个 MySQL 版本的坑8.0.16 之前CHECK 约束会被解析但静默忽略不真正生效。如果实验环境是旧版本需要靠触发器补位比如限制成绩不能小于 0 或大于 100CREATE TRIGGER trg_score_before_insert BEFORE INSERT ON score FOR EACH ROW BEGIN IF NEW.score 0 OR NEW.score 100 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT score must be 0-100; END IF; END;触发器里的SIGNAL SQLSTATE 45000是主动抛错插入非法成绩会被数据库直接拦下。这个脚本适合放在实验报告里作为“用户自定义完整性”的验证部分。理解约束比背约束重要建表时想清楚“哪些脏数据是业务上绝对不能接受的”再决定用 CHECK、外键还是触发器。2.3 完整流程把账号、授权、约束、验证一次跑通实验二通常要求把安全性和完整性串成一个完整流程而不是分开做几个小验证。我的习惯是先建库建表再建账号授权最后用受限账号故意做违规操作确认每条都被数据库挡下来。mysql -uroot -p -e CREATE DATABASE IF NOT EXISTS schooldb DEFAULT CHARACTER SET utf8mb4; mysql -uroot -p schooldb schema.sql mysql -uroot -p -e CREATE USER IF NOT EXISTS app_userlocalhost IDENTIFIED BY Str0ng_Pass; GRANT SELECT, INSERT, UPDATE, DELETE ON schooldb.* TO app_userlocalhost; mysql -uapp_user -p schooldb -e DELETE FROM student WHERE id 999;最后一条以 app_user 身份执行。由于只授了 DELETE 权限这条语句如果目标记录存在会被正常删除想验证“权限不足”的报错可以再试一次删表或改表结构-- 以 app_user 执行预期报错 DROP TABLE student; ALTER TABLE student ADD COLUMN gender VARCHAR(10);两个语句都会因为缺少 DDL 权限返回 1142 错误。这个现象要保留在实验记录里它是“最小权限生效”的直接证据。整套流程里最容易被忽略的是验证方式不是“命令执行成功就行”而是要看错误码。MySQL 的权限错误 1044、1142、1227 分别对应不同的拒绝场景记录到报告里会让整个实验更有说服力。完整性约束的验证也一样插入重复主键报 1062违反外键报 1452违反 CHECK 报 3819这些错误码都是以后排查问题的抓手。3. 并发控制与事务用两把锁和隔离级别拦住超卖3.1 ACID 在 MySQL 里靠什么撑起来数据库保护不只是防止别人乱改数据还要防止正常操作之间互相干扰。实验报告里的事务部分背后就是 ACID 四个特性。原子性靠 undo log持久性靠 redo log隔离性靠锁和 MVCC一致性则靠前面说的约束加上事务机制共同保证。很多人在这一步开始觉得玄学其实实验里只需要观察现象。先看当前环境的隔离级别和日志状态SELECT transaction_isolation; SHOW VARIABLES LIKE log_bin; SHOW ENGINE INNODB STATUS\G第一条命令查看隔离级别第二条确认 binlog 是否开启第三条可以看到 InnoDB 当前的事务、锁等待和日志信息。注意MySQL 8.0 的变量名是transaction_isolation旧版本叫tx_isolation在实验报告里写错变量名会直接得到 NULL。3.2 隔离级别的边界READ COMMITTED 与 REPEATABLE READMySQL 默认隔离级别是 REPEATABLE READ能同时防住脏读和不可重复读并在一定程度上防幻读。但这背后是快照读不是每条 SELECT 都实时看最新数据。实验中很容易做出一个“矛盾现象”事务 A 开启后查不到事务 B 刚插入的数据但用SELECT ... FOR UPDATE又能查到。-- 会话 1 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT * FROM stock WHERE id 1; -- 会话 2 UPDATE stock SET num num - 5 WHERE id 1; COMMIT; -- 会话 1 再次查询结果依然显示旧值 SELECT * FROM stock WHERE id 1; -- 会话 1 使用当前读 SELECT * FROM stock WHERE id 1 FOR UPDATE;普通 SELECT 走快照读事务开始后第一次查询的快照会被复用所以看不到其他事务的提交FOR UPDATE 走当前读每次都能拿到最新已提交数据。这就是实验里“可重复读”与“最新数据”同时存在的边界。把隔离级别切到 READ COMMITTED 再跑一遍普通 SELECT 的结果会变化这就是不可重复读的直观演示。实验报告里建议两个级别都做对比输出结果比背定义管用。3.3 锁等待模拟两个事务抢同一行时到底发生了什么并发保护最经典的验证是行锁冲突。用两个终端模拟“抢库存”-- 会话 A START TRANSACTION; UPDATE stock SET num num - 1 WHERE id 1; -- 会话 B START TRANSACTION; UPDATE stock SET num num - 1 WHERE id 1;会话 B 的 UPDATE 会一直阻塞直到会话 A 执行 COMMIT 或 ROLLBACK。这个场景里数据库用行锁保证同一时刻只有一个人能改这条记录。实验报告要求把阻塞现象抓下来常见做法是另开一个终端查看锁等待SHOW PROCESSLIST; SELECT * FROM information_schema.INNODB_TRX\G如果阻塞时间过长会触发锁等待超时。这个阈值由innodb_lock_wait_timeout控制默认 50 秒。实验时为了快速看到超时效果可以调小一点SET SESSION innodb_lock_wait_timeout 5;这个参数只影响锁等待不影响事务执行时长别当成通用超时来调。另一个容易翻车的点是引擎整个事务实验必须在 InnoDB 表上做MyISAM 不支持事务UPDATE 会直接提交ROLLBACK 根本救不回来。后面避坑章节会再展开。4. 备份与恢复把误删的数据用 binlog 捞回来4.1 备份选型逻辑备份、物理备份与 binlog 的配合实验报告里的“数据库保护”最后通常落到备份恢复。备份方式要按场景选mysqldump 是逻辑备份生成 SQL 文本适合中小规模库和跨版本迁移缺点是恢复慢物理备份直接拷贝数据文件速度快但依赖工具和平台binlog 则是增量恢复和误操作恢复的核心。实验里最稳的组合是“mysqldump 全量 binlog 增量”。在这之前要先确认 binlog 开着没开的话后面一切时间点恢复都无从谈起SHOW VARIABLES LIKE log_bin;如果是 OFF需要改配置后重启服务。配置写在 my.cnf 的 [mysqld] 段[mysqld] server-id 1 log-bin mysql-bin binlog_format ROWserver-id 在开启 binlog 时必须配置不配置实例可能起不来。binlog_format 建议用 ROW恢复时能精确定位到具体行变更对实验场景来说最直观。4.2 全量备份与时间点恢复的标准动作全量备份命令看起来简单参数含义很关键mysqldump --single-transaction --master-data2 --set-gtid-purgedOFF -uroot -p schooldb schooldb_full.sql--single-transaction基于 InnoDB 的 MVCC 拿到一个一致性的快照备份过程中其他事务的写入不会被混进来。--master-data2会把 binlog 位置信息以注释形式写在备份文件头部这是后续增量恢复的坐标。--set-gtid-purgedOFF是为了避免在没开 GTID 的环境里导入备份时权限报错。模拟一次误删然后把它救回来DELETE FROM student WHERE id 100;恢复分两步。第一步导入全量备份把数据库恢复到备份时刻的状态mysql -uroot -p schooldb schooldb_full.sql第二步用 binlog 补上备份点之后、误删之前的事务mysqlbinlog --start-datetime2025-01-01 10:00:00 \ --stop-datetime2025-01-01 10:30:00 \ mysql-bin.000008 | mysql -uroot -p schooldb--start-datetime和--stop-datetime按事务提交时间截取增量区间。单独用全量备份会丢最后一次备份后的全部修改单独用 binlog 又缺少基线数据两者必须按顺序配合。时间窗口宁可稍微放大一点也不要卡太紧漏掉操作多出来的数据可以再手工修正。4.3 误删一张表的完整复盘实验里更极端一点的要求是模拟表被删然后完整恢复。流程一样但要多看一个坐标信息。备份文件头部会有一行注释grep -n CHANGE MASTER TO schooldb_full.sql输出类似-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000008, MASTER_LOG_POS156;这表示全量备份对应的 binlog 文件是mysql-bin.000008位置在 156后续增量恢复要从这个文件往后找。恢复步骤是先建库再导表最后重放 binlogmysql -uroot -p -e DROP TABLE schooldb.score; mysql -uroot -p schooldb schooldb_full.sql mysqlbinlog --start-position156 \ --stop-position890 \ mysql-bin.000008 | mysql -uroot -p schooldb这里用--start-position和--stop-position替代时间窗口精度更高。位置参数从备份文件的 CHANGE MASTER TO 注释里取结束位置则要提前在 binlog 里定位到误删语句的前一条事务。把 binlog 解码成可读 SQL 再定位是这一步最实用的小技巧mysqlbinlog --base64-outputDECODE-ROWS -v mysql-bin.000008 binlog_decode.sql grep -n DROP TABLE binlog_decode.sql找到故障语句的行号后回看它前面的位置值就是恢复终点。这个习惯救过我很多次凡是做恢复实验先解码、先定位再动手重放而不是闭着眼把整个 binlog 灌进去。5. 常见问题与避坑排查五个高频翻车点的一次性解决方案5.1 GRANT 已执行却还是 1044 / 1142现象CREATE USER 和 GRANT 都执行成功但用新账号连接后查数据仍然报权限不足。原因最常见的是 host 不匹配。app_userlocalhost只能通过本地 socket 连接用mysql -h 127.0.0.1走 TCP 时MySQL 会把它当成另一个账号app_user127.0.0.1该账号并不存在。其次是授了 A 库的权限实际 USE 的是 B 库。解决先确认登录来源再核对授权SHOW GRANTS FOR app_userlocalhost; SELECT user, host FROM mysql.user;如果应用要远程连授权时把 host 写成具体的 IP 或网段例如app_user10.10.0.%。不要一上来就给%那等于对全网开放登录入口违背最小权限原则。5.2 并发扣库存还是超卖事务白开了现象两个会话同时“先查库存再判断够不够最后 UPDATE”库存明明只剩 1却卖出去两单。原因两个会话都先用普通 SELECT 读库存在 REPEATABLE READ 下读到同一个旧快照都认为库存够然后各自更新。判断和扣减之间没有加锁也没有用原子操作。解决把“判断 扣减”合并成一条原子 UPDATE让数据库的行锁来保证互斥UPDATE stock SET num num - 1 WHERE id 1 AND num 0;执行后检查影响行数等于 1 表示扣减成功等于 0 表示库存不足。想保留先查后改的写法就把查询改成当前读锁住那行SELECT num FROM stock WHERE id 1 FOR UPDATE;实验报告里把两种写法都跑一遍记录吞吐和超卖次数结论非常直观。5.3 MyISAM 表做事务ROLLBACK 说好了却没生效现象开了 START TRANSACTION执行 DELETE 后 ROLLBACK数据还是没了。原因表引擎是 MyISAM不支持事务。MyISAM 的 DML 语句隐式提交START TRANSACTION 不会让它获得事务能力。解决先确认引擎SHOW TABLE STATUS FROM schooldb WHERE Name stock;看到 Engine 列不是 InnoDB就改掉ALTER TABLE stock ENGINE InnoDB;以后建表直接写ENGINEInnoDB。这个坑在并发实验里尤其隐蔽——同一个实验步骤在 InnoDB 下表锁等待正常在 MyISAM 下表锁直接串行现象完全不同容易让人误判是锁配置出了问题。5.4 备份恢复后外键校验失败导入半路中断现象用 mysqldump 生成的文件恢复时报ERROR 1452 (23000): Cannot add or update a child row导入停在半路。原因单库备份文件通常会在开头写入SET FOREIGN_KEY_CHECKS0所以整库恢复没问题。但如果是手工按表导出、再按表导入父表数据还没进库子表先插入就会触发外键失败。解决尽量整库导出、整库恢复不要拆表。手工导入时在会话里先关掉外键检查SET FOREIGN_KEY_CHECKS 0; -- 导入数据 SET FOREIGN_KEY_CHECKS 1;恢复完成后记得做一次外键校验确认没有悬空引用再重新打开检查。关检查只解决导入顺序问题不能掩盖数据本身的完整性漏洞。5.5 binlog 恢复后数据比预期多或比预期少现象用 mysqlbinlog 重放后发现误删的语句也被重放了或者故障前一秒的操作没恢复进来。原因时间窗口定得不准。事务提交时间和本地时间可能有偏差--stop-datetime如果卡在故障语句附近很容易把故障操作一起重放进去。解决先用解码模式把 binlog 转成可读 SQL定位到具体误操作前后的位置值mysqlbinlog --base64-outputDECODE-ROWS -v mysql-bin.000008 binlog_decode.sql grep -n DELETE FROM student binlog_decode.sql找到误删语句对应的位置后把它前一个事务的结束位置作为恢复终点再用--stop-position精确重放。恢复前先在测试库整体演练一遍确认数据行数和关键记录都对再对生产库执行。这个习惯能省掉很多“数据对不上”的返工。6. 进阶验证把保护方案变成一张故障演练清单做完实验不等于保护机制真的可用。我会在收尾时跑一遍故障演练把核心保护能力逐项验证而不是只看建表和授权成功。下面这张清单适合直接贴在实验报告最后也可以作为以后数据库上线前的检查表。验证项操作方式预期结果越权访问拦截用受限账号执行 DROP TABLE报错 1142操作被拒绝完整性约束生效插入重复主键、非法外键分别报错 1062、1452并发更新互斥两个事务更新同一行后执行者等待先提交者释放锁误操作恢复全量备份 binlog 时间点重放目标表恢复到故障前状态再配一段环境预检脚本把关键开关一次性查出来mysql -uroot -p -e SHOW VARIABLES LIKE log_bin; mysql -uroot -p -e SELECT transaction_isolation; mysql -uroot -p -e SHOW TABLE STATUS FROM schooldb WHERE Namestock;这三条分别确认 binlog 开启、隔离级别和表引擎。任何一个不符合预期后面的备份恢复和并发实验都不具备可复现条件。从那以后我每次给数据库环境做保护类实验都强制先跑一遍这三个检查再动手写 SQL误删恢复也坚持先解码定位再重放。这套流程让“数据库保护”从纸面概念变成了可验证的工程习惯希望帮到你。本文还有配套的精品资源点击获取