04_MySQL DBA 日常运维手册 —— 会话管理、锁与阻塞、事务监控、慢查询与日志巡检

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

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

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;

五、慢查询日志（官方：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

— 解释：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;

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

八、磁盘与空间巡检

# 解释：数据目录磁盘使用率
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 虚机运维注意事项

十、踩坑记录（实测汇总）

十一、要点速记

十二、参考资料（官方正式文档）

本文章属于《MySQL 数据库全栈运维实战系列》日常运维篇。欢迎收藏、交流。