📖 本篇简介：本篇是《MySQL 从入门到精通》踩坑系列的收官篇，整理 DBA 与后端工程师每天都会碰到的日常运维 12 个坑。前置知识建议已读完第 24 章性能监控与参数调优、第 15 章用户权限管理。学完本篇你将能提前发现磁盘告警、连接泄漏、权限过大、定时任务失效、版本升级翻车等隐患。本示例基于 MySQL 8.0，所有 SQL 按 8.0 语法书写。
目录
- 坑 1：磁盘被 binlog 写满导致数据库挂死
- 坑 2：连接数打满与连接泄漏
- 坑 3：root 密码弱且公网暴露
- 坑 4：event 事件调度器未开启
- 坑 5：跨大版本直接升级报错
- 坑 6：慢查询日志长期未开启
- 坑 7：服务器时区不同步导致时间错乱
- 坑 8：在线大 DDL 直接锁表
- 坑 9：脚本里明文写数据库密码
- 坑 10：max_allowed_packet 过小丢大包
- 坑 11：系统时区与库时区不一致
- 坑 12：无监控告警故障半夜才发现
- 本篇速查表
- 运维注意事项
- 动手练习
坑 1：磁盘被 binlog 写满导致数据库挂死
场景 / 错误示范：
业务量上涨，binlog 一天涨到 50G，没人清理，某天磁盘 100% 满，数据库写入全部失败。
-- 错误：从不清理 binlog，也没设过期
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
-- 返回 2592000（30 天），但磁盘根本撑不住 30 天
# 磁盘查看
df -h
# /var/lib/mysql 100% 使用率
为什么是坑：binlog 是追加写，只增不减，不设过期或定期清理，迟早把磁盘吃满。磁盘满之后 InnoDB 无法写 redo、无法写临时表，整个实例挂死，且重启都可能失败。
正确做法：
-- 正确 1：设置自动过期（按需调小，保留 3~7 天即可）
SET GLOBAL binlog_expire_logs_seconds = 604800; -- 1 天
-- 正确 2：手动清理已备份的 binlog
PURGE BINARY LOGS BEFORE '2026-10-01 00:00:00';
PURGE BINARY LOGS TO 'mysql-bin.000100'; -- 清理到指定文件前
-- 正确 3：监控磁盘，>80% 告警
-- my.cnf 固化
[mysqld]
binlog_expire_logs_seconds = 604800
加分点：从库还在需要的 binlog 千万别 PURGE，先确认所有从库已经拉完；用 SHOW REPLICA STATUS 看各从库当前位点再清。
坑 2：连接数打满与连接泄漏
场景 / 错误示范：
应用发布后没配连接池，每个请求新建一个 MySQL 连接，很快 Too many connections。
-- 错误：连接池不配，长连接泄漏
SHOW STATUS LIKE 'Threads_connected';
-- 连接数飙到 max_connections = 151，新连接全被拒
# 错误：每次请求新建连接，用完不关闭
import pymysql # 需 pip install pymysql，本环境仅作示范无服务器可连
def query_bad():
conn = pymysql.connect(host='db', user='app', password='xxx', database='appdb')
cur = conn.cursor()
cur.execute("SELECT 1")
# 忘了 conn.close()，连接堆积
为什么是坑：MySQL 每连接一个线程，max_connections 默认 151。应用侧不配连接池、不归还连接，连接数很快打满，新业务连不上。泄漏的连接还占着内存和行锁。
正确做法：
-- 正确 1：合理调大连接数（按机器内存，1 连接约 1MB+）
SET GLOBAL max_connections = 1000;
-- 正确 2：看谁在占连接
SELECT id, user, host, db, time, state, info
FROM information_schema.PROCESSLIST
ORDER BY time DESC LIMIT 20;
-- 正确 3：掐掉空闲超时连接
SET GLOBAL wait_timeout = 600; -- 空闲 10 小时太夸张，改成 10 分钟
SET GLOBAL interactive_timeout = 600;
# 正确：用连接池（如 DBUtils / SQLAlchemy pool），复用连接
# from sqlalchemy import create_engine
# engine = create_engine("mysql+pymysql://app:xxx@db/appdb", pool_size=10, max_overflow=20)
加分点：连接数不是越大越好，CPU 核数 * 2 + 磁盘数 是经验值；超过几千后上下文切换开销反而拖慢性能。
坑 3：root 密码弱且公网暴露
场景 / 错误示范：
为了图方便，root 密码设成 123456，还允许 % 远程登录，MySQL 端口直接暴露公网。
-- 错误：弱密码 + 任意主机
CREATE USER 'root'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
-- 查看风险账号
SELECT user, host FROM mysql.user;
-- 看到 root@% 立即警觉
为什么是坑：公网扫端口脚本分分钟爆破弱密码，一旦被进库，数据被删/被勒索（常见于暗网删库勒索）。root@% 是运维大忌，生产数据库应只允许内网 + 强密码 + 最小权限账号。
正确做法：
-- 正确 1：root 只允许本机
DELETE FROM mysql.user WHERE user='root' AND host='%';
FLUSH PRIVILEGES;
-- 正确 2：业务账号最小权限
CREATE USER 'app_rw'@'10.0.0.%' IDENTIFIED BY 'Cplx@Str0ng#Pwd';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_rw'@'10.0.0.%';
-- 正确 3：强密码策略
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;
加分点：数据库绝不直接暴露公网，走 VPC 内网 + 堡垒机；敏感操作账号双因子认证，离职员工账号当天回收。
坑 4：event 事件调度器未开启
场景 / 错误示范：
建了一个每天凌晨清理日志的 event，但业务日志一直没清，查了半天才发现事件调度器根本没开。
-- 错误：建了 event 但 scheduler 是 OFF
CREATE EVENT cleanup_log
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM operation_log WHERE create_time < NOW() - INTERVAL 7 DAY;
SHOW VARIABLES LIKE 'event_scheduler';
-- 返回 OFF，事件根本不会被触发
为什么是坑：MySQL 的事件调度器（Event Scheduler）默认关闭。建了 event 但没开调度器，它就是个摆设；重启后 event_scheduler 又会回到默认 OFF，需要 my.cnf 固化。
正确做法：
-- 正确 1：在线开启
SET GLOBAL event_scheduler = ON;
-- 验证
SHOW PROCESSLIST;
-- 应看到一条 "Daemon" 状态的 event_scheduler 线程
-- my.cnf 固化，重启不失效
[mysqld]
event_scheduler = ON
-- 查看已建事件是否在跑
SELECT event_name, status, last_executed FROM information_schema.EVENTS
WHERE event_schema = 'appdb';
加分点：清理类任务优先用系统 crontab + 业务脚本，DB event 适合"纯数据库内部"的定时维护；event 出问题不易被监控，线上慎用。
坑 5：跨大版本直接升级报错
场景 / 错误示范：
图省事把 5.6 的数据目录直接挂到 8.0 启动，起不来，报错"Cannot upgrade 5.6 to 8.0 directly"。
# 错误：直接换二进制启动老数据目录
systemctl stop mysql56
# 直接用 8.0 的 mysqld 启动同一个 datadir
mysqld --defaults-file=/etc/my8.cnf &
# 报错：Data dictionary upgrade from version 5... not supported
为什么是坑：MySQL 大版本之间数据字典结构不兼容，8.0 只支持从 5.7 原地升级，5.6 及更早必须先升到 5.7 再升 8.0。跨级升等于裸奔，数据字典直接崩。
正确做法：
# 正确路径：逐级升级
# 5.6 -> 5.7 -> 8.0
# 每升一级：mysqldump 备份 -> 停库 -> 换二进制 -> mysql_upgrade / 启动自动升级
# 升级前检查
mysql shell > util.checkForUpgrade();
-- 升级后必跑
USE mysql;
CHECK TABLE user;
-- 8.0 启动时若检测到低版本数据字典，会自动触发 upgrade
加分点：升级窗口至少预留回退时间，升级前做全量备份；小版本（8.0.36→8.0.37）滚动升级风险低，大版本必须演练。
坑 6：慢查询日志长期未开启
场景 / 错误示范：
业务变慢，但数据库从没开过慢查询日志，到底哪条 SQL 慢完全无从下手。
-- 错误：slow_query_log 默认 OFF
SHOW VARIABLES LIKE 'slow_query_log';
-- 返回 OFF
SHOW VARIABLES LIKE 'long_query_time';
-- 默认 10 秒，等于不记录
为什么是坑：没有慢日志，性能问题就是盲人摸象。等业务报障再开，已经错过现场。慢查询日志是 SQL 优化的"病历本"，必须常年开着。
正确做法：
-- 正确：在线开启慢日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒就记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON; -- 没走索引的也记
-- my.cnf 固化
[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
# 用 mysqldumpslow 或 pt-query-digest 分析慢日志
# mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
加分点：慢日志要定期切分（按天 rotate），否则单文件几个 G 分析不动；把 Top10 慢 SQL 每周review 一遍。
坑 7：服务器时区不同步导致时间错乱
场景 / 错误示范：
主从跨机房部署，主库在东八区、从库在 UTC，NOW() 两边差 8 小时，binlog 重放时间错乱。
-- 错误：系统时区不一致
SELECT NOW(), @@global.time_zone;
-- 主库：2026-10-07 12:00:00 SYSTEM
-- 从库：2026-10-07 04:00:00 SYSTEM （UTC）
为什么是坑：TIMESTAMP 类型按时区转换存储，DATETIME 不转。主从时区不一致，带 NOW() 的语句重放后时间对不上；业务按时间筛选订单会出现"差 8 小时"的灵异 bug。
正确做法：
-- 正确：所有实例统一设为东八区（或 UTC，团队约定一致即可）
SET GLOBAL time_zone = '+08:00';
SET time_zone = '+08:00'; -- 会话级
-- 验证
SELECT NOW(), @@global.time_zone;
# my.cnf 固化
[mysqld]
default-time-zone = '+08:00'
# 系统层也开 NTP 时间同步
# timedatectl set-timezone Asia/Shanghai
加分点：跨地域业务更建议统一存 UTC，展示层再转当地时区；这样迁机房、切时区都不会动数据。
坑 8：在线大 DDL 直接锁表
场景 / 错误示范：
给千万行的 orders 表加索引，直接 ALTER TABLE orders ADD INDEX idx_create(create_time)，业务卡死 20 分钟。
-- 错误：大表直接在线 DDL，没预估影响
ALTER TABLE orders ADD INDEX idx_create_time (create_time);
-- 期间 DML 被堵，业务连接堆积
为什么是坑：MySQL 8.0 虽然支持 Online DDL，但加索引仍要扫描全表 + 写 redo，期间会拿 MDL 锁，阻塞并发写；大表（千万级）直接跑，业务接口全部超时。
正确做法：
-- 正确 1：用 Online DDL 并指定不拷贝表
ALTER TABLE orders
ADD INDEX idx_create_time (create_time),
ALGORITHM=INPLACE, LOCK=NONE;
-- LOCK=NONE 期间仍允许读写
-- 正确 2：超大表用 pt-online-schema-change（逻辑复制）
-- pt-online-schema-change --alter "ADD INDEX idx_create_time(create_time)" \
-- D=appdb,t=orders --execute
-- 正确 3：8.0 支持并行 DDL
SET GLOBAL innodb_parallel_read_threads = 8;
-- DDL 前先在从库/预发跑，评估时长
EXPLAIN 看一下执行计划；或在测试库量一遍耗时
加分点：DDL 一定在业务低峰跑，跑前 SHOW PROCESSLIST 确认无长事务；大表 DDL 工具（pt-osc / gh-ost）比裸 ALTER 安全。
坑 9：脚本里明文写数据库密码
场景 / 错误示范：
备份脚本、部署脚本里把数据库密码明文写死，脚本传到 git 仓库被公开。
# 错误：明文密码写在脚本里
mysqldump -uroot -pMyP@ssw0rd appdb > backup.sql
# 错误：代码里硬编码密码
import pymysql # 本环境仅作示范
conn = pymysql.connect(host='db', user='root', password='MyP@ssw0rd')
为什么是坑：代码仓库一旦泄露（开源、员工离职电脑丢失、CI 日志打印），数据库密码就裸奔。明文密码还在 history、ps 命令行里暴露，同机其他用户能看到。
正确做法：
# 正确 1：用选项文件，权限 600
# ~/.my.cnf 内容：
# [client]
# user=root
# password=MyP@ssw0rd
mysqldump --defaults-file=~/.my.cnf appdb > backup.sql
# 正确 2：从环境变量读
import os # 标准库
import pymysql # 本环境仅作示范
conn = pymysql.connect(
host='db',
user=os.environ['DB_USER'],
password=os.environ['DB_PASSWORD'], # 从环境变量取
database='appdb'
)
加分点：生产密码交给密钥管理系统（KMS / Vault），按环境隔离；定期轮换密码，别一个密码用三年。
坑 10：max_allowed_packet 过小丢大包
场景 / 错误示范：
往库里插一段大 JSON（16MB），报"Packet too large"，应用偶发失败。
-- 错误：默认 max_allowed_packet 只有 64M 或 4M
SHOW VARIABLES LIKE 'max_allowed_packet';
-- 插入一条含 20MB JSON 的行
INSERT INTO docs(content) VALUES (REPEAT('x', 20*1024*1024));
-- 报错：MySQL server has gone away / Packet too large
为什么是坑：max_allowed_packet 限制单条网络包大小，默认值偏小（4M~64M）。存大文本、JSON、二进制的业务遇到大包就断连，且报错信息不直观，排查很久。
正确做法：
-- 正确：调大到业务需要的尺寸（如 64M）
SET GLOBAL max_allowed_packet = 64 * 1024 * 1024;
-- 注意：客户端、服务端两边都要调
# my.cnf 固化，客户端也配
[mysqld]
max_allowed_packet = 64M
[mysqldump]
max_allowed_packet = 64M
[client]
max_allowed_packet = 64M
加分点：大字段别直接塞 MySQL，超过 1MB 的内容建议放对象存储（OSS），库里只存 URL；否则复制、备份、缓冲池都被拖垮。
坑 11：系统时区与库时区不一致
场景 / 错误示范：
Java 应用 JVM 时区是 UTC，MySQL 是东八区，写入时间读出来差 8 小时，财务对账对不上。
-- 错误：应用与库时区各玩各的
SELECT NOW(); -- MySQL 东八区 12:00
-- 应用里 new Date() 拿到的是 UTC 04:00
为什么是坑：时间字段在"应用 → 驱动 → MySQL"三层各转一次，任何一层时区不一致，存进去读出来就错。TIMESTAMP 会随时区转，DATETIME 不转，混用更乱。
正确做法：
-- 正确 1：MySQL 统一时区
SET GLOBAL time_zone = '+08:00';
# 正确 2：连接串显式指定时区
# pymysql 连接串：charset='utf8mb4'
# JDBC 连接串：serverTimezone=Asia/Shanghai
# spring.datasource.url=jdbc:mysql://db:3306/appdb?serverTimezone=Asia/Shanghai
-- 约定：统一用 DATETIME 存业务时间，避免时区漂移
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
create_time DATETIME NOT NULL -- 不用 TIMESTAMP，避免时区转换
);
加分点：全公司约定"数据库一律东八区、应用一律东八区、日志一律东八区"，出问题少一半；涉外业务再单独存 UTC。
坑 12：无监控告警故障半夜才发现
场景 / 错误示范：
数据库主库磁盘满了、从库延迟了 1 小时，直到第二天早上用户投诉才发现，错过最佳处理时间。
-- 错误：没有任何主动监控，全靠用户反馈
-- 没有 Zabbix / Prometheus / 云监控，没有告警群
为什么是坑：数据库是核心基础设施，故障发现时间直接决定损失。没有监控 = 闭眼开车；告警不分级 = 告警风暴被忽略。必须覆盖资源、性能、复制、可用性四个维度。
正确做法：
-- 核心监控指标（采样入 Prometheus / Zabbix）
-- 1) 资源：磁盘使用率 >80%、CPU >80%、内存 >85%
-- 2) 性能：QPS、慢查询数、连接数、缓冲池命中率
-- 3) 复制：IO/SQL 线程状态、Seconds_Behind_Source
-- 4) 可用性：主库存活、是否可写
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW REPLICA STATUS\G
# 伪代码：告警分级
# P0：主库挂、磁盘满、复制中断 -> 电话 + 短信
# P1：慢查询激增、连接数 >80% -> 企业微信
# P2：单日慢 SQL Top 列表 -> 日报
加分点：监控告警要做"演练"——定期人为造一次故障，验证告警是否真的到人、值班是否真的响应；告警收敛后周报复盘。
本篇速查表
| 坑号 | 现象关键词 | 根因一句话 | 一句话解法 |
|---|---|---|---|
| 1 | 磁盘 100% | binlog 不清理 | 设过期 + PURGE + 磁盘告警 |
| 2 | Too many connections | 没连接池/泄漏 | 连接池 + wait_timeout |
| 3 | 库被脱裤 | root@% 弱密码暴露公网 | 最小权限 + 内网 + 强密码 |
| 4 | event 不执行 | event_scheduler OFF | 开调度器并 my.cnf 固化 |
| 5 | 跨大版本起不来 | 数据字典不兼容 | 逐级升 5.7→8.0 |
| 6 | 慢 SQL 无从查 | 没开慢日志 | 常年开 slow_query_log |
| 7 | 时间差 8 小时 | 主从时区不一致 | 统一 time_zone + NTP |
| 8 | DDL 堵业务 | 大表裸 ALTER | pt-osc / LOCK=NONE |
| 9 | 密码泄露 | 明文写脚本/git | 环境变量 + 选项文件 |
| 10 | Packet too large | max_allowed_packet 小 | 调大到 64M |
| 11 | 应用与库时间对不上 | 时区链路不一致 | 连接串指定时区 |
| 12 | 半夜才发现故障 | 无监控告警 | 四维度监控 + 分级告警 |
运维注意事项
1. 服务器磁盘、内存、CPU、连接数、复制延迟，五项必须有监控和告警阈值，缺一不可。
2. 任何账号权限遵循最小原则，业务账号不给 SUPER、不允许多库跨写；离职账号当天回收。
3. 变更（DDL、参数调整、版本升级）一律走工单 + 低峰执行 + 可回退，禁止线上手改。
4. 密码、密钥、证书集中管理，禁止入库入 git；定期轮换。
5. 每季度做一次故障演练（主库宕机、磁盘满、误删表），验证备份、监控、值班全链路。
动手练习
练习 1：业务反馈"数据库连不上，报 Too many connections"。请列出至少 4 个排查与应急步骤。
<details>
<summary>参考答案</summary>
1. SHOW PROCESSLIST 看连接分布；2. 看是否有连接泄漏的应用实例；3. 临时 KILL 空闲/异常连接；4. 调大 max_connections 并查应用连接池配置；5. 查是否有慢 SQL 占着连接不放。
</details>
练习 2：为什么 root@'%' 是危险配置？生产应该怎么建账号？
<details>
<summary>参考答案</summary>
root@% 允许从任意主机以最高权限远程登录，公网一旦暴露或弱密码被爆破，整库沦陷。生产应：root 仅本机登录；业务账号按网段（如 10.0.0.%）+ 最小权限（库级 SELECT/INSERT/UPDATE/DELETE）+ 强密码；管理走堡垒机。
</details>
练习 3：给一张 2000 万行的表加索引，你会怎么操作避免影响业务？
<details>
<summary>参考答案</summary>
1. 先在从库/预发评估耗时；2. 业务低峰执行；3. 用 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 或 pt-online-schema-change；4. 跑前确认无长事务；5. 全程监控复制延迟与业务接口耗时。
</details>
下一篇预告：返回教程总目录
文章回复
0 条公开回复