Carry の Blog
Carry の Blog
2024-01-09
目录

第六章 故障处理手册原创

# 第六章 故障处理手册

本章旨在提供一套标准化的流程和方法，用于诊断和处理 MySQL 8.0 数据库运行过程中可能遇到的常见故障。

# 6.1 故障处理原则

  1. 保持冷静，记录信息: 遇到问题时，首先记录故障现象、发生时间、错误信息、影响范围等关键信息。
  2. 优先恢复服务: 在允许的情况下，首要目标是尽快恢复数据库服务，然后再深入排查根源。
  3. 先诊断，后操作: 避免在未明确原因的情况下盲目操作，以免问题恶化。
  4. 查阅日志: 错误日志、慢查询日志、系统日志是定位问题的首要信息来源。
  5. 利用监控数据: 结合监控系统（如 Prometheus, Zabbix, PMM）的历史数据和实时数据进行分析。
  6. 由简入繁: 从最常见、最简单的原因开始排查（如网络、磁盘空间、配置错误）。
  7. 隔离问题: 尝试缩小问题范围，判断是单点问题还是集群问题，是特定 SQL 还是全局性能问题。
  8. 参考文档与社区: 查阅官方文档、知识库或相关技术社区寻求帮助。
  9. 事后复盘: 问题解决后，进行复盘总结，分析根本原因，更新 SOP 或配置，防止问题再次发生。

# 6.2 常见故障分类与处理预案

# 6.2.1 无法连接数据库

  • 现象: 客户端报错 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '...' 或 ERROR 2003 (HY000): Can't connect to MySQL server on '...' (...)。
  • 诊断步骤:
    1. 检查 MySQL 服务状态:
      • 在服务器上执行 systemctl status mysqld 或 service mysqld status (根据实际服务管理方式)。
      • 检查 mysqld 进程是否存在: ps aux | grep mysqld。
    2. 检查网络连通性:
      • 从客户端 ping <mysql_server_ip>。
      • 从客户端 telnet <mysql_server_ip> 3306 (或实际端口)。
      • 检查服务器防火墙设置 (firewall-cmd --list-all, iptables -L, ufw status)，确认 3306 端口已开放。
      • 检查服务器 SELinux 状态 (getenforce) 及相关策略 (semanage port -l | grep mysqld_port_t)。
    3. 检查 MySQL 监听地址:
      • 查看 my.cnf 中的 bind-address 参数。如果是 127.0.0.1，则只允许本地连接。如果是 0.0.0.0 或特定 IP，检查是否正确。
      • 在服务器上执行 netstat -tulnp | grep 3306 确认 mysqld 正在监听预期的 IP 和端口。
    4. 检查 Socket 文件 (本地连接):
      • 确认 my.cnf 中 [client] 和 [mysqld] 段的 socket 参数路径一致。
      • 确认该 socket 文件 (/var/lib/mysql/mysql.sock 或其他路径) 存在且 mysql 用户有权限访问其所在目录。
    5. 检查连接数限制:
      • 登录 MySQL (如果可能) 执行 SHOW GLOBAL STATUS LIKE 'Threads_connected'; 和 SHOW VARIABLES LIKE 'max_connections';。如果 Threads_connected 接近 max_connections，则可能是连接数耗尽。
    6. 检查错误日志: 查看 MySQL 错误日志 (log_error 指定的文件) 获取更详细的错误信息。
  • 可能原因与解决:
    • MySQL 服务未运行 -> 启动服务。
    • 网络不通/防火墙/SELinux 阻止 -> 解决网络问题，调整防火墙/SELinux 规则。
    • bind-address 配置错误 -> 修改 my.cnf 并重启服务。
    • Socket 文件路径不匹配或权限问题 -> 修正配置或权限。
    • 连接数耗尽 -> 优化应用连接池、增加 max_connections (需评估资源)、排查慢查询或连接泄漏。
    • MySQL 启动失败 -> 查看错误日志定位启动失败原因。

