数据库基础体系 · 第 19/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL 生产运维：参数、容量、备份、监控与常见故障排查

一、先明确运行边界

本文以 MySQL 8.4 的公开语义为基础，示例默认：

存储引擎为 InnoDB；
业务主要使用事务表；
数据库部署在 Linux 服务器上；
参数可能通过配置文件、启动参数或运行时变量设置；
备份示例使用 MySQL 社区版常见的 mysqldump 与二进制日志；
高可用切换、GTID、半同步复制等属于复制体系，本文只在备份和故障排查需要时说明其边界。

生产运维不能只看“数据库是否存活”。至少要同时回答五个问题：

参数当前生效了吗，重启后还会生效吗？
当前容量够用多久，满盘前会发生什么？
备份是否形成了可恢复的数据，而不是只生成了一个文件？
监控能否区分 CPU、磁盘、锁、连接和复制问题？
故障发生时，如何从现象定位到原因，并验证恢复确实完成？

二、理解 InnoDB：参数和故障现象的基础

2.1 数据、索引、日志和临时空间分别是什么

InnoDB 的表数据和索引以页为基本管理单位，默认页大小通常为 16 KiB。聚簇索引叶子节点中保存整行数据，二级索引叶子节点保存索引列以及主键值。

因此，一行数据通常不只占用一份空间：

聚簇索引：保存行本身；
每个二级索引：保存索引列和主键；
undo：保存旧版本，用于回滚和一致性读；
redo：记录已修改页的恢复信息；
binlog：记录供复制和时间点恢复使用的逻辑事件；
临时表空间或磁盘临时文件：用于排序、分组、物化等操作。

例如：

CREATE TABLE account (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    status TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_status_created (status, created_at)
) ENGINE = InnoDB;

这张表至少需要：

聚簇索引，存储 id、email、status、created_at；
idx_status_created 二级索引，存储 status、created_at 和主键 id；
页级空间、记录头、空闲空间和索引树内部节点。

所以“表中有 1 TB 行数据”不等于“磁盘只需要 1 TB”。容量评估必须看真实的表空间和索引大小。

可以使用：

SELECT
    table_schema,
    table_name,
    engine,
    table_rows,
    data_length,
    index_length,
    data_free
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
ORDER BY data_length + index_length DESC
LIMIT 20;

其中：

data_length 是数据相关空间估计；
index_length 是索引空间估计；
对 InnoDB 而言，table_rows 通常是估算值，不应当当作精确行数；
data_free 的含义依赖表空间组织方式，不等同于整个文件系统可回收空间。

2.2 事务、redo、undo 与 binlog 的关系

一次典型的 InnoDB 事务可以抽象为：

事务修改内存中的 buffer pool 页；
生成 undo 记录，以便回滚或提供一致性读；
生成 redo 记录，描述数据页修改；
提交时，按配置将 redo 刷盘，并写入 binlog；
后台线程以后将脏页刷入表空间。

redo 的作用是崩溃恢复：如果修改已经提交但数据页尚未写回，启动恢复时可通过 redo 重做。

undo 的作用不同：

回滚未提交事务；
在多版本并发控制下，为旧事务提供历史版本；
长事务不提交时，旧版本可能持续保留，阻碍 purge，导致 undo 膨胀。

binlog 主要记录数据库逻辑变更事件，用于：

复制；
增量备份；
时间点恢复。

因此，redo 和 binlog 不是同一种日志，也不能互相替代：

只有 redo，不能可靠地替代长期备份或复制日志；
只有 binlog，没有一个一致的基线备份，也无法从很早的时间点恢复；
只备份表文件而不考虑事务一致性，可能得到逻辑上不一致的数据。

三、参数管理：知道“当前值”还不够

3.1 参数的作用域和生命周期

MySQL 参数至少涉及三个概念：

全局作用域：影响服务器或新建连接；
会话作用域：只影响当前连接；
持久化配置：重启后仍然存在。

查看变量：

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW SESSION VARIABLES LIKE 'transaction_isolation';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

SHOW GLOBAL VARIABLES 查看全局值，SHOW SESSION VARIABLES 查看当前连接值。很多会话参数在建立连接时从全局值复制，之后修改全局值不会改变已有连接。

例如：

SET GLOBAL max_connections = 800;

它只改变运行中的全局变量，并不一定写入配置文件；重启后可能恢复为旧值。

在支持持久化的参数上，可以使用：

