第五章 监控与日常维护

有效的监控和规范的日常维护是保障 MySQL 数据库稳定、高效运行的关键。本章将介绍核心监控指标、常用监控工具和方法，以及必要的日常维护任务。

5.1 监控重要性与目标

• 主动发现问题: 及时发现潜在的性能瓶颈、资源耗尽、错误或异常行为。
• 性能分析与优化: 收集数据以了解数据库负载模式，为参数调优、索引优化和 SQL 优化提供依据。
• 容量规划: 监控资源使用趋势（CPU、内存、磁盘、连接数），预测未来需求，提前进行扩容规划。
• 故障诊断: 在出现问题时，历史监控数据是快速定位和解决问题的宝贵信息。
• SLA 保障: 确保数据库服务满足预定的服务水平协议要求。

5.2 核心监控指标

监控应覆盖系统和 MySQL 两个层面。

5.2.1 系统层面指标

• CPU:
  • Usage (%): 整体 CPU 使用率，持续过高（如 > 80%）可能表示 CPU 瓶颈。
  • Load Average: 系统平均负载，反映等待 CPU 的进程队列长度。
  • iowait (%): CPU 等待 I/O 操作完成的时间比例，过高表示磁盘 I/O 可能是瓶颈。
• 内存:
  • Used / Free / Available: 物理内存使用情况。
  • Swap Usage: Swap 交换空间使用情况，频繁或大量的 Swap 使用通常表示内存不足，严重影响性能。
• 磁盘 I/O:
  • IOPS (Input/Output Operations Per Second): 每秒读写操作次数。
  • Throughput (MB/s): 每秒读写数据量。
  • Latency (ms): I/O 操作平均延迟。
  • Utilization (%): 磁盘繁忙程度。
• 网络:
  • Bandwidth Usage (Mbps): 网络带宽使用情况。
  • Packet Loss (%): 网络丢包率。
  • Latency (ms): 网络延迟。
• 磁盘空间:
  • Usage (%): 各挂载点（特别是数据目录、日志目录、备份目录）的磁盘空间使用率，需设置告警阈值（如 85%）。

5.2.2 MySQL 层面指标 (通过 SHOW GLOBAL STATUS LIKE '...')

• 连接与线程:
  • Threads_connected: 当前打开的连接数。
  • Threads_running: 正在执行查询的线程数。
  • Max_used_connections: MySQL 启动以来同时存在的最大连接数。
  • Aborted_connects: 失败的连接尝试次数。
  • Connection_errors_%: 连接错误相关的计数器。
• QPS / TPS (估算):
  • Queries: 服务器执行的总语句数 (包括 COM_ 和非 COM_ 语句)。
  • Com_select, Com_insert, Com_update, Com_delete: 各类 DML 操作计数器。
  • QPS (每秒查询数) ≈ (Queries - Queries_last) / interval
  • TPS (每秒事务数) ≈ (Com_commit + Com_rollback - Com_commit_last - Com_rollback_last) / interval
• InnoDB Buffer Pool:
  • Innodb_buffer_pool_wait_free: 等待空闲缓冲页的次数，非零表示 Buffer Pool 可能不足或 I/O 压力大。
  • Innodb_buffer_pool_read_requests: 逻辑读请求次数。
  • Innodb_buffer_pool_reads: 物理读（从磁盘读取）次数。
  • 命中率 ≈ (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests (应尽可能高，如 > 99%)。
• InnoDB 日志:
  • Innodb_log_waits: 等待日志缓冲区刷新的次数，非零表示 innodb_log_buffer_size 可能偏小或 I/O 瓶颈。
  • Innodb_os_log_written: 写入 Redo Log 的总字节数。
• 锁:
  • Innodb_row_lock_waits: 行锁等待次数。
  • Innodb_row_lock_time_avg: 平均行锁等待时间。
  • Table_locks_waited: 表锁等待次数。
• 临时表:
  • Created_tmp_disk_tables: 在磁盘上创建的临时表数量。
  • Created_tmp_tables: 在内存中创建的临时表数量。
  • 磁盘临时表比例过高 (Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables)) 可能表示 tmp_table_size 或 max_heap_table_size 不足，或查询需要优化。
• 复制 (从库): (参考 )
  • Slave_IO_Running, Slave_SQL_Running: 复制线程状态。
  • Seconds_Behind_Master: 复制延迟（秒）。
• 慢查询:
  • Slow_queries: 慢查询计数器。

5.3 监控工具与方法

5.3.1 MySQL 内置工具

• SHOW [GLOBAL] STATUS;: 查看服务器状态变量（计数器）。
• SHOW [GLOBAL] VARIABLES;: 查看服务器配置参数。
• SHOW ENGINE INNODB STATUS\G;: 提供 InnoDB 详细状态信息，包括事务、锁、Buffer Pool、日志等，是诊断 InnoDB 问题的重要工具。
• SHOW PROCESSLIST; / information_schema.PROCESSLIST: 查看当前正在执行的线程、状态、SQL 语句。
• Performance Schema:
  • MySQL 内置的性能数据收集引擎，提供非常详细的底层性能事件信息。
  • 默认部分启用，可通过 my.cnf 配置 performance_schema=ON 及相关 setup_instruments, setup_consumers 来控制收集范围。
  • 关键表举例:
    • events_statements_summary_by_digest: 按 SQL 指纹聚合的语句性能统计（执行次数、耗时、锁等待、扫描行数等）。
    • events_waits_summary_global_by_event_name: 全局等待事件统计。
    • memory_summary_global_by_event_name: 内存使用统计。
