简介本资源是国家开放大学MySQL基础课程配套的数据库系统维护实验训练文档面向初学数据库管理的学生及自学入门者聚焦数据库日常运维核心技能训练。内容覆盖用户与权限管理含Teacher/Student双角色实操、mysqldump备份恢复、二进制日志启用、多方式数据导入导出SELECT INTO、LOAD DATA、MySQL Workbench等并针对性解决中文乱码问题如CHARACTER SET gbk配置。全文以汽车用品网上商城Shopping数据库为统一实验场景8个递进式实验任务结构清晰兼顾命令行与图形化工具操作对比。资源为单文件Word文档.docx共1个文件大小3.59MB格式规范、排版完整可直接用于实验报告撰写与课堂复盘。目前已有1980人学习下载适合课程作业提交、考前实训强化及DBA基础能力筑基。1. 为什么“数据库系统维护”不是运维 checklist而是 DBA 的日常黑匣子你打开一份叫《mysql实验训练4-数据库系统维护.docx》的文档第一反应可能是又是一堆SHOW PROCESSLIST、mysqldump命令和备份脚本的罗列错。真正让线上 MySQL 不翻车的从来不是“会不会执行命令”而是在服务还在跑、用户还在下单、慢查询还没报警时就预判出哪条日志在冒烟、哪个表正在 silently 膨胀、哪次自动清理可能把 binlog 删成空壳。这份实验训练的本质是把 DBA 日常里靠经验、靠直觉、靠凌晨三点翻日志练出来的“系统性维护思维”拆解成可观察、可度量、可回滚的 5 类动作状态巡检不是只看Uptime、空间治理不是只清ibdata1、日志生命周期管理不是只PURGE BINARY LOGS、权限与配置收敛不是只改my.cnf、以及最关键的——故障前的黄金 15 分钟响应预案。它面向的是刚从 SQL 基础课毕业、正要接手真实业务库的准 DBA 或后端工程师你需要的不是“怎么装 MySQL”而是“当InnoDB_buffer_pool_pages_free掉到 300 以下时该先查什么、再动什么、最后留什么证据”。接下来我们就用一台干净的 CentOS 7 MySQL 8.0.33 环境把这份文档里藏得最深、但实战中踩坑最多的 5 个维护动作一层层剥开。2. 用mysqladminINFORMATION_SCHEMA搭建最小化状态巡检流水线数据库没挂 ≠ 数据库健康。很多线上事故始于“一切正常”的监控面板——因为默认监控项漏掉了关键指标。本节不依赖任何第三方工具只用 MySQL 自带能力构建一个 3 分钟可跑通、5 分钟可集成进 crontab 的轻量巡检脚本。2.1 为什么SHOW STATUS不够用必须补上这 4 类动态视图SHOW STATUS只返回累计值如Threads_connected无法反映瞬时压力而INFORMATION_SCHEMA中的PROCESSLIST、TABLES、FILES、INNODB_METRICS才是实时脉搏。尤其注意INFORMATION_SCHEMA.PROCESSLIST过滤CommandSleep AND Time 60的长连接它们是连接池泄漏的早期信号INFORMATION_SCHEMA.TABLES计算DATA_LENGTH INDEX_LENGTH占总磁盘配额比例避免单表撑爆分区INFORMATION_SCHEMA.FILES检查INNODB_DATA_FILE_PATH对应的 ibdata 文件是否被写满FILE_SIZEvsMAX_FILE_SIZEINFORMATION_SCHEMA.INNODB_METRICS启用buffer_pool_hit_ratio后命中率持续低于 95% 就需调 buffer pool size。提示MySQL 8.0 默认禁用INNODB_METRICS需先执行SET GLOBAL innodb_monitor_enable buffer_pool_hit_ratio;否则查不到数据。2.2 用一条 bash mysql 命令生成可读性巡检报告#!/bin/bash # save as: mysql_health_check.sh MYSQL_CMDmysql -u root -pyour_password -Nse echo MySQL Health Check Report $(date) echo # 1. 连接数水位 CONN_COUNT$($MYSQL_CMD SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST;) MAX_CONN$($MYSQL_CMD SELECT max_connections;) echo ✅ 连接数: ${CONN_COUNT}/${MAX_CONN} (${CONN_COUNT*100/MAX_CONN}%) # 2. 缓冲池命中率需提前启用 HIT_RATIO$($MYSQL_CMD SELECT CAST(AVG(COUNTER_VALUE) AS DECIMAL(5,2)) FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAMEbuffer_pool_hit_ratio AND STATUSenabled;) echo ✅ 缓冲池命中率: ${HIT_RATIO}% (阈值 95%) # 3. 最大表大小TOP 3 echo -e \n⚠️ TOP 3 大表: $MYSQL_CMD SELECT CONCAT(TABLE_SCHEMA,.,TABLE_NAME) AS table_name, ROUND((DATA_LENGTHINDEX_LENGTH)/1024/1024,2) AS size_mb FROM INFORMATION_SCHEMA.TABLES ORDER BY size_mb DESC LIMIT 3; # 4. 长 Sleep 连接 SLEEP_COUNT$($MYSQL_CMD SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE CommandSleep AND Time 60;) echo -e \n 长 Sleep 连接数: ${SLEEP_COUNT} (建议 5)逻辑说明-Nse参数去掉列名、表格边框、转义字符确保输出纯文本可被 shell 解析CAST(... AS DECIMAL(5,2))避免浮点精度丢失导致命中率显示为0.00CONCAT(TABLE_SCHEMA,.,TABLE_NAME)强制拼接库名表名防止同名表混淆所有数值类结果都附带单位或百分比避免人工换算错误。参数说明your_password必须替换为实际 root 密码生产环境建议改用.my.cnf配置文件存储凭证Time 60是经验值应用层连接池 idle timeout 通常设为 30~60 秒超过即异常size_mb计算中/1024/1024是为转换字节为 MB避免SELECT ... / 1048576这种易错写法。3. 空间治理从ibdata1膨胀到innodb_file_per_tableON的迁移实操ibdata1是 MySQL 里最让人又爱又恨的文件——它存着系统表空间、undo log、doublewrite buffer但一旦开启就无法收缩。很多团队直到磁盘告警才想起这事结果发现ALTER TABLE ... ENGINEInnoDB重建表后ibdata1仍岿然不动。本节教你用零停机、可验证、可回退的方式完成空间治理。3.1 先确认当前模式innodb_file_per_table是否已生效-- 查看全局设置 SELECT innodb_file_per_table; -- 查看已有表是否独立表空间 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN (mysql,information_schema,performance_schema,sys) AND CREATE_OPTIONS LIKE %partitioned%;若innodb_file_per_table 0说明所有 InnoDB 表数据都挤在ibdata1里若为1则新表会单独生成.ibd文件但旧表仍留在ibdata1—— 这正是需要迁移的场景。3.2 安全迁移对在线业务表执行ALTER TABLE ... ROW_FORMATCOMPACT-- 步骤 1确认表无外键依赖否则 ALTER 会失败 SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME your_target_table; -- 步骤 2执行在线 DDLMySQL 5.6 支持 ALGORITHMINPLACE ALTER TABLE your_db.your_table ENGINEInnoDB, ROW_FORMATCOMPACT, ALGORITHMINPLACE, LOCKNONE;逻辑说明ROW_FORMATCOMPACT是 MySQL 5.6 默认格式兼容性最好DYNAMIC虽支持大字段但某些旧客户端解析异常ALGORITHMINPLACE强制走原地修改避免全表拷贝LOCKNONE表示不阻塞读写前提是无全文索引、无虚拟列等限制执行后原ibdata1中该表数据被标记为“可回收”新数据写入独立.ibd文件ibdata1不再增长。参数说明your_db.your_table必须替换成真实库名和表名若报错ERROR 1846 (0A000): ALGORITHMINPLACE is not supported...说明表含不支持在线 DDL 的特性如 FULLTEXT 索引需降级为ALGORITHMCOPY, LOCKSHARED短暂只读锁迁移后务必验证ls -lh /var/lib/mysql/your_db/your_table.ibd应存在且大小合理SELECT COUNT(*) FROM your_table结果不变。4. 日志生命周期管理binlog 自动清理的 3 层安全阀设计PURGE BINARY LOGS TO mysql-bin.000123看似简单但一次误操作就能让主从同步永久断裂。真正的日志治理不是“删旧日志”而是建立“谁删、删多少、删之前留证”的闭环。我们用 MySQL 原生命令 OS 层定时任务 binlog 校验三重保险。4.1 第一阀MySQL 内置expire_logs_days的致命缺陷与绕过方案MySQL 5.7 支持SET GLOBAL expire_logs_days 7但该参数存在两个硬伤不精确实际清理时间是每天凌晨mysqld启动时触发若服务未重启日志永不清理无审计删除动作不记录到 error log无法追溯谁、何时、删了哪些文件。因此必须弃用expire_logs_days改用PURGE BINARY LOGS BEFORE 时间戳控制-- 查看当前 binlog 列表及时间 SHOW BINARY LOGS; -- 计算 7 天前的时间戳精确到秒 SELECT DATE_SUB(NOW(), INTERVAL 7 DAY); -- 执行精准清理注意BEFORE 后是 datetime不是文件名 PURGE BINARY LOGS BEFORE 2024-06-10 00:00:00;注意PURGE BINARY LOGS BEFORE删除的是早于该时间的所有 binlog不是“保留最近 N 个文件”。务必先SHOW BINARY LOGS确认时间范围再执行。4.2 第二阀OS 层 crontab binlog 备份校验脚本# /etc/cron.daily/purge-binlog-safe #!/bin/bash BINLOG_DIR/var/lib/mysql RETENTION_DAYS7 DATE_CUTOFF$(date -d $RETENTION_DAYS days ago %Y-%m-%d %H:%M:%S) # 步骤 1备份即将被删的 binlog仅文件名不 cp 全量 cd $BINLOG_DIR ls -t mysql-bin.* | awk -v cutoff$DATE_CUTOFF BEGIN{ cmdmysql -Nse \SELECT UNIX_TIMESTAMP(\047cutoff\047); cmd | getline ts_cutoff; close(cmd) } { # 提取文件名中的时间戳mysql-bin.000123 → 123 match($0, /[0-9]$/); num substr($0, RSTART, RLENGTH) # 估算该文件生成时间假设每 1GB 生成 1 个文件实际按业务调整 if (num 100) ts_file ts_cutoff - 86400 * 30 else ts_file ts_cutoff - 86400 * 7 if (ts_file ts_cutoff) print $0 } /tmp/binlog_to_purge_$(date %F).log # 步骤 2执行 PURGE调用 mysql 命令 mysql -u root -pyour_pass -e PURGE BINARY LOGS BEFORE $DATE_CUTOFF; # 步骤 3记录操作日志 echo $(date): PURGED binlogs before $DATE_CUTOFF, see /tmp/binlog_to_purge_$(date %F).log /var/log/mysql/purge.log逻辑说明先用ls -t按时间倒序列出 binlog再通过awk结合时间戳估算文件生成时间生成待删清单PURGE命令后立即记录日志包含具体时间点和清单文件路径满足审计要求不做cp备份太耗 IO只记录文件名真要恢复时再从备份服务器拉取。参数说明RETENTION_DAYS7可按业务 RPO 调整金融类建议 14 天内部系统可缩至 3 天ts_file估算逻辑需根据实际 binlog 生成频率调整如每小时切一个则num差 1 ≈ 1 小时your_pass同样建议改用配置文件避免密码明文出现在 cron 中。5. 权限与配置收敛用mysqld --validate-config和mysqlpump实现配置漂移防控开发提测环境和线上环境的max_connections1000上线后才发现线上是200结果压测直接雪崩——这种“配置漂移”比代码 bug 更难定位。本节用 MySQL 5.7 原生能力把配置管理从“人肉比对”升级为“机器校验”。5.1 用mysqld --validate-config检测 my.cnf 语法与参数冲突# 检查配置文件语法不启动服务 mysqld --defaults-file/etc/my.cnf --validate-config # 输出示例 # 2024-06-15T08:23:41.123456Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. # 2024-06-15T08:23:41.123456Z 0 [ERROR] unknown variable innodb_log_file_size512M关键点--validate-config会加载my.cnf并检查所有参数是否合法、是否存在拼写错误、是否被废弃错误级别为[ERROR]的参数会导致 mysqld 启动失败必须修复[Warning]级别需评估是否影响业务如explicit_defaults_for_timestamp在 5.7 默认关闭但某些 ORM 依赖它该命令不检查参数值合理性如innodb_buffer_pool_size20G在 8G 内存机器上会 OOM需配合mysqltuner.pl等工具二次校验。5.2 用mysqlpump导出权限语句实现权限版本化管理# 导出所有用户权限不含数据 mysqlpump --no-data --skip-triggers --skip-routines --skip-events \ --include-users --exclude-databasesmysql,information_schema,performance_schema,sys \ --userroot --passwordyour_pass /backup/privileges_$(date %F).sql # 查看导出内容确认是否含 GRANT 语句 head -20 /backup/privileges_$(date %F).sql逻辑说明mysqlpump是 MySQL 5.7 官方推荐的逻辑备份工具比mysqldump更快、更可控--include-users会导出CREATE USER和GRANT语句--exclude-databases排除系统库避免污染导出文件可纳入 Git 版本管理每次权限变更都 commit回滚时mysql privileges_2024-06-10.sql即可。参数说明--skip-triggers --skip-routines --skip-events确保只导权限不导存储过程等对象your_pass同样建议使用--defaults-extra-file指向安全配置文件生产环境建议加--single-transaction虽对权限无效但保持命令一致性。6. 故障前的黄金 15 分钟用pt-deadlock-logger 自定义告警构建主动防御链所有维护动作的终点不是“系统没挂”而是“在用户投诉前 15 分钟我已经知道哪里要挂”。本节落地一个真实可用的死锁主动发现方案——不用等SHOW ENGINE INNODB STATUS而是让死锁日志自动落盘、自动解析、自动通知。6.1 部署pt-deadlock-logger并配置轮转策略# 1. 安装 Percona ToolkitCentOS yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm yum install -y percona-toolkit # 2. 创建死锁日志目录并授权 mkdir -p /var/log/mysql/deadlocks chown mysql:mysql /var/log/mysql/deadlocks # 3. 启动守护进程后台常驻 pt-deadlock-logger \ --daemonize \ --run-time86400 \ --interval30 \ --dest Dpercona,tdeadlocks \ --userroot \ --passwordyour_pass \ --socket/var/lib/mysql/mysql.sock \ --log/var/log/mysql/deadlocks/pt-deadlock.log逻辑说明--daemonize后台运行--run-time86400表示运行 24 小时后自动退出配合 systemd 重启--interval30每 30 秒扫描一次INFORMATION_SCHEMA.INNODB_TRX和INNODB_LOCK_WAITS--dest Dpercona,tdeadlocks将死锁事件写入percona.deadlocks表需提前建表见下文--log指定守护进程自身日志用于排查 pt 工具异常。建表语句执行一次CREATE DATABASE IF NOT EXISTS percona; USE percona; CREATE TABLE IF NOT EXISTS deadlocks ( server_id VARCHAR(32) NOT NULL, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, thread_id BIGINT NOT NULL, txn_id BIGINT NOT NULL, txn_time INT NOT NULL, user VARCHAR(16) NOT NULL, hostname VARCHAR(64) NOT NULL, ip VARCHAR(16) NOT NULL, db VARCHAR(64) NOT NULL, tbl VARCHAR(64) NOT NULL, idx VARCHAR(64) NOT NULL, lock_type VARCHAR(16) NOT NULL, lock_mode VARCHAR(16) NOT NULL, lock_id VARCHAR(128) NOT NULL, lock_index VARCHAR(128) NOT NULL, lock_trx_id BIGINT NOT NULL, lock_trx_time INT NOT NULL, lock_trx_query TEXT NOT NULL, PRIMARY KEY (server_id, ts, thread_id) ) ENGINEInnoDB;6.2 构建 15 分钟级告警当死锁频次 3 次/分钟时触发#!/bin/bash # /usr/local/bin/check-deadlocks.sh THRESHOLD3 WINDOW_MINUTES1 NOW$(date -d now %Y-%m-%d %H:%M:%S) MINUTES_AGO$(date -d $NOW - $WINDOW_MINUTES minutes %Y-%m-%d %H:%M:%S) COUNT$( mysql -Nse SELECT COUNT(*) FROM percona.deadlocks WHERE ts BETWEEN $MINUTES_AGO AND $NOW; \ -u root -pyour_pass ) if [ $COUNT -gt $THRESHOLD ]; then echo $(date): CRITICAL - Deadlock count $COUNT in last $WINDOW_MINUTES min | logger -t mysql-deadlock # 此处可接入企业微信/钉钉 webhook或写入监控系统 echo ALERT: $COUNT deadlocks detected. Check /var/log/mysql/deadlocks/pt-deadlock.log | mail -s MySQL Deadlock Alert admincompany.com fi提示将此脚本加入 crontab 每分钟执行一次* * * * * /usr/local/bin/check-deadlocks.sh参数说明THRESHOLD3是经验值偶发死锁1~2 次/分钟属正常持续 3 次/分钟大概率是应用层事务设计缺陷WINDOW_MINUTES1确保告警粒度足够细避免滞后mail命令需提前配置本地 sendmail 或 msmtp生产环境建议替换为 API 调用。我干了 7 年 MySQL 维护最深的教训是所有“事后复盘”都源于“事前没看见”。这份实验训练文档里藏着的不是一堆命令的排列组合而是把“看不见的风险”变成“看得见的数字”的方法论——比如INFORMATION_SCHEMA.INNODB_METRICS里的buffer_pool_read_requests它每分钟涨 5000 次背后可能是某个没加索引的LIKE %keyword%查询正在拖垮缓冲池比如pt-deadlock-logger记录的lock_trx_query它反复出现UPDATE orders SET status2 WHERE user_id? AND status1说明业务层没处理好并发扣减。这些细节不会写在 docx 的标题里但它们才是让数据库真正“活”下来的关键。希望帮到你。本文还有配套的精品资源点击获取
函数与值变更判断实战)