SET PERSIST max_connections = 800;

这会将配置写入 MySQL 的持久化变量文件，通常是数据目录下的 mysqld-auto.cnf。该文件属于实例配置的一部分，应纳入变更管理和备份。若只想验证语法而不改变运行状态，可使用：

SET PERSIST_ONLY max_connections = 800;

常见生命周期如下：

操作	当前实例	新连接	重启后
`SET SESSION`	当前连接改变	否	否
`SET GLOBAL`	通常改变	视参数而定	通常不保留
`SET PERSIST`	通常改变	视参数而定	保留
配置文件	重启后生效	是	保留

并非所有变量都能动态修改。有些参数只能启动时设置，有些动态参数还受到权限、状态或最小最大值限制。变更前应检查：

SELECT
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM performance_schema.variables_info
WHERE VARIABLE_NAME IN (
    'max_connections',
    'innodb_buffer_pool_size',
    'innodb_redo_log_capacity'
);

performance_schema.variables_info 可以帮助判断变量来源、是否动态以及相关修改信息；具体列应以目标版本实际返回为准。

3.2 参数文件校验与变更步骤

直接编辑配置文件并重启，风险在于：

拼写错误导致实例无法启动；
参数在当前版本已移除或改名；
单位理解错误，例如字节、页、秒或枚举值；
参数虽合法，但与机器内存、磁盘或连接数不匹配。

建议先用实例的配置检查能力验证文件。例如常见 Linux 安装可使用：

mysqld --defaults-file=/etc/my.cnf --validate-config

具体路径和运行用户取决于发行版。若二进制不支持该选项，应使用目标版本文档规定的校验方式，并在备用实例或测试环境启动验证。

一个完整的参数变更过程至少包括：

记录旧值、变更原因和预期；
确认参数作用域、动态性和重启行为；
确认剩余内存、磁盘和连接资源；
先在低风险时段或从库验证；
修改一个相关参数，而不是同时调整一组无法归因的参数；
观察错误日志、延迟、吞吐和资源变化；
验证重启后配置仍符合预期；
保留回滚值。

3.3 关键 InnoDB 参数

`innodb_buffer_pool_size`

Buffer pool 是 InnoDB 缓存数据页和索引页的主要内存区域。命中 buffer pool 时，查询可以避免从磁盘读取；写入也通常先修改内存页，再由后台刷盘。

它太小会导致：

读 I/O 增加；
页面频繁淘汰；
脏页刷盘压力集中；
查询延迟抖动。

它太大则可能：

挤压连接线程、排序缓冲、临时表和操作系统内存；
触发 swap；
让系统在高并发下出现严重尾延迟。

“设置为物理内存的某个固定百分比”只是经验，不是保证。应先扣除操作系统、连接级内存、监控代理、备份进程和其他服务的需求，再评估 buffer pool。

查看使用情况：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

在多实例或大内存环境中，buffer pool 可以分成多个实例以降低内部竞争，但实例数量不是越多越好；还要考虑版本语义、内存规模和实际争用。

`innodb_redo_log_capacity`

redo 容量决定了系统能够容纳多少尚未通过数据页刷盘释放的 redo。容量太小可能使检查点推进过于频繁，造成写入抖动；容量增大可以给脏页刷盘更多缓冲时间，但不能消除磁盘写入需求。

容量太大也有代价：

崩溃恢复可能需要处理更多日志；
不能替代足够快的存储；
不能解决持续生成 redo 大于刷盘能力的问题。

要区分两种情况：

短时突发写入：更大的 redo 可能吸收峰值；
长期写入速率超过刷盘速率：最终仍会出现 checkpoint pressure、延迟升高或空间耗尽。

MySQL 8.4 使用 innodb_redo_log_capacity 表示 redo 总容量；不要把旧版本中分散的 redo 文件参数直接照搬到新版本。

`max_connections` 与连接级内存

max_connections 是并发客户端连接上限，不是合理并发数，也不是每个连接预先分配全部内存。

内存风险可以近似写成：

M_{\text{total}} \approx M_{\text{global}} + C_{\text{active}} \times M_{\text{per-connection}} + M_{\text{temporary}} + M_{\text{OS}}

其中：

