04_MySQL DBA 日常运维手册 —— 会话管理、锁与阻塞、事务监控、慢查询与日志巡检
系列定位：《MySQL 数据库全栈运维实战系列》第 04 篇，与《Oracle 数据库全栈运维实战系列（VMware 版）》同构对标。适用版本：MySQL 8.0（5.7 差异随文标注）。官方文档：MySQL 8.0 Reference Manual、Server Status Variables / InnoDB Locking / The Slow Query Log。
一、日常运维全景（DBA 每日清单）
图 1
图 2
| 列 | 说明 | 类比 Oracle |
|---|---|---|
| Id | 连接线程 ID | sid/serial# |
| User | 用户名 | username |
| Host | 客户端 IP:端口 | machine |
| db | 当前数据库 | schema |
| Command | Sleep/Query/Connect 等 | status |
| Time | 当前状态持续秒数 | seconds_in_wait |
| State | 详细状态 | event |
| Info | 正在执行的 SQL | sql_text |
2.2 连接数监控与处理
-- 解释：当前连接数 / 最大连接数
SHOW VARIABLES LIKE 'max_connections'; -- 上限
SHOW STATUS LIKE 'Threads_connected'; -- 当前连接数
SHOW STATUS LIKE 'Threads_running'; -- 活跃（正在执行）线程数
-- 解释：杀掉空闲连接（Sleep 状态超过 N 秒）
-- 注意：KILL 是异步的，不会立即终止
KILL 12345; -- 解释：按 Id 杀连接
-- 批量杀 Sleep 连接（生成 SQL）
SELECT CONCAT('KILL ', id, ';') AS kill_sql
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 600; -- 空闲超过 10 分钟
生产建议：-
wait_timeout=28800（8 小时，默认）→ 建议改为 600（10 分钟），配合应用侧连接池。- 连接数打满报ERROR 1040 (08004): Too many connections：先查应用侧连接池配置，再考虑调大 max_connections。
三、锁与阻塞排查（官方：InnoDB Locking）
图 3
3.1 锁类型体系
图 4
| 锁类型 | 说明 | 类比 Oracle |
|---|---|---|
| Record Lock | 锁定单行记录 | row lock |
| Gap Lock | 锁定索引间隙，防止其他事务插入 | 间隙锁 |
| Next-Key Lock | Record + Gap，InnoDB RR 隔离级别默认 | 临键锁 |
| 意向锁 | 表级，标识本事务要加什么行锁 | TM 锁 |
| AUTO-INC Lock | 自增 ID 分配时的表级锁 | sequence lock |
3.2 排查阻塞链
-- 解释：8.0 查看当前所有锁（performance_schema.data_locks）
SELECT * FROM performance_schema.data_locks\G
-- 解释：查看锁等待关系（谁在等谁）
SELECT * FROM performance_schema.data_lock_waits\G
-- 解释：综合视图：谁阻塞了谁（sys schema 封装）
SELECT * FROM sys.innodb_lock_waits\G
-- 解释：查看当前活跃事务
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
trx_query, trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;
图 5
3.3 处理阻塞
-- 解释：找到阻塞源头（trx_mysql_thread_id 就是 SHOW PROCESSLIST 的 Id）
-- 在 innodb_lock_waits 中：
-- requesting_engine_lock_id = 等待方
-- blocking_engine_lock_id = 阻塞方
-- 解释：杀掉阻塞源头（谨慎！先确认业务影响）
KILL <blocking_thread_id>;
-- 解释：锁等待超时设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 默认 50 秒
SET GLOBAL innodb_lock_wait_timeout = 10; -- 生产建议 10 秒快速失败
5.7 差异：5.7 用
information_schema.INNODB_LOCKS和INNODB_LOCK_WAITS视图；8.0 迁移到performance_schema.data_locks和data_lock_waits，信息更详细。
四、事务监控
-- 解释：当前所有事务
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_sec,
trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
-- 解释：查找长事务（运行超过 60 秒）
SELECT trx_id, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
trx_query, trx_mysql_thread_id
FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60
ORDER BY trx_started;
-- 解释：未提交事务（导致 undo 膨胀 + 锁不释放）
SELECT * FROM information_schema.innodb_trx
WHERE trx_state = 'RUNNING' AND trx_rows_modified > 0;
| 事务异常 | 危害 | 处理 |
|---|---|---|
| 长事务（>5 分钟） | undo 膨胀、锁不释放、purge 滞后 | 定位应用，必要时 KILL |
| 未提交事务 | 连接 Sleep 但事务没 commit | 检查应用是否忘记 commit |
| 大事务 | 主从延迟大、binlog 体积大 | 拆小事务，分批提交 |
五、慢查询日志（官方：The Slow Query Log）
5.1 配置
# my.cnf 慢查询配置
slow_query_log = ON
slow_query_log_file = /data/mysql/slow.log
long_query_time = 1 # 超过 1 秒记录
log_queries_not_using_indexes = ON # 没走索引的也记录
log_slow_admin_statements = ON # 管理语句（ALTER/CREATE）也记录
min_examined_row_limit = 100 # 扫描行数低于 100 不记录
5.2 分析慢查询
# 解释：mysqldumpslow 是 MySQL 自带的慢查询分析工具
# -s t 按总时间排序，-s c 按次数排序
# -t 10 取 Top 10
mysqldumpslow -s t -t 10 /data/mysql/slow.log
# 解释：更强大的工具 pt-query-digest（Percona Toolkit）
pt-query-digest /data/mysql/slow.log | less
| 工具 | 说明 |
|---|---|
| mysqldumpslow | MySQL 自带，基础统计 |
| pt-query-digest | Percona Toolkit，深度分析，生产推荐 |
| explain | 对单条慢 SQL 看执行计划 |
-- 解释：performance_schema 也可以看慢 SQL 汇总（无需读日志）
SELECT DIGEST_TEXT, COUNT_STAR,
ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_sec,
ROUND(AVG_TIMER_WAIT/1000000000000, 2) AS avg_sec,
SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10\G
六、错误日志与常规日志
6.1 错误日志
-- 解释：查看错误日志路径
SHOW VARIABLES LIKE 'log_error';
-- 解释：查看最近的错误（8.0 支持从 error log 读）
SELECT * FROM performance_schema.error_log
WHERE SUPPRESSED = ''
ORDER by EVENT_TIMESTAMP DESC
LIMIT 20;
| 常见错误 | 说明 | 处理 |
|---|---|---|
| Can't connect to server | 连接被拒 | 检查端口/防火墙/连接数 |
| Too many connections | 连接数打满 | 调大 max_connections 或排查泄漏 |
| Deadlock found | 死锁 | 查看死锁日志，优化事务顺序 |
| Table doesn't exist | 表不存在 | 检查库名/表名拼写 |
| Got error 28 from storage engine | 磁盘满 | 清理磁盘空间 |
6.2 死锁分析
-- 解释：查看最近一次死锁详情
SHOW ENGINE INNODB STATUS\G
-- 重点看 LATEST DETECTED DEADLOCK 段
-- 解释：开启死锁日志记录到 error log
SET GLOBAL innodb_print_all_deadlocks = ON;
图 6
七、备份检查（日常必做）
# 解释：检查昨日备份文件是否存在、大小是否正常
ls -lh /backup/mysql/
# 期望：备份文件大小 > 0，且与库大小比例合理
# 解释：检查备份日志是否有报错
tail -50 /backup/mysql/backup.log
-- 解释：检查 binlog 是否正常生成（备份依赖 binlog 做增量）
SHOW BINARY LOGS;
-- 解释：检查主从复制状态（如果有从库）
SHOW REPLICA STATUS\G -- 8.0 用 REPLICA，5.7 用 SLAVE
| 检查项 | 正常标准 |
|---|---|
| 备份文件存在 | 每日备份文件生成且大小 > 0 |
| 备份日志 | 无 ERROR/WARNING |
| binlog 连续 | SHOW BINARY LOGS 无 gaps |
| 从库延迟 | Seconds_Behind_Source < 60 秒 |
八、磁盘与空间巡检
# 解释：数据目录磁盘使用率
df -h /data
# 解释：binlog 目录大小
du -sh /data/mysql/binlog/
# 解释：error log / slow log 大小
du -sh /data/mysql/error.log /data/mysql/slow.log
-- 解释：查看各库大小排行
SELECT TABLE_SCHEMA,
ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024, 2) AS total_gb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY total_gb DESC;
-- 解释：查看最大的 10 张表
SELECT TABLE_SCHEMA, TABLE_NAME,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024, 2) AS total_gb
FROM information_schema.TABLES
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC
LIMIT 10;
九、VMware 虚机运维注意事项
| 项 | 建议 | 原因 |
|---|---|---|
| 快照管理 | 不要在业务运行时频繁快照，快照链不宜超过 2 层 | 快照会导致磁盘 IO 性能急剧下降 |
| 时间同步 | VMware Tools 时间同步保持开启 | 慢查询日志时间戳、binlog 位点依赖准确时间 |
| 磁盘空间监控 | 预留 20% 以上空闲空间 | 快照 delta 会持续增长，磁盘满导致 MySQL crash |
| 内存膨胀 | 监控 VMware 内存膨胀驱动（ballooning） | 宿主机内存紧张时 ballooning 会饿死虚机 |
| 备份方式 | 不要用 VMware 快照替代 MySQL 备份 | 快照不是数据库一致性备份，恢复后可能需 crash recovery |
十、踩坑记录（实测汇总）
| # | 坑 | 现象 | 解决 |
|---|---|---|---|
| 1 | 连接数打满 | 应用报 Too many connections | 排查应用连接池泄漏，调大 max_connections |
| 2 | 长事务未提交 | undo 膨胀、锁不释放 | 查 innodb_trx，KILL 长事务 |
| 3 | 死锁未监控 | 业务偶发 ERROR 1213 | 开 innodb_print_all_deadlocks，定期分析 |
| 4 | 慢查询日志没开 | 不知道哪条 SQL 慢 | 开启 slow_query_log，设 long_query_time=1 |
| 5 | binlog 占满磁盘 | 磁盘满 MySQL crash | 调小 expire_logs_days，定期 PURGE BINARY LOGS |
| 6 | error log 路径不对 | 找不到错误信息 | 确认 log_error 参数，定期轮转日志 |
| 7 | KILL 连接不生效 | KILL 后连接还在 | KILL 是异步的，大事务需要等锁释放 |
| 8 | 从库延迟大 | 主库更新从库看不到 | 查Seconds_Behind_Source，优化大事务 |
| 9 | VMware 快照没删 | 磁盘越来越大、IO 变慢 | 做完验证后立即删除快照 |
| 10 | time_zone 未设置 | 时间函数返回 UTC | my.cnf 设置 default-time-zone='+8:00' |
| 11 | performance_schema 没开 | 锁/事务查不了 | 8.0 默认开启，确认 performance_schema=ON |
| 12 | 备份没验证 | 真恢复时才发现备份损坏 | 定期做恢复演练，不能只看备份成功 |
十一、要点速记
- 日常巡检三板斧：processlist 看连接、innodb_trx 看事务、data_locks 看锁。
- 慢查询是性能优化入口：slow.log + pt-query-digest 是标配。
- 长事务是万恶之源：undo 膨胀、锁不释放、主从延迟都跟它有关。
- 备份不能只看成功：必须定期恢复演练，备份≠可恢复。
- VMware 快照 ≠ 数据库备份：快照只是虚机层面，数据库一致性仍需 mysqldump/xtrabackup。
十二、参考资料（官方正式文档）
| 主题 | 官方链接 |
|---|---|
| MySQL 8.0 Reference Manual | https://dev.mysql.com/doc/refman/8.0/en/ |
| InnoDB Locking（锁机制） | https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html |
| InnoDB Transaction Model | https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-model.html |
| The Slow Query Log | https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html |
| The Error Log | https://dev.mysql.com/doc/refman/8.0/en/error-log.html |
| SHOW PROCESSLIST | https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html |
| Performance Schema Lock Tables | https://dev.mysql.com/doc/refman/8.0/en/performance-schema-lock-tables.html |
| sys Schema（innodb_lock_waits） | https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html |
| Percona Toolkit (pt-query-digest) | https://www.percona.com/software/database-tools/percona-toolkit |
本文章属于《MySQL 数据库全栈运维实战系列》日常运维篇。欢迎收藏、交流。
转载自 CSDN-专业IT技术社区
原文链接：https://blog.csdn.net/dk843108982/article/details/169437991