• Sys Schema:
  • 基于 Performance Schema 的视图库，用更友好的方式展示性能数据。
  • 需要 Performance Schema 启用。
  • 常用视图举例:
    • sys.statement_analysis: 类似 events_statements_summary_by_digest，但更易读。
    • sys.host_summary: 按客户端主机聚合的性能信息。
    • sys.user_summary: 按用户聚合的性能信息。
    • sys.schema_table_statistics_with_buffer: 带 Buffer Pool 信息的表统计。
    • sys.innodb_lock_waits: 当前的 InnoDB 锁等待信息。
• 日志文件:
  • 错误日志 (Error Log): 记录启动、关闭、运行期间的错误和警告信息，必须重点关注。
  • 慢查询日志 (Slow Query Log): 记录执行时间超过 long_query_time 的查询，是 SQL 优化的重要来源。
  • 通用查询日志 (General Query Log): 记录所有连接和执行的语句，开销很大，仅在调试时短期开启。

5.3.2 第三方监控系统 (推荐)

手动检查内置工具效率低下，推荐使用专业的监控系统实现自动化、可视化和告警。

• Prometheus + mysqld_exporter + Grafana:
  • Prometheus: 开源时序数据库和监控系统。
  • mysqld_exporter: 从 MySQL 收集指标并暴露给 Prometheus。
  • Grafana: 开源可视化平台，用于展示 Prometheus 数据（提供丰富的 MySQL Dashboard 模板）。
  • 这是目前非常流行和强大的开源监控组合。
• Zabbix:
  • 成熟的企业级开源监控解决方案，提供 MySQL 监控模板。
• Percona Monitoring and Management (PMM):
  • Percona 公司提供的专门针对 MySQL, MongoDB, PostgreSQL 的开源监控管理平台，功能强大，集成了 Grafana 和 QAN (Query Analytics)。

选择建议: 对于大多数场景，Prometheus + Grafana 组合是灵活且强大的选择。PMM 则提供了更集成的 MySQL 专项监控体验。

5.4 日常维护任务

5.4.1 定期检查 (建议每日/每周)

• 错误日志检查: 每日检查 MySQL 错误日志，关注 [ERROR] 和 [Warning] 级别的日志，及时处理发现的问题。
• 慢查询日志分析: 定期（如每日）分析慢查询日志，找出执行效率低的 SQL 语句，结合 EXPLAIN 进行优化。可以使用 pt-query-digest 等工具辅助分析。
• 复制状态检查: 监控主从复制状态 (Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master)，确保复制正常且延迟在可接受范围内。
• 磁盘空间检查: 监控数据、日志、备份目录的磁盘使用率，防止空间耗尽。
• 备份有效性验证: 定期（如每季度）进行恢复演练，从备份中恢复数据到测试环境，确保备份可用。
• 系统资源检查: 关注服务器 CPU、内存、I/O 等资源使用情况，判断是否存在瓶颈。

5.4.2 优化任务 (按需)

• 索引优化:
  • 根据慢查询日志、Performance Schema/Sys Schema 分析结果，识别缺少索引或索引效率低下的查询。
  • 使用 EXPLAIN 分析查询执行计划。
  • 添加、修改或删除索引。注意在线 DDL 操作对生产环境的影响 (MySQL 8.0 支持大部分 DDL 的 Online 操作)。
• 参数调优:
  • 根据长期监控数据和业务负载变化，审视并调整 my.cnf 中的关键参数（如 innodb_buffer_pool_size, max_connections 等）。参数调整需谨慎，建议在测试环境验证后应用于生产。
• 表碎片整理:
  • 对于频繁进行 DELETE 或 UPDATE（特别是变长字段）的 InnoDB 表，可能会产生碎片。
  • 通过 SHOW TABLE STATUS LIKE 'table_name'\G 查看 Data_free。
  • 使用 OPTIMIZE TABLE table_name; 或 ALTER TABLE table_name ENGINE=InnoDB; (空操作重建) 来整理碎片。
  • 注意: OPTIMIZE TABLE 会锁表（取决于 MySQL 版本和操作类型），应在业务低峰期执行，且对于非常大的表可能耗时较长。

5.4.3 安全检查 (建议每月/每季度)

• 用户权限审计: 定期审查 MySQL 用户及其权限，移除不再需要的用户，遵循最小权限原则。检查 mysql.user 表。
• 异常登录检查: 检查错误日志或审计日志（如果启用），关注失败的登录尝试和来自异常 IP 的连接。
• 密码策略: 确保关键用户（如 root）使用强密码，并考虑定期轮换。

5.5 备份策略简述

虽然详细的备份与恢复将在后续章节介绍，但日常维护中必须确保备份策略有效执行。

• 备份频率: 根据业务 RPO (Recovery Point Objective) 要求确定备份频率（如每日全备 + 每小时增量/差异）。
• 备份类型:
  • 逻辑备份: mysqldump (适合数据量小、需要跨版本恢复、需要人类可读的场景)。
  • 物理备份: Xtrabackup (推荐，速度快、非阻塞、支持增量备份，适合大数据量)。
• 备份存储: 备份文件应存储在与 MySQL 服务器物理隔离的存储介质上。
• 备份验证: 定期恢复测试是验证备份有效性的唯一可靠方法。

下一步

• 故障处理手册
• 安全与权限管理