$M_{\text{global}}$ ：buffer pool、线程缓存等全局内存；
$C_{\text{active}}$ ：同时执行语句的连接数；
$M_{\text{per-connection}}$ ：排序、连接、读缓冲等可能按操作分配的内存；
$M_{\text{temporary}}$ ：临时表和其他额外分配；
$M_{\text{OS}}$ ：操作系统和其他进程所需内存。

这是风险估算，不是精确内存模型。连接数上升时，不能只看 max_connections，还要看：

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Connection_errors%';

Threads_connected 高表示连接多，Threads_running 高表示同时执行的线程多；大量连接处于空闲状态和大量连接同时执行，是不同问题。

`innodb_flush_log_at_trx_commit` 与 `sync_binlog`

这两个参数影响提交持久性和性能取舍。

在常见配置下：

innodb_flush_log_at_trx_commit=1：事务提交时将 redo 写入并刷新到持久介质，提供更强的提交持久性；
sync_binlog=1：binlog 提交同步刷盘。

如果降低这些设置，可能减少 I/O，但操作系统崩溃或机器掉电时，最近提交的事务可能丢失，具体风险取决于存储设备、文件系统和故障类型。

“事务返回成功”与“数据在任何故障下都绝不丢失”不是同一个承诺。生产配置必须由 RPO 要求决定，而不是只追求吞吐。

`sql_mode`、时区与字符集

这类参数不一定直接表现为 CPU 或 I/O 问题，却会造成数据语义错误。

应明确检查：

SELECT
    @@GLOBAL.sql_mode,
    @@SESSION.sql_mode,
    @@GLOBAL.time_zone,
    @@SESSION.time_zone,
    @@GLOBAL.character_set_server,
    @@GLOBAL.collation_server;

例如严格 SQL 模式下，非法或超范围数据可能直接报错；非严格模式下可能发生截断或警告。应用连接池若设置了自己的会话参数，还可能导致同一实例上不同连接行为不一致。

四、容量规划：从字节、并发和增长速度推导

4.1 磁盘容量不是“当前数据大小加一点”

可以把生产磁盘需求粗略拆成：

D_{\text{required}} = D_{\text{data}} + D_{\text{index}} + D_{\text{redo}} + D_{\text{undo/temp}} + D_{\text{binlog}} + D_{\text{backup/local}} + D_{\text{reserve}}

其中：

$D_{\text{data}}$ ：聚簇索引中的数据；
$D_{\text{index}}$ ：二级索引；
$D_{\text{redo}}$ ：redo 文件；
$D_{\text{undo/temp}}$ ：undo、临时表空间和临时文件；
$D_{\text{binlog}}$ ：保留周期内的 binlog；
$D_{\text{backup/local}}$ ：本机保留的备份或导出文件；
$D_{\text{reserve}}$ ：在线 DDL、重建索引、复制异常和运维操作所需余量。

例如：

当前数据与索引：800 GB；
每日 binlog：120 GB；
binlog 保留 7 天：约 840 GB；
本地压缩逻辑备份：300 GB；
redo、临时空间和操作余量：至少 200 GB；
预计未来一个月数据增长：150 GB。

则粗略需求为：

800 + 840 + 300 + 200 + 150 = 2290\text{ GB}

如果磁盘只有 2.4 TB，表面上看“当前数据还能放下”，但 binlog 或一次在线 DDL 就可能触发满盘。

4.2 增长速度与耗尽时间

从监控中取得两个时间点的已用空间：

g = \frac{U_2-U_1}{t_2-t_1}

其中 $g$ 是平均增长速度， $U$ 是已用空间。

若文件系统可安全使用的上限为 $C$ ，当前已用空间为 $U$ ，则估计剩余时间：

T_{\text{left}} = \frac{C-U}{g}

这个公式只能用于趋势预警。生产上还要加入突发项：

大批量导入；
归档失败；
binlog 由于从库延迟而无法清理；
在线 DDL 临时占用；
备份文件重复生成；
事务长时间未提交导致 undo 增长。

检查文件系统：

df -h
df -i
du -xhd1 /var/lib/mysql | sort -h

df -h 看块空间，df -i 看 inode。两者都可能导致写入失败。删除文件后空间没有释放，还可能是进程仍持有已删除文件：

lsof +L1

4.3 表空间和 binlog 统计

查看表大小：

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;

查看 binlog：

SHOW BINARY LOGS;

应关注：

最老 binlog 的时间；
当前写入速度；
从库是否仍需要旧日志；
备份链是否依赖这些日志；
自动清理策略是否与恢复目标一致。

不能简单地执行：