# 6.2.2 数据库性能急剧下降/响应缓慢

  • 现象: 应用响应变慢，查询执行时间显著增加，CPU/IO 资源使用率飙升。
  • 诊断步骤:
    1. 识别瓶颈资源:
      • 使用系统监控工具 (top, htop, iostat, vmstat) 查看 CPU、内存 (Swap)、磁盘 I/O (%iowait)、网络的使用情况。
    2. 检查当前活动线程:
      • 登录 MySQL 执行 SHOW FULL PROCESSLIST;。关注 State 列（如 query end, sending data, copying to tmp table, locked 等）和 Time 列（执行时间长的查询）。
      • 使用 sys.processlist 或 sys.session 视图获取更详细信息。
    3. 检查锁等待:
      • 执行 SHOW ENGINE INNODB STATUS\G; 查看 LATEST DETECTED DEADLOCK 和 TRANSACTIONS 部分的锁信息。
      • 使用 sys.innodb_lock_waits 视图查看当前锁等待。
    4. 分析慢查询日志:
      • 检查慢查询日志 (slow_query_log_file)，找出耗时最长、执行频率最高的查询。
      • 使用 EXPLAIN 分析这些查询的执行计划，判断是否缺少索引或索引选择不当。
    5. 检查 Performance Schema / Sys Schema:
      • 查询 sys.statement_analysis 按 SQL 指纹查看语句的平均耗时、扫描行数、临时表使用等。
      • 查询 sys.host_summary 或 sys.user_summary 判断是否特定来源或用户导致高负载。
    6. 检查 InnoDB Buffer Pool 状态:
      • SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free'; (非零表示 Buffer Pool 压力大)
      • 计算 Buffer Pool 命中率。
    7. 检查配置参数: 确认关键性能参数（innodb_buffer_pool_size, tmp_table_size, max_heap_table_size 等）是否合理。
  • 可能原因与解决:
    • CPU 瓶颈:
      • 原因: 复杂计算、大量排序、无索引查询全表扫描。
      • 解决: 优化 SQL、添加索引、升级 CPU、考虑读写分离。
    • 内存瓶颈 (Swap 升高):
      • 原因: innodb_buffer_pool_size 设置过大、连接数过多导致线程内存消耗大、其他进程占用内存。
      • 解决: 调整 Buffer Pool 大小、优化连接数、增加物理内存。
    • 磁盘 I/O 瓶颈 (%iowait 高):
      • 原因: Buffer Pool 不足导致频繁物理读、大量写操作（Redo/Binlog/数据）、未使用独立表空间、磁盘性能差。
      • 解决: 增加 Buffer Pool、优化 SQL 减少扫描、使用 SSD/NVMe、分离数据/日志目录到不同磁盘、调整 innodb_io_capacity。
    • 锁竞争:
      • 原因: 长时间运行的事务、热点行更新、未使用合适的索引导致行锁升级为表锁。
      • 解决: 优化事务逻辑（拆分大事务）、优化 SQL 和索引减少锁范围、识别并 KILL 阻塞的会话。
    • 慢查询:
      • 原因: 缺少索引、索引失效、查询逻辑复杂、数据量过大。
      • 解决: 添加/优化索引、改写 SQL、分区表、定期归档数据。
    • 连接数过多:
      • 原因: 应用连接池配置不当、连接泄漏。
      • 解决: 调整应用连接池、排查应用代码。

# 6.2.3 MySQL 服务崩溃/重启

  • 现象: MySQL 服务意外停止或自动重启。
  • 诊断步骤:
    1. 检查错误日志: 这是最重要的步骤。仔细查看 MySQL 错误日志中服务停止前的最后几条日志，通常会包含崩溃的原因或信号信息 (Signal)。
    2. 检查系统日志: 查看 /var/log/messages 或 journalctl，检查是否有 OOM (Out Of Memory) Killer 杀掉 mysqld 进程的记录。
    3. 检查磁盘空间: 确认数据目录、日志目录所在分区是否有足够空间。
    4. 检查硬件问题: 关注系统日志中是否有内存、磁盘等硬件错误报告。
    5. 检查配置更改: 回忆最近是否有配置 (my.cnf) 或系统参数的更改。
    6. 复现问题 (如果可能): 在测试环境尝试复现问题场景。
  • 可能原因与解决:
    • OOM Killer:
      • 原因: MySQL 或其他进程内存使用超出系统限制。
      • 解决: 降低 innodb_buffer_pool_size 或其他内存相关参数、限制连接数、增加服务器内存、优化其他耗内存进程。
    • Bug: MySQL 或系统库的 Bug 可能导致崩溃。
      • 解决: 查看错误日志中的堆栈跟踪信息，搜索官方 Bug 库，考虑升级到更新的稳定版本。
    • 数据损坏: InnoDB 数据文件损坏可能导致启动失败或运行中崩溃。
      • 解决: 尝试使用 innodb_force_recovery (从 1 到 6 逐级尝试) 启动，导出数据后重建实例。参考 6.2.5 数据损坏。
    • 配置错误: 不合理的参数配置可能导致不稳定。
      • 解决: 回滚配置更改，或参考官方文档检查参数设置。
    • 硬件故障: 内存条、磁盘故障。
      • 解决: 更换故障硬件。

