简介这份资源是国家开放大学MySQL基础课程的实验训练4配套文档面向正在学习数据库系统维护的在校学生与自学者帮助完成用户管理、权限控制、备份恢复及数据导入导出等核心实验任务。包内为1个docx文档大小约3.59MB内容围绕汽车用品网上商城Shopping数据库展开涵盖创建Teacher与Student账户、授予与验证SELECT、INSERT、DELETE、UPDATE权限、使用mysqldump备份与恢复数据库、启用二进制日志、借助SELECT INTO OUTFILE与LOAD DATA完成会员表和汽车配件表的导出导入并给出CHARACTER SET gbk解决中文乱码的排错思路。文档按实验6-1至6-8逐条编排步骤与验证结果清晰可直接对照操作并整理实验报告。目前已有1980人学习下载适合需要按实验清单完成作业、巩固数据库维护流程的读者参考。1. 数据库系统维护到底在维护什么从一次凌晨三点的告警说起很多同学做完 MySQL 实验训练 4 之后最大的困惑不是“不会敲命令”而是“敲完这些命令到底在防什么”。我带过几届做数据库实验的学生也帮某公司处理过线上库的突发故障最典型的一次是凌晨三点收到磁盘告警一张业务表所在的磁盘分区被 binlog 撑爆写入全部阻塞。事后复盘发现这台实例从上线起就没做过日志清理策略也没人定期看information_schema里的表空间增长。数据库系统维护这件事说白了就是让一套正在跑的 MySQL 实例在容量、性能、安全、可恢复性四个维度上持续可控而不是等它崩了再去救。这个实验训练对应的正是这套能力用户权限怎么收口、日志怎么轮转、备份怎么验证、表空间和碎片怎么处理、慢查询怎么定位。它适合两类人——一类是刚学完 SQL 语法、第一次接触“运维视角”的学生另一类是写了几年业务代码、突然被要求兼管数据库的开发者。前者需要把命令和场景对上号后者需要把零散经验补成一套可执行的检查清单。下面我按“先立住原理、再落到命令、最后讲坑”的顺序把这份实验训练里真正值得动手的部分拆开讲。2. 用户权限与连接层维护先把门锁好再谈性能数据库维护的第一优先级不是调参而是确认“谁能从哪台机器、用什么身份、对哪些库做什么”。我见过太多实验环境里 root 账号允许任意主机连接、密码还是空的情况这种库一旦暴露在可访问网段基本等于把家门钥匙插在锁上。这一章先把权限模型和连接层配置讲清楚再给可直接复现的命令。2.1 权限表模型mysql 库里的四张表各管什么MySQL 的权限校验是分层级的理解这一点比死记GRANT语法重要得多。核心是mysql库下的四张表user表决定“这个账号能不能连上来”db表决定“能访问哪些库”tables_priv决定“能访问哪些表”columns_priv决定“能访问哪些列”。连接建立时先查user表做认证认证通过后每次执行语句再逐层检查后面三张表。这意味着一个常见误区只REVOKE了库级权限但user表里还留着Super或Grant这类全局权限账号依然能做危险操作。排查时我一般直接查权限表而不是靠记忆-- 查看某个账号在各层级的实际授权情况 SELECT host, user, Select_priv, Super_priv, Grant_priv FROM mysql.user WHERE user app_user; -- 查看库级授权 SELECT * FROM mysql.db WHERE user app_user; -- 查看表级授权 SELECT * FROM mysql.tables_priv WHERE user app_user;逻辑说明第一条查全局权限重点看Super_priv能改运行时参数、杀连接和Grant_priv能把权限再授给别人这两个在生产账号上通常必须为N。第二条查库级权限确认业务账号只拿到自己那个库。第三条查表级权限用于精细化收口。参数上要注意host字段支持通配符%表示任意主机实验环境图省事常用它但生产上应尽量写成具体网段如10.0.%。2.2 最小权限账号的创建与验证流程“最小权限”不是一句口号落到操作上就是业务账号只给SELECT/INSERT/UPDATE/DELETE需要建表才给CREATE绝不给DROP和GRANT。下面这套流程可以直接抄-- 1. 创建只允许内网网段连接的账号 CREATE USER app_user10.0.% IDENTIFIED BY Str0ng_Pass!2024; -- 2. 只授予业务库的增删改查 GRANT SELECT, INSERT, UPDATE, DELETE ON biz_db.* TO app_user10.0.%; -- 3. 刷新权限改权限表后必须执行 FLUSH PRIVILEGES; -- 4. 验证用新账号登录后尝试越权操作应当被拒绝 -- DROP TABLE biz_db.orders; -- 预期报错command denied逻辑说明第一步的10.0.%限定了来源网段比%安全得多。第二步只给四个 DML 权限DROP、ALTER、GRANT一律不给。第三步FLUSH PRIVILEGES在直接用GRANT语句时其实会自动生效但如果你手工改过权限表就必须执行。第四步是关键——很多人授完权就结束了从不做越权验证结果权限给多了自己都不知道。验证时故意执行一条越权语句看到command denied才算收口成功。注意实验环境里经常出现“账号密码写在代码里”的情况维护时顺手检查一下应用配置文件把明文密码换成环境变量或配置中心这属于数据库维护的外延但同样重要。2.3 连接数与超时参数别让空闲连接拖垮实例权限收好之后第二个要盯的是连接层。默认max_connections是 151实验环境够用但一旦有连接泄漏应用拿了连接不还很快就会打满新请求全部报Too many connections。我一般会同时看三个指标当前连接数、活跃连接数、以及wait_timeout的设置。-- 查看连接相关参数 SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE wait_timeout; SHOW VARIABLES LIKE interactive_timeout; -- 查看当前连接分布 SHOW STATUS LIKE Threads_connected; SHOW STATUS LIKE Threads_running; -- 找出空闲时间过长的连接 SELECT id, user, host, db, command, time, state FROM information_schema.processlist WHERE command Sleep AND time 300 ORDER BY time DESC;逻辑说明Threads_connected是当前总连接Threads_running是正在执行语句的连接两者差距大说明大量连接在空转。wait_timeout控制非交互连接空闲多久被断开默认 28800 秒8 小时偏长我一般调到 600 到 1800 秒之间让泄漏的连接尽快释放。最后那条查询能直接列出空闲超过 300 秒的连接确认是应用连接池配置问题还是真的有人在挂着。参数调整用SET GLOBAL wait_timeout 900;但注意这是运行时生效重启会丢要持久化得写进配置文件。3. 日志体系与备份恢复出事时你手里得有后悔药数据库维护里最容易被忽视、出事时又最要命的就是日志和备份。日志是黑匣子备份是后悔药两者缺一不可。这一章讲清楚 binlog、error log、slow log 各自的作用和轮转方式再给一套“备份完必须验证”的流程。3.1 三类日志的分工与开启方式很多人把日志混为一谈其实它们服务的目标完全不同。error log 记录启动、崩溃、复制中断这类事件是排查“为什么起不来”的第一现场slow log 记录执行超过阈值的语句是性能优化的入口binlog 记录所有数据变更是主从复制和时间点恢复的基础。三者的开启方式和影响面不一样-- 查看日志相关配置 SHOW VARIABLES LIKE log_error; SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE log_bin; SHOW VARIABLES LIKE binlog_format; -- 动态开启慢查询日志无需重启 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;逻辑说明log_error通常指向一个文件路径出问题时先看它。slow_query_log和long_query_time可以动态开启long_query_time 1表示超过 1 秒就记录实验环境可以设 0.5 秒更敏感。log_queries_not_using_indexes会把没走索引的语句也记下来排查全表扫描很有用但生产上如果这类语句多日志会涨得很快要配合轮转。log_bin是只读参数只能在配置文件里开binlog_format推荐ROW因为它是基于行变更的主从数据一致性更有保障。3.2 逻辑备份与物理备份的选型对比备份方式选错恢复时才发现问题就晚了。逻辑备份用mysqldump导出的是 SQL 文本跨版本、跨平台都能用但数据量大时慢且占空间物理备份直接拷数据文件快但和版本、存储引擎强绑定。选型看两个维度数据量和恢复时间要求。对比项逻辑备份mysqldump物理备份文件级备份速度慢逐行导出快直接拷贝恢复速度慢逐条重放快文件回位跨版本兼容好差需同版本占用空间较小可压缩较大适用场景中小库、迁移大库、快速恢复我一般建议实验训练阶段用mysqldump把流程跑通理解备份和恢复的完整链路数据量上到几十 GB 之后再考虑物理备份工具。下面是一套带验证的备份流程# 1. 逻辑备份单事务保证一致性记录 binlog 位置 mysqldump -u root -p \ --single-transaction \ --master-data2 \ --routines --triggers \ --databases biz_db /backup/biz_db_$(date %F).sql # 2. 压缩归档节省空间 gzip /backup/biz_db_$(date %F).sql # 3. 恢复到测试库验证关键步骤别跳过 mysql -u root -p -e CREATE DATABASE verify_db; gunzip /backup/biz_db_$(date %F).sql.gz | mysql -u root -p verify_db # 4. 对比行数确认恢复完整 mysql -u root -p -e SELECT COUNT(*) FROM verify_db.orders;逻辑说明--single-transaction在 InnoDB 上开启一致性快照备份期间不锁表--master-data2会把 binlog 位置以注释形式写进备份文件做时间点恢复时靠它定位起点--routines --triggers把存储过程和触发器一起导出否则恢复后业务逻辑会缺。第三步的恢复验证是最容易被跳过的一步但恰恰最重要——没验证过的备份等于没有备份。第四步对比行数确认数据完整。提示备份文件不要和数据库放在同一块磁盘上否则磁盘故障时备份一起没。实验环境可以放到另一个目录生产上要异地。3.3 基于 binlog 的时间点恢复演练有了全量备份还需要 binlog 才能恢复到“误操作前一秒”的状态。假设某次误删了一张表恢复思路是先恢复最近一次全量备份再用 binlog 重放备份之后到误操作之前的变更。# 1. 从备份文件中找到 binlog 起点--master-data2 写入的注释 grep CHANGE MASTER /backup/biz_db_2024-06-01.sql # 2. 查看当前有哪些 binlog 文件 mysql -u root -p -e SHOW BINARY LOGS; # 3. 用 mysqlbinlog 导出指定时间段的变更 mysqlbinlog --start-datetime2024-06-01 00:00:00 \ --stop-datetime2024-06-01 14:30:00 \ /var/lib/mysql/binlog.000003 /backup/incr.sql # 4. 先恢复全量再重放增量 mysql -u root -p /backup/biz_db_2024-06-01.sql mysql -u root -p /backup/incr.sql逻辑说明第一步从备份文件里提取 binlog 起点这是重放的起始位置。第三步的--start-datetime和--stop-datetime把范围卡在误操作之前stop-datetime就是“后悔药”的截止时间。第四步顺序不能反必须先全量后增量。这里有个血泪经验binlog_format如果是STATEMENT某些函数如NOW()重放结果可能不一致所以生产上坚持用ROW。4. 表空间、碎片与慢查询性能维护的三个抓手权限和备份是“不出事”的保障性能维护则是“跑得动”的保障。这一章聚焦三个最常被问到的点表空间怎么涨的、碎片怎么清、慢查询怎么定位到具体语句。4.1 表空间增长的排查路径磁盘告警十有八九是某张表或某个日志文件涨太快。排查顺序我一般是从大到小先看哪个库占空间再看哪张表最后看是数据还是索引。-- 按库统计占用空间 SELECT table_schema, ROUND(SUM(data_length index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables GROUP BY table_schema ORDER BY total_mb DESC; -- 按表统计区分数据和索引 SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND((data_length index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables WHERE table_schema biz_db ORDER BY total_mb DESC LIMIT 10;逻辑说明第一条按库汇总快速定位是哪个库在膨胀。第二条按表细分data_mb是数据占用index_mb是索引占用如果索引比数据还大说明索引建多了或者有冗余索引需要清理。这两个查询走的是information_schema在表特别多时会有一定开销但比直接du数据目录更准确因为它区分了数据和索引。4.2 碎片整理OPTIMIZE 什么时候该用、什么时候别用InnoDB 删除数据后空间不会立刻还给操作系统而是留在表空间里形成碎片。碎片多了同样的数据量占更多磁盘扫描也变慢。整理碎片用OPTIMIZE TABLE但它不是随便用的。-- 查看表的碎片情况data_free 就是碎片空间 SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(data_free / 1024 / 1024, 2) AS free_mb FROM information_schema.tables WHERE table_schema biz_db AND data_free 0 ORDER BY data_free DESC; -- 整理单张表 OPTIMIZE TABLE biz_db.orders;逻辑说明data_free大于 0 说明有可回收空间但注意这个值在共享表空间下可能不准。OPTIMIZE TABLE在 InnoDB 上实际是重建表会锁表MySQL 5.6 以后支持在线 DDL但仍有开销所以千万别在业务高峰期执行。我的习惯是碎片占比超过 30% 且业务低峰期才做做完立刻观察data_free是否归零。如果表特别大用pt-online-schema-change这类工具更稳妥但实验训练阶段先用原生命令理解原理。4.3 慢查询定位到语句与执行计划慢查询日志开了之后关键是会读。日志里每条记录包含执行时间、锁时间、扫描行数、返回行数重点看“扫描行数远大于返回行数”的语句那基本就是没走好索引。# 用 mysqldumpslow 汇总慢查询按出现次数排序 mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log # 按总耗时排序 mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log-- 对可疑语句看执行计划 EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND status paid; -- 看实际执行情况MySQL 8.0 EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 10086 AND status paid;逻辑说明mysqldumpslow -s c按出现次数排序找出高频慢查询-s t按总耗时排序找出最耗资源的。EXPLAIN重点看type列ALL是全表扫描ref/range较好、key列实际用的索引、rows列预估扫描行数。EXPLAIN ANALYZE会真正执行并给出实际耗时比EXPLAIN更准但注意它会执行语句别在写操作上用。优化方向通常是加联合索引注意联合索引的最左前缀原则。5. 避坑与排查维护操作里最容易翻车的五件事这一章是我这些年踩过的坑里挑出来最有代表性的五条每条按“现象 → 原因 → 解决”写都是实验训练里容易遇到、生产上也常犯的问题。5.1 改了权限没验证越权操作照样能跑现象给业务账号REVOKE了DROP权限结果测试时发现还是能删表。原因账号在user表里还留着全局的Drop_priv或者之前授过ALL PRIVILEGES没清干净库级REVOKE盖不住全局权限。解决先SHOW GRANTS FOR app_user10.0.%;看完整授权链再用REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...清空后重新按最小权限授最后一定要用新账号实际执行一次越权语句验证。5.2 备份文件没验证恢复时才发现是空的现象定期mysqldump跑得好好的真出事要恢复时发现备份文件只有几 KB或者恢复报错。原因备份脚本没检查退出码磁盘满了或者权限不对导致导出中断但脚本继续往下走。解决备份命令后加set -e或判断$?备份完立刻做一次恢复到临时库并对比行数把“验证”作为备份流程的强制环节而不是可选项。5.3 OPTIMIZE TABLE 在高峰期执行导致锁表现象业务反馈某段时间下单全部超时排查发现是有人对大表执行了OPTIMIZE TABLE。原因InnoDB 的OPTIMIZE本质是重建表即使有在线 DDL大表重建期间仍会占用大量 IO 和短暂锁。解决碎片整理放到业务低峰期大表用在线工具执行前先确认data_free占比是否真的值得做别为了几个 MB 碎片去锁一张千万行的表。5.4 wait_timeout 调太短连接池频繁重连现象把wait_timeout从 8 小时调到 60 秒后应用日志里出现大量“连接已断开”重连。原因应用连接池的空闲连接被服务端主动断开但连接池不知道拿到的还是失效连接。解决wait_timeout要大于连接池的maxIdleTime让连接池先回收服务端后断。一般设 600 到 1800 秒同时检查连接池配置里的validationQuery是否开启。5.5 binlog 没设过期时间磁盘被悄悄吃满现象磁盘使用率缓慢上升找不到大表最后发现是 binlog 目录几十 GB。原因expire_logs_days默认值偏大或没设binlog 一直累积。解决设置binlog_expire_logs_secondsMySQL 8.0或expire_logs_days一般保留 7 到 14 天同时确认从库已经同步完再清理。清理用PURGE BINARY LOGS BEFORE ...别直接rm文件否则索引文件对不上会出问题。6. 把维护做成例行检查一份可落地的巡检清单前面讲的都是单点操作但数据库维护真正的价值在于“例行化”——把零散命令变成每天、每周、每月固定跑的检查项问题在变成故障之前就被发现。这一章给一份我自己在用的巡检清单以及怎么把它脚本化。先说清单本身。每日检查四项连接数是否接近上限、error log 有没有新报错、磁盘使用率、主从延迟如果有从库。每周检查三项慢查询 Top 10、表空间增长趋势、备份是否成功且验证通过。每月检查两项权限复核有没有多余账号和权限、碎片情况。这份清单不复杂难的是坚持。脚本化时我一般用一个 bash 脚本串起来输出成一份简短报告#!/bin/bash # 数据库日常巡检脚本示例 MYSQLmysql -u monitor -pxxx -N -B echo 连接数 $MYSQL -e SHOW STATUS LIKE Threads_connected; echo 磁盘使用 df -h /var/lib/mysql | tail -1 echo 最近错误日志 tail -20 /var/log/mysql/error.log echo 备份文件最新时间 ls -lt /backup/*.sql.gz | head -3 echo 碎片 Top 5 $MYSQL -e SELECT table_name, ROUND(data_free/1024/1024,2) AS free_mb FROM information_schema.tables WHERE table_schemabiz_db AND data_free0 ORDER BY data_free DESC LIMIT 5;逻辑说明-N -B去掉表头和边框输出更适合脚本解析。脚本里每一项对应清单里的一个检查点跑完把输出发到监控或邮件。参数上监控账号只给PROCESS和SELECT权限即可别用 root。这个脚本可以挂到 crontab 每天早上一跑五分钟看完比出事后再翻日志高效得多。最后说一个我自己的习惯每次做完任何维护操作——不管是改权限、调参数还是清碎片——都在一个维护记录文件里写一行“时间 操作 原因 验证结果”。这个习惯救过我好几次因为很多问题不是当场暴露的是几天后业务反馈异常翻记录才知道之前动过什么。数据库维护没有一劳永逸靠的是把每一次操作都留下痕迹把每一次故障都变成清单里的一条。希望帮到你。本文还有配套的精品资源点击获取