PURGE BINARY LOGS TO 'binlog.000123';

如果某个从库尚未读取这些日志，清理后它可能无法继续复制，只能重新构建。清理 binlog 前应检查复制位点和备份策略。

4.4 容量与性能的关系

磁盘容量和磁盘性能是两个维度：

容量决定能否继续写；
IOPS、吞吐和 fsync 延迟决定写入和提交是否及时；
redo 充足不能弥补磁盘 fsync 很慢；
buffer pool 增大不能弥补索引设计错误或随机 I/O 饱和。

因此容量监控至少同时采集：

文件系统使用率；
数据目录各子目录大小；
binlog 每小时增长量；
redo 使用和 checkpoint 压力；
磁盘 IOPS、吞吐、await、队列长度；
备份和在线 DDL 的额外空间。

五、备份：从“复制数据”到“可恢复”

5.1 RPO、RTO 和备份目标

备份设计前先定义：

RPO（Recovery Point Objective）：最多允许丢失多长时间的数据；
RTO（Recovery Time Objective）：故障后允许多长时间恢复服务。

例如：

每天一次全量备份，且不保存 binlog，RPO 可能接近 24 小时；
全量备份加连续保存 binlog，可将恢复点推进到某个时间或事务；
备份文件存在，但恢复需要 20 小时，而业务要求 30 分钟恢复，则该备份方案在 RTO 上不合格。

备份的可靠性应定义为：

\text{可靠备份} = \text{一致性} + \text{完整性} + \text{可读取} + \text{可恢复验证}

生成文件不等于完成备份。

5.2 逻辑备份：`mysqldump`

对 InnoDB 事务表，常见一致性导出方式是：

mysqldump \
  --single-transaction \
  --quick \
  --routines \
  --events \
  --triggers \
  --set-gtid-purged=COMMENTED \
  --all-databases \
  > full.sql

各选项的含义：

--single-transaction：对支持事务的表，在事务隔离语义下获取一致性快照，通常不需要长时间锁住普通 DML；
--quick：逐行读取，减少客户端内存占用；
--routines：包含存储过程和函数；
--events：包含事件；
--triggers：包含触发器；
--set-gtid-purged=COMMENTED：如何处理 GTID 信息必须结合是否用于复制和恢复决定，不能机械套用；
--all-databases：导出所有数据库，但权限和系统对象处理仍需结合版本验证。

重要边界：

--single-transaction 只对事务表提供该一致性语义；
对 MyISAM 等非事务表，它不能保证同一快照；
导出期间执行 DDL 可能导致失败或结果不符合预期；
外部文件、用户操作系统账号、配置文件和证书不会自动包含；
超大实例的逻辑导出会消耗较长时间和大量 CPU、磁盘带宽；
通过复制从库执行备份可以降低主库压力，但必须确认从库数据完整且复制一致。

导入到测试实例：

mysql --host=127.0.0.1 --port=3307 -uroot -p < full.sql

恢复时不要直接覆盖唯一生产实例。应先导入隔离实例，检查：

SELECT COUNT(*) FROM app.account;
CHECK TABLE app.account;

CHECK TABLE 能发现部分表级问题，但不能替代完整业务校验，例如金额汇总、行数、关键主键范围和抽样查询。

5.3 物理备份与克隆边界

物理备份直接保存 InnoDB 表空间、redo、undo 等文件，通常更适合大型实例，因为恢复时不需要逐行执行 SQL。MySQL Enterprise Backup 是商业工具；社区版不能假定存在一个等价的官方免费热物理备份命令。

不应在数据库运行期间简单执行：

cp -r /var/lib/mysql /backup/mysql

这可能复制到不同时间点的表空间、redo 和元数据，无法保证可恢复一致性。物理备份必须使用支持 InnoDB 一致性和备份锁语义的工具，或者在停库后进行冷备。

文件系统快照也不是天然一致：

快照系统必须保证崩溃一致性或与数据库备份流程协同；
快照保留策略要覆盖恢复时间；
快照不能代替异地备份；
必须实际挂载副本并启动恢复验证。

5.4 全量备份加 binlog 的时间点恢复

时间点恢复通常需要：

一个已验证的全量备份；
从全量备份对应位置开始的连续 binlog；
目标时间点；
一个隔离的恢复实例。

典型流程：

# 1. 恢复全量备份
mysql --host=127.0.0.1 --port=3307 -uroot -p < full.sql

