简介这份资源是国家开放大学MySQL基础课程的实验训练4配套文档面向正在学习数据库系统维护的在校学生与自学者帮助完成用户管理、权限控制、备份恢复及数据导入导出等核心实验任务。包内仅含1个docx文档压缩包约3.59MB内容围绕汽车用品网上商城Shopping数据库展开涵盖创建Teacher与Student账户、授予与验证SELECT/INSERT/DELETE/UPDATE权限、使用mysqldump备份与恢复数据库、启用并查看二进制日志以及通过SELECT…INTO、LOAD DATA、MySQL Workbench等方式完成会员表和汽车配件表的导出导入并给出CHARACTER SET gbk解决中文乱码的排错思路。文档按实验6-1至6-8逐条编排步骤与验证结果清晰可直接对照操作并整理成实验报告。目前已有1980人学习下载适合需要按实验清单完成作业、巩固数据库维护操作流程的读者参考。1. 数据库系统维护到底在维护什么从一份实验训练文档说起很多人第一次看到“数据库系统维护”这个词脑子里浮现的是重启服务、看看日志、备份一下完事。但真正在生产环境里跑过 MySQL 的人都知道维护的核心不是“救火”而是让数据库在出问题之前就处于可控状态。一份名为“mysql实验训练4-数据库系统维护”的实验文档本质上是在训练一套标准动作用户权限怎么管、数据怎么备份与恢复、日志怎么读、表怎么优化、状态怎么监控。这套动作在实验环境里是练习题在真实业务里就是保命技能。这篇文章面向两类人一是正在做数据库实验、需要把步骤跑通并理解每一步在干什么的学生或初级工程师二是已经上手 MySQL、但对“维护”这件事只有零散经验、想系统补齐备份恢复和性能排查能力的开发者。我会按“先讲清楚为什么这么做再给可复现的命令和参数最后说坑在哪”的顺序展开所有命令都可以在本地 MySQL 实例上直接执行。实验文档给的是骨架这里补的是血肉和踩坑记录。2. 用户与权限维护最小权限原则怎么落到 GRANT 语句上数据库维护的第一道口子往往不是性能而是权限。实验里常见的场景是创建一个新用户只给它某个库的读写权限然后验证它不能碰其他库。这件事听起来简单但 GRANT 语句的粒度、生效范围、以及 MySQL 8 之后默认认证插件的变化足够让新手翻车好几次。2.1 为什么不能直接用 root 跑业务root 在 MySQL 里等同于“什么都能干”包括 DROP DATABASE、修改权限表、读取所有用户数据。业务代码如果用 root 连接一旦出现 SQL 注入或者配置泄露攻击者拿到的就是整个实例的控制权。最小权限原则的要求是每个应用只拿到它真正需要的那几个库、那几张表、那几种操作。常见做法是给每个业务单独建账号按库授权必要时再按表或按列收窄。实验里通常只要求到库级别但真实项目里我一般会多问一句这个账号需要 DDL 权限吗如果不需要就不要给 CREATE、DROP、ALTER。2.2 创建用户并授权的完整命令下面这段在 MySQL 8.0 及以上版本可以直接执行。注意密码策略和认证插件MySQL 8 默认用caching_sha2_password老客户端可能连不上实验环境里如果客户端版本低可以显式指定mysql_native_password。-- 创建实验用账号限定从本机连接 CREATE USER exp_userlocalhost IDENTIFIED BY Exp2024#Test; -- 只给实验库的增删改查权限不给 DDL GRANT SELECT, INSERT, UPDATE, DELETE ON exp_db.* TO exp_userlocalhost; -- 如果确实需要建表权限再单独加 -- GRANT CREATE, INDEX ON exp_db.* TO exp_userlocalhost; -- 刷新权限让授权立即生效 FLUSH PRIVILEGES; -- 查看该用户最终权限 SHOW GRANTS FOR exp_userlocalhost;逻辑说明CREATE USER只负责建账号不附带任何权限GRANT才是授权动作ON exp_db.*表示作用范围是整个 exp_db 库的所有表FLUSH PRIVILEGES在直接用 GRANT 语句时其实不是必须的因为 GRANT 会自己更新内存权限表但实验里保留这一步可以避免“为什么权限没生效”的困惑。参数说明exp_userlocalhost中的主机部分决定从哪里连接localhost只允许本机 socket 连接%允许任意主机生产环境不要随便用%。密码里的特殊字符在命令行里可能需要转义实验时如果报语法错误先检查引号。2.3 验证权限是否真的生效授权之后一定要用新账号实际登录一次而不是只看SHOW GRANTS的输出。下面用 mysql 客户端验证# 用新账号登录注意 -p 和密码之间不要有空格 mysql -u exp_user -pExp2024#Test -h 127.0.0.1 -P 3306 # 登录后执行 USE exp_db; SELECT COUNT(*) FROM some_table; # 尝试访问其他库应该被拒绝 USE mysql; -- ERROR 1044 (42000): Access denied for user exp_userlocalhost to database mysql如果USE mysql没有报错说明权限给大了回去检查是不是误用了ON *.*。另一个常见问题是-h 127.0.0.1和-h localhost在 MySQL 里走的是不同连接路径前者走 TCP后者走 socket授权时的 host 部分要对应上。3. 备份与恢复mysqldump 的参数怎么配才敢用在真实库上备份是数据库维护里最不能省的一环但也是最容易被“随便跑一下”对待的一环。实验文档通常只要求导出一个库再导回去但真实场景里备份要考虑一致性、锁表时间、字符集、存储过程、触发器、以及恢复时的顺序。mysqldump 是 MySQL 自带的逻辑备份工具用对了很稳用错了就是血泪经验。3.1 逻辑备份和物理备份的选型理由mysqldump 属于逻辑备份导出的是 SQL 语句恢复时重新执行。优点是跨版本、跨平台、单表可恢复缺点是慢数据量大时导出和恢复都耗时而且导出期间如果不对表加锁可能出现数据不一致。物理备份比如直接拷贝数据文件或使用企业版工具速度快但和版本、操作系统绑定紧。实验环境里用 mysqldump 足够真实项目里如果库超过几十 GB我会优先考虑物理备份方案或者用主从复制做热备。3.2 一条可复用的 mysqldump 命令下面这条命令在实验和中小型生产库里都适用关键参数都给了注释mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ -u root -p \ exp_db /backup/exp_db_$(date %Y%m%d_%H%M%S).sql逻辑说明--single-transaction让 InnoDB 表在一个一致性快照里导出不锁表这是 InnoDB 场景下最重要的参数--routines导出存储过程和函数--triggers导出触发器--events导出事件调度器里的任务--set-gtid-purgedOFF在非 GTID 复制环境里避免导入时报 GTID 相关错误--default-character-setutf8mb4防止中文乱码。参数说明如果库里有 MyISAM 表--single-transaction对它们无效需要加--lock-tables但这会锁表。-p后面不要直接跟密码回车后交互输入更安全脚本里可以用--defaults-extra-file指定配置文件。3.3 恢复时的顺序和验证恢复不是简单地把 SQL 文件喂进去就完事。如果备份文件里包含建库语句先确认目标实例上没有同名库否则可能覆盖。恢复命令# 先建空库如果备份文件里没有 CREATE DATABASE mysql -u root -p -e CREATE DATABASE IF NOT EXISTS exp_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; # 导入备份 mysql -u root -p exp_db /backup/exp_db_20240101_120000.sql # 验证对比关键表的行数和校验和 mysql -u root -p -e SELECT COUNT(*) FROM exp_db.some_table;恢复后至少做三件事核对关键表行数、检查存储过程和触发器是否还在、用业务查询跑一遍看结果是否正常。实验里经常出现“导入没报错但数据少了一半”的情况多半是备份时没加--single-transaction导致快照不一致或者导入时中途报错被忽略。4. 日志与状态监控从慢查询日志里捞出真正拖慢系统的 SQL数据库维护不能只看“现在有没有报错”还要看“哪些 SQL 在悄悄拖慢系统”。MySQL 的慢查询日志、错误日志、通用日志各有用途实验里通常要求开启慢查询日志并分析一条慢 SQL。这一章讲怎么开、怎么看、怎么用 EXPLAIN 定位问题。4.1 慢查询日志的开启与参数含义慢查询日志默认是关闭的需要手动开。下面在 MySQL 会话里动态开启重启失效要永久生效得写进配置文件。-- 查看当前慢查询相关参数 SHOW VARIABLES LIKE slow_query%; SHOW VARIABLES LIKE long_query_time; -- 动态开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log; SET GLOBAL long_query_time 1; -- 记录未使用索引的查询实验环境建议开生产环境慎开 SET GLOBAL log_queries_not_using_indexes ON;逻辑说明long_query_time单位是秒设为 1 表示执行超过 1 秒的 SQL 会被记录log_queries_not_using_indexes会把没走索引的查询也记下来方便发现潜在问题但如果表很小、查询很频繁这个日志会膨胀得很快。参数说明slow_query_log_file的路径要有写权限Linux 下通常是/var/log/mysql/Windows 下换成对应目录。动态修改只对当前实例生效重启后恢复默认永久生效要改my.cnf或my.ini里的[mysqld]段。4.2 用 mysqldumpslow 和 EXPLAIN 分析慢 SQL慢查询日志本身是文本直接看很费劲。MySQL 自带mysqldumpslow工具可以做聚合# 按执行时间排序显示前 10 条 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 按出现次数排序 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log拿到具体 SQL 后用 EXPLAIN 看执行计划EXPLAIN SELECT o.id, o.amount, u.name FROM orders o JOIN users u ON o.user_id u.id WHERE o.created_at 2024-01-01 ORDER BY o.amount DESC LIMIT 100;重点看type列ALL 是全表扫描ref/range 较好、key列实际用的索引、rows列预估扫描行数、Extra列出现 Using filesort、Using temporary 通常意味着需要优化。如果type是 ALL 且rows很大优先考虑在created_at或user_id上加索引。4.3 表维护ANALYZE、OPTIMIZE 和碎片整理InnoDB 表在大量删除和更新后会产生碎片统计信息也可能过时。实验里常见的维护命令-- 更新索引统计信息让优化器选对执行计划 ANALYZE TABLE exp_db.orders; -- 整理表碎片重建表InnoDB 下会锁表大表慎用 OPTIMIZE TABLE exp_db.orders; -- 查看表状态关注 Data_free 列碎片空间 SHOW TABLE STATUS LIKE orders\GANALYZE TABLE很快可以在业务低峰期定期跑OPTIMIZE TABLE在 InnoDB 下实际是重建表会占用大量 IO 和磁盘空间大表上执行前一定要确认磁盘余量和维护窗口。Data_free如果持续很大说明碎片多但也不是必须马上整理先看性能是否真的受影响。5. 避坑与排查数据库维护实验里最容易翻车的 5 个点这一章记录的是我在实验和真实环境里反复见到的坑每条按“现象 → 原因 → 解决”写。新手照着实验文档做往往就是卡在这些地方。坑一授权后新用户仍然连不上。现象是SHOW GRANTS显示权限正常但用新账号登录报 Access denied。原因通常是 host 部分不匹配比如授权时写的是userlocalhost连接时用了-h 127.0.0.1走的是 TCP 而不是 socket。解决方法是把 host 改成user127.0.0.1或者user%然后FLUSH PRIVILEGES。坑二mysqldump 导出中文乱码。现象是导入后中文变成问号或乱码。原因是导出和导入两端的字符集不一致或者没指定--default-character-set。解决方法是在导出和导入命令里都显式加--default-character-setutf8mb4并确认库和表的字符集也是 utf8mb4。坑三恢复时报表已存在或外键约束失败。现象是导入 SQL 文件时报Table already exists或Cannot add or update a child row。原因是目标库不是空库或者备份文件里表的创建顺序和外键依赖顺序不一致。解决方法是恢复前先建空库或者用--add-drop-table让 mysqldump 在导出时带上 DROP 语句导入时先关外键检查SET FOREIGN_KEY_CHECKS0;导入完再打开。坑四慢查询日志开了但文件是空的。现象是slow_query_log显示 ON但日志文件里没内容。原因是long_query_time设得太大或者查询确实没超过阈值也可能是日志路径没写权限。解决方法是先把long_query_time设成 0 测试一下确认有日志写入后再调回合理值同时检查 MySQL 进程对日志目录的写权限。坑五OPTIMIZE TABLE 把磁盘撑爆。现象是执行OPTIMIZE TABLE过程中报磁盘空间不足甚至导致服务异常。原因是 InnoDB 下这个操作会重建表需要额外的磁盘空间存放临时数据。解决方法是大表不要随便 OPTIMIZE先看Data_free是否真的值得整理必须做的话选业务低峰期并提前确认磁盘余量至少是表大小的两倍。6. 把维护动作变成可重复的检查清单一个自动化脚本的写法实验做完不等于维护能力就到位了。真实环境里维护动作要能重复执行、能留下记录、能在出问题时快速回溯。我一般会把日常检查写成脚本定时跑输出一份简短报告。下面这个 bash 脚本覆盖了连接数、慢查询、表碎片、备份文件时间四个检查点可以直接改成自己环境的版本。#!/bin/bash # db_health_check.sh - MySQL 日常维护检查脚本 # 用法./db_health_check.sh /var/log/db_check_$(date %F).log MYSQL_USERroot MYSQL_PASSyour_password MYSQL_HOST127.0.0.1 echo 检查时间: $(date %Y-%m-%d %H:%M:%S) # 1. 当前连接数和最大连接数 echo --- 连接数 --- mysql -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -e SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections; # 2. 慢查询数量本次启动以来 echo --- 慢查询统计 --- mysql -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -e SHOW GLOBAL STATUS LIKE Slow_queries; # 3. 碎片最大的 5 张表 echo --- 碎片 TOP5 --- mysql -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -e SELECT table_schema, table_name, ROUND(data_free/1024/1024, 2) AS free_mb FROM information_schema.tables WHERE data_free 0 ORDER BY data_free DESC LIMIT 5; # 4. 最近备份文件时间 echo --- 最近备份 --- ls -lt /backup/*.sql 2/dev/null | head -3 echo 检查结束 逻辑说明脚本用mysql -e执行单条或多条 SQL 并直接输出适合放进 cron 定时跑。连接数检查用来发现连接泄漏慢查询数量用来判断是否需要进一步分析日志碎片 TOP5 用来决定是否安排 OPTIMIZE备份文件时间用来确认备份任务没有静默失败。参数说明MYSQL_PASS直接写在脚本里有泄露风险生产环境建议用--defaults-extra-file指向一个权限为 600 的配置文件。data_free的单位是字节脚本里除以两次 1024 转成 MB。cron 里跑的时候注意环境变量mysql命令最好写绝对路径。这个脚本的价值不在于它多复杂而在于它把“维护”从一次性实验变成了可重复的例行动作。我自己的习惯是每周看一次碎片和慢查询趋势每月做一次恢复演练——备份文件不验证等于没有备份。恢复演练不需要在 production 上做本地起一个实例把最近的备份导进去跑几条业务查询确认数据完整这件事花不了半小时但能在真出事的时候省下几个通宵。希望帮到你。本文还有配套的精品资源点击获取