# 6.2.4 复制错误 (ReplicaSet)

  • 现象: 从库 SHOW SLAVE STATUS\G 显示 Slave_IO_Running: No 或 Slave_SQL_Running: No，Seconds_Behind_Master 持续增大或为 NULL。
  • 诊断步骤:
    1. 查看 SHOW SLAVE STATUS\G: 重点关注 Last_IO_Error, Last_SQL_Error, Slave_IO_State, Slave_SQL_Running_State。
    2. 检查网络: 确认主从网络通畅，端口开放。
    3. 检查主库状态: 确认主库运行正常，Binlog 功能正常。
    4. 检查从库错误日志: 获取更详细的错误信息。
    5. 检查磁盘空间: 确认从库中继日志 (Relay Log) 目录空间充足。
    6. 检查 GTID 信息 (如果使用): 对比主从 Retrieved_Gtid_Set 和 Executed_Gtid_Set。
  • 常见错误与解决:
    • IO 线程错误 (Slave_IO_Running: No):
      • Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet...': 增大主从库的 max_allowed_packet。
      • Got fatal error 1236 ... 'Could not find first log file name in binary log index file': 主库 Binlog 可能被清理，从库需要重新找点或重做。
      • Authentication failure: 检查复制用户名、密码、主机是否正确，权限是否足够。
      • 网络超时/连接断开: 检查网络稳定性。
    • SQL 线程错误 (Slave_SQL_Running: No):
      • Error 'Duplicate entry '...' for key ...' (errno: 1062): 从库数据被意外写入导致主键冲突。
        • 解决: (谨慎操作) 跳过错误 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; (非 GTID) 或使用 GTID 跳过事务。根本解决需要排查从库写操作来源，或重新同步数据。
      • Error 'Could not execute ... event on table ...; Can't find record in '...' (errno: 1032): 主库删除了记录，从库应用 UPDATE 或 DELETE 时找不到。
        • 解决: 同上，跳过错误或重新同步。通常表明主从数据已不一致。
      • Error ... table ... doesn't exist ...: 主库执行了涉及某表的 DDL 或 DML，但从库上该表不存在或结构不同。
        • 解决: 确保主从表结构一致，可能需要重新同步。
    • 延迟增大 (Seconds_Behind_Master 持续增加):
      • 原因: 从库应用 Binlog 速度慢（单线程瓶颈、从库 I/O 差、大事务）。
      • 解决: 开启并行复制 (slave_parallel_workers > 0, slave_parallel_type = LOGICAL_CLOCK)、优化从库硬件、优化主库事务。

# 6.2.5 数据损坏

  • 现象: MySQL 无法启动，错误日志报告 InnoDB 损坏；查询特定表报错；备份失败。
  • 诊断步骤:
    1. 检查错误日志: 查找具体的损坏信息和涉及的文件。
    2. 尝试启动: 如果无法启动，使用 innodb_force_recovery 参数尝试启动。在 my.cnf 中添加 innodb_force_recovery = 1，然后尝试启动。如果不行，逐级增加到 6。
      • 重要: innodb_force_recovery > 0 时，数据库处于只读模式，且禁止 DML 操作。目的是尽可能导出数据。
    3. 检查表: 如果能启动，使用 CHECK TABLE table_name; 检查特定表。
  • 处理预案:
    1. 最高优先级：备份！: 在进行任何修复操作前，如果实例能启动（即使是 force_recovery 模式），立即尝试使用 mysqldump 导出所有数据或损坏表的数据。
    2. 使用 innodb_force_recovery 导出数据:
      • 设置合适的 innodb_force_recovery 值（从 1 开始尝试）并启动 MySQL。
      • 使用 mysqldump 导出数据。
      • 停止 MySQL，移除 innodb_force_recovery 参数。
    3. 重建实例或恢复备份:
      • 最佳方案: 从最近的可用备份（物理备份如 Xtrabackup 或逻辑备份 mysqldump）恢复到一个新的实例或清空当前数据目录后恢复。
      • 如果只有导出的数据: 停止 MySQL，删除数据目录下的所有内容（请务必先确认数据已导出或有其他备份！），重新初始化数据库 (mysqld --initialize)，启动 MySQL，然后导入之前导出的 dump.sql 文件。
    4. 预防:
      • 使用可靠的硬件和文件系统。
      • 确保正常关机 (shutdown 或 systemctl stop)。
      • 定期执行备份并验证。
      • 监控磁盘健康状况。

# 6.3 紧急预案与升级流程

  • 主库宕机 (ReplicaSet):
    1. 确认主库无法恢复。
    2. 选择一个数据最新、延迟最小的从库作为新的主库。
    3. 确保新主库已应用完所有 Relay Log (SHOW SLAVE STATUS\G 中 Relay_Log_Pos == Exec_Master_Log_Pos)。
    4. 在新主库上执行 RESET SLAVE ALL; (如果它之前是从库)，并关闭 read_only 和 super_read_only。
    5. 将应用流量切换到新主库。
    6. 让其他从库执行 CHANGE MASTER TO MASTER_HOST='new_master_ip', ...; 指向新主库。
    7. (可选) 使用 MHA 或 Orchestrator 实现自动故障切换。
  • InnoDB Cluster 故障: 参考官方文档关于 InnoDB Cluster 的故障排查和恢复章节，通常涉及检查 Group Replication 状态、网络、成员状态等。
  • 数据库升级:
    1. 详细阅读目标版本的 Release Notes，了解不兼容变更和升级要求。
    2. 在与生产环境一致的测试环境中进行充分的升级测试。
    3. 备份！执行完整的数据库备份。
    4. 按照官方文档推荐的升级路径执行（通常是原地升级或逻辑导出导入）。
    5. 升级后进行功能和性能验证。

# 下一步

#MySQL#数据库#故障处理
上次更新: 4/24/2025

← 第七章 安全与权限管理→

最近更新
01
tidb fast ddl
04-04
02
TiDB配置文件调优 原创
04-03
03
如何移除TiDB中的表分区 原创
04-03
更多文章>
Theme by Vdoing