# 2. 查看 binlog 中的事件
mysqlbinlog --base64-output=DECODE-ROWS -vv \
  binlog.000123 binlog.000124 > events.txt

# 3. 按时间恢复到目标时间
mysqlbinlog \
  --stop-datetime="2025-03-08 12:00:00" \
  binlog.000123 binlog.000124 \
  | mysql --host=127.0.0.1 --port=3307 -uroot -p

这里的时间必须明确时区。mysqlbinlog 读取的是 binlog 事件；若使用基于行的复制格式，-vv 可帮助查看行变更，但输出不一定是可直接执行的原始业务 SQL。

更安全的恢复操作是：

先在隔离实例恢复全量；
确认全量备份结束位置；
从该位置开始应用连续 binlog；
应用到目标时间之前；
核对关键表；
再决定是否切换业务。

如果是误删数据，目标时间应设置在误操作之前。直接将 binlog 全部应用到最新状态，可能重新执行错误操作。

5.5 备份验证必须包含恢复演练

备份任务成功日志只能说明命令退出成功。至少应验证：

test -s full.sql
sha256sum full.sql > full.sql.sha256
gzip -t full.sql.gz

更重要的是定期恢复：

在隔离环境创建与生产相近的 MySQL 版本；
恢复最近全量；
应用 binlog；
检查表数量、关键表行数、最大主键、业务汇总；
测量从备份开始到可查询的时间；
记录权限、字符集、时区和事件是否恢复；
验证备份文件可从异地存储读取。

备份应与生产故障域隔离。把唯一备份放在同一块磁盘上，无法应对磁盘损坏；放在同一机房，也无法应对机房级故障。

六、监控：用状态、日志和系统指标建立因果链

6.1 监控的四层

第一层：可用性

检查端口和 SQL：

mysqladmin ping -h127.0.0.1 -uroot -p

mysqladmin ping 主要说明服务器是否响应连接，不代表业务 SQL 正常。更可靠的探针应执行轻量 SQL：

SELECT 1;

并检查目标业务库、权限和基本读写路径。

第二层：资源

操作系统至少监控：

CPU 使用率、iowait；
内存、swap；
磁盘使用率和 inode；
IOPS、吞吐、延迟和队列；
网络丢包、连接和带宽；
文件句柄。

数据库层至少监控：

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Handler_read%';

单个计数器没有上下文。应监控速率，例如：

\text{QPS} = \frac{Q_2-Q_1}{t_2-t_1}

如果 Questions 增长很快但 Threads_running 同时升高，可能是并发执行变多；如果 QPS 下降而线程长期运行，可能是查询变慢或被锁阻塞。

第三层：数据库内部状态

查看当前线程：

SHOW FULL PROCESSLIST;

关注：

Command 是否为 Sleep；
Time 是否很长；
State 是否包含等待锁、排序、发送数据等状态；
Info 是否为具体 SQL；
是否存在大量 Locked 或长时间事务。

生产环境中，优先使用具备权限控制的 performance_schema 和 sys 视图，而不是把高权限进程信息开放给所有账号。

查看长事务：

SELECT
    trx_id,
    trx_started,
    trx_state,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

长事务会造成：

undo 不能及时清理；
purge 延迟；
表空间增长；
锁持有时间变长；
备份快照长期保留旧版本。

第四层：日志和业务信号

错误日志用于发现：

崩溃恢复；
表空间或文件系统错误；
权限、启动参数错误；
InnoDB 检查失败；
连接、复制和插件错误。

慢查询日志用于定位执行时间超过阈值的语句，但“慢”是执行时间定义，不等于一定是索引问题。启用前应评估磁盘写入和日志轮转：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

线上直接降低 long_query_time 可能迅速增加日志量。应结合采样、轮转和短时间观察窗口。

Performance Schema 的语句摘要可用于按指纹聚合 SQL：

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

字段的时间单位为皮秒级计时值，展示时应换算为秒或毫秒：

\text{毫秒} = \frac{\text{timer}}{10^9}

因为 $1$ 秒等于 $10^{12}$ 皮秒，毫秒等于 $10^9$ 皮秒。

6.2 告警要围绕症状和趋势

有意义的告警包括：

文件系统接近安全上限；
inode 使用率异常；
Threads_running 持续高于正常基线；
连接错误增加；
长事务超过业务允许时长；
锁等待和死锁异常增加；
binlog 增长速度异常；
从库延迟、SQL 线程或 IO 线程停止；
备份任务失败或超过计划窗口；
最近一次恢复演练过期。

单纯告警“CPU 大于 80%”经常没有足够信息。CPU 高可能来自正常批处理，也可能来自错误 Join；CPU 不高也不能排除磁盘延迟、锁等待或连接耗尽。

七、常见故障排查

7.1 无法连接或连接数耗尽

现象

应用报：

Too many connections；
连接建立超时；
连接被拒绝；
连接成功但执行 SQL 超时。

先区分网络、认证和数据库连接上限：

nc -vz db.example.com 3306
mysql -hdb.example.com -uapp -p -e 'SELECT 1'

数据库内检查：

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Connection_errors%';
SHOW VARIABLES LIKE 'max_connections';

诊断路径

端口不通：检查网络、防火墙、监听地址和实例进程；
端口通但认证失败：检查账号、主机匹配、密码和 TLS；
Too many connections：检查连接池是否泄漏、连接是否长期空闲、查询是否阻塞；
Threads_connected 高但 Threads_running 低：可能是连接池过大或大量空闲连接；
Threads_running 高：先查慢 SQL、锁和磁盘，不要立即无限增大 max_connections。

临时增加连接上限可能导致内存耗尽。更安全的恢复顺序通常是：

停止异常流量或降低应用并发；
终止确认无害的空闲或异常会话；
修复连接池和超时；
仅在内存核算后调整 max_connections；
验证连接错误、内存和延迟是否恢复。

终止连接：

KILL 12345;

KILL 会中断会话或查询，事务可能进入回滚；大事务回滚也会占用 I/O 和锁资源，不能把批量执行当成无成本操作。

7.2 慢查询：先确定是执行慢还是等待慢

查询耗时可以分解为：

T_{\text{total}} = T_{\text{queue}} + T_{\text{lock}} + T_{\text{read}} + T_{\text{compute}} + T_{\text{network}}

因此，慢查询不一定是优化器选择了错误索引，也可能在等待锁或排队。

先查看摘要和慢日志，再对具体 SQL 执行：

EXPLAIN ANALYZE
SELECT ...

EXPLAIN 主要展示优化器计划；EXPLAIN ANALYZE 会实际执行语句并报告实际行数、循环和耗时，因此只应对可接受的查询或测试副本使用。不要对生产环境的写语句随意执行实际分析。

检查重点：

是否全表扫描；
估算行数与实际行数是否严重偏差；
Join 顺序和访问方式是否合理；
是否出现大范围排序；
是否出现临时表；
返回行数是否远大于业务实际需要；
是否因为隐式类型转换导致索引无法有效使用。

统计信息过旧时，优化器可能错误估计选择性。可在低风险窗口考虑：

ANALYZE TABLE app.orders;

它会读取数据分布并更新统计信息，可能产生 I/O；不能在高峰期无计划地对所有大表执行。

反例是只看到 type=ALL 就立即加索引。若表很小，或者查询确实需要读取大部分行，全表扫描可能比索引回表更便宜。索引优化必须结合实际行数、选择性、写入成本和执行计划。

7.3 锁等待和死锁

InnoDB 的锁问题有两种常见形态：

锁等待：事务 A 等待事务 B 释放锁；
死锁：事务 A 等待 B，B 又等待 A，形成环。

死锁无法通过“继续等待”解决。InnoDB 会选择一个事务回滚并返回死锁错误，应用必须具备重试逻辑。

检查当前锁等待：

SELECT
    waiting_pid,
    waiting_query,
    blocking_pid,
    blocking_query,
    locked_table,
    locked_type
FROM sys.innodb_lock_waits;

如果目标版本的 sys 视图列不同，应先执行：

DESC sys.innodb_lock_waits;

也可以查看：

SHOW ENGINE INNODB STATUS\G

其中的 LATEST DETECTED DEADLOCK 能显示最近一次死锁涉及的事务和锁。

一个典型死锁

事务 A：

START TRANSACTION;
UPDATE account SET status = 1 WHERE id = 1;
-- 尚未提交

事务 B：

START TRANSACTION;
UPDATE account SET status = 1 WHERE id = 2;
-- 尚未提交

随后：

A 再更新 id=2，等待 B；
B 再更新 id=1，等待 A；
形成环，InnoDB 回滚其中一个事务。

解决方向不是简单关闭死锁检测，而是：

让所有代码按相同顺序访问资源，例如总是先锁较小的 id；
缩短事务；
不在事务中执行外部网络调用；
为更新条件建立合适索引，减少锁定范围；
对死锁错误实现有限次数、带退避的重试；
检查隔离级别与业务一致性要求。

死锁与锁等待的根因可能是同一类访问顺序问题，但处理方式不同：死锁通常快速返回错误，锁等待可能长期拖住线程和连接。

7.4 磁盘空间不足

现象

可能出现：

No space left on device；
无法写入 binlog；
临时表创建失败；
事务提交失败；
MySQL 无法启动；
复制线程因无法写 relay log 停止。

排查

df -h
df -i
du -xhd1 /var/lib/mysql | sort -h
lsof +L1

数据库内：

SHOW BINARY LOGS;
SELECT
    table_schema,
    table_name,
    data_length,
    index_length
FROM information_schema.tables
ORDER BY data_length + index_length DESC
LIMIT 20;

恢复时不要直接删除未知文件。尤其不能手工删除：

InnoDB 表空间；
redo 文件；
undo 文件；
未确认是否仍被使用的 binlog；
relay log。

更稳妥的处理顺序：

停止会产生额外文件的备份、导入或批处理；
确认是否有已删除但仍被进程占用的文件；
根据复制状态和备份策略清理旧 binlog；
将备份迁移到外部存储；
扩容文件系统或增加独立日志盘；
只有在确认后才删除临时文件；
恢复后检查提交、复制、备份和错误日志。

如果是 binlog 保留过久，应先查明为什么不能清理：可能是从库延迟、备份程序依赖、复制槽位或运维策略错误。盲目 PURGE 可能使从库无法追上。

7.5 复制延迟或复制停止

复制涉及至少两条数据流：

源库产生 binlog；
复制 IO 线程读取源库 binlog，并写入从库 relay log；
SQL 线程或复制应用线程读取 relay log；
从库执行事件并更新数据。

查看状态：

SHOW REPLICA STATUS\G

关注：

Replica_IO_Running；
Replica_SQL_Running；
Seconds_Behind_Source；
Source_Log_File 与 Read_Source_Log_Pos；
Relay_Source_Log_File 与 Exec_Source_Log_Pos；
Last_IO_Error；
Last_SQL_Error。

Seconds_Behind_Source 不是绝对可靠的真实延迟，尤其在网络中断、长事务或时间基准异常时。应结合源库当前 binlog、从库执行位置和业务读一致性判断。

诊断分支：

IO 线程停止：检查网络、账号权限、源库端口、TLS、binlog 是否已被清理；
IO 正常、SQL 停止：查看 SQL 错误，可能是重复键、表不存在、权限或数据冲突；
两线程都运行但延迟增加：可能是从库执行能力不足、单线程瓶颈、大事务或源库突发写入；
延迟突然下降：不代表数据一定完全一致，应核对执行位点和关键数据。

如果源库已清理从库尚未读取的 binlog，通常不能简单重启复制；需要根据备份和 GTID 状态重新构建从库。修复复制错误时，跳过事件可能造成数据不一致，不能把“复制恢复运行”当作“数据恢复正确”。

7.6 实例重启、崩溃恢复和无法启动

启动失败先看错误日志，而不是反复重启：

journalctl -u mysqld -n 200 --no-pager

或查看发行版配置的 MySQL 错误日志。

常见原因：

配置文件语法或参数错误；
数据目录权限错误；
磁盘或 inode 耗尽；
端口被占用；
InnoDB redo/表空间损坏；
版本升级不兼容；
内存不足被操作系统杀死。

崩溃恢复期间，InnoDB 会根据 redo 重做已记录的修改，并回滚未完成事务。恢复时间与未刷盘修改量、磁盘速度和事务规模有关。恢复期间不应删除 redo 或强行复制数据目录。

如果错误日志明确显示数据损坏，应：

先保留现场和原始文件；
确认最近可用备份；
在副本上尝试恢复；
仅在明确理解数据丢失风险后考虑强制恢复选项；
优先从备份重建，而不是长期运行在可能丢数据的修复模式下。

innodb_force_recovery 属于故障救援工具，不是日常启动参数。提高级别可能限制写入，甚至导致更多数据无法正常恢复；使用前应复制数据、记录级别并制定撤销方案。

7.7 怀疑数据损坏

不要仅凭某次查询报错就判断表损坏。先区分：

SQL 语法或权限错误；
磁盘 I/O 错误；
表空间文件缺失；
索引损坏；
应用写入异常；
服务器内存或存储设备故障。

可以查看：

CHECK TABLE app.account;

但对于 InnoDB，检查结果和修复能力受限，不能把 REPAIR TABLE 当成通用修复方法。发现底层 I/O 错误或校验错误时，应同时检查：

dmesg -T | egrep -i 'error|io|ext4|xfs|nvme|scsi'

并检查磁盘、文件系统、云盘事件和主机硬件。若备份可用，通常应在新实例上恢复并校验，而不是在原实例上反复尝试破坏性操作。

八、一个可执行的生产排障顺序

面对“数据库变慢或不可用”，可以按以下因果顺序执行：

第一步：确认影响范围

SELECT NOW(), @@hostname, @@port, @@read_only;
SELECT 1;

确认当前连接的是哪台实例，避免把排查命令执行在从库、代理或错误环境上。

第二步：检查主机资源

uptime
free -h
df -h
iostat -xz 1 5

CPU 高且磁盘正常：查 SQL、排序、函数计算；
iowait 高：查磁盘延迟、刷脏页、redo、备份和临时表；
内存不足或 swap：查 buffer pool、连接数和连接级缓冲；
磁盘接近满：优先释放安全空间并停止扩大问题的任务。

第三步：检查数据库线程和锁

SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.innodb_trx\G;
SHOW ENGINE INNODB STATUS\G;

如果大量线程处于锁等待，优先找阻塞事务；如果大量线程在执行相似 SQL，转向慢查询和执行计划。

第四步：检查近期变化

生产故障常由变化触发：

发布了新 SQL；
新增或删除索引；
修改了统计信息；
调整了参数；
批量任务开始；
备份或 DDL 占用资源；
复制拓扑或流量发生切换。

没有时间线的监控只能告诉你“现在异常”，很难说明“为什么异常”。

第五步：执行最小风险恢复

恢复动作应优先选择可逆操作：

限制流量；
暂停批处理；
终止确认无害的查询；
修复连接池；
扩容磁盘；
从健康副本读取；
将备份任务迁移到从库。

高风险操作包括：

删除 binlog；
强制跳过复制事件；
修改事务持久性参数；
删除 InnoDB 文件；
使用 innodb_force_recovery；
在未验证备份的情况下重建主库。

每个动作之后都要验证：错误率、延迟、线程数、锁等待、磁盘空间和复制状态是否朝预期方向变化。

九、参数、容量、备份和监控之间的联动

这些主题不是独立的。

9.1 参数改变会影响容量

增大 max_connections 可能增加内存压力；增大排序缓冲可能增加临时空间；增大 redo 容量会增加磁盘占用；延长 binlog 保留时间会增加磁盘需求。

9.2 备份会影响性能和容量

逻辑备份会读取大量数据，增加磁盘读和网络带宽；本地备份文件会占用空间；备份快照可能使长事务或历史版本保留时间变长。

9.3 监控决定恢复是否可控

没有 binlog 增长监控，就可能在磁盘满之前不知道清理策略失效；没有恢复演练，就不知道 RTO 是否满足；没有长事务监控，就难以解释 undo 增长和备份读视图迟迟不结束。

9.4 故障处理必须区分“恢复服务”和“恢复正确性”

例如：

跳过复制错误可以让线程继续运行，但可能牺牲一致性；
删除旧 binlog 可以释放磁盘，但可能破坏从库恢复能力；
降低刷盘参数可以暂时提高吞吐，但可能扩大掉电丢失窗口；
杀掉长事务可以释放锁，但可能触发长时间回滚。

生产运维的验证标准不能只是“命令成功”或“进程变绿”，还要确认：

数据是否完整；
事务语义是否仍满足要求；
备份链是否连续；
副本是否仍可用于切换；
变更是否在重启后保持；
资源是否只是把问题推迟到了更晚的时间。

最终，可靠的 MySQL 运维建立在一条完整链路上：

\text{参数状态} \rightarrow \text{资源消耗} \rightarrow \text{日志与指标} \rightarrow \text{备份链} \rightarrow \text{恢复验证}

只有能够从这条链路中定位原因、控制风险并验证结果，数据库生产运维才不只是执行命令，而是具备可证明的恢复能力。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。