DevOps知识中心
DevOps知识中心

快速排查常见 MySQL 复制故障

借助这份实用指南，快速解决常见的 MySQL 复制故障。学习如何解读 `SHOW REPLICA STATUS` 的错误代码、检查 MySQL 错误日志，并了解二进制日志（binary logs）的用途。本文提供了可操作的步骤和最佳实践，用于诊断重复条目、binlog 文件丢失和数据分歧等问题，帮助您维护健康的复制环境。

41 浏览量

快速排查常见 MySQL 复制故障

MySQL 复制是一项强大功能，可让您维护数据库的多个副本，这对于高可用性、读扩展和灾难恢复至关重要。但是，设置和维护复制有时可能会导致意外故障。本指南提供了一种实用的方法，通过专注于理解错误代码和检查相关日志来快速诊断和解决常见的 MySQL 复制问题。

当复制中断时，可能会停止关键操作，因此系统化的故障排除过程至关重要。我们将涵盖最常见的问题，让您能够高效地识别根本原因并实施解决方案。通过理解症状并知道在哪里查找线索，您可以最大限度地减少停机时间并确保您的复制设置保持健康。

理解 MySQL 复制基础知识

在深入排查故障之前，快速回顾一下 MySQL 复制的工作原理很有益。在典型的主-从（或主-副本）设置中：

  • 主服务器上的二进制日志 (Binlog): 主服务器在其二进制日志文件中记录所有更改数据的事件。
  • 副本上的复制线程: 副本服务器有两个线程：
    • I/O 线程: 连接到主服务器，从主服务器的二进制日志读取事件，并将其写入自己的中继日志。
    • SQL 线程: 从中继日志读取事件并在副本数据库上执行它们。

复制故障通常发生在 I/O 线程无法获取事件，或者 SQL 线程无法应用它们时。

常见的复制错误代码及其含义

MySQL 提供了错误代码，可深入了解复制问题。SHOW REPLICA STATUS（在旧版本上为 SHOW SLAVE STATUS）命令是检查复制状态的主要工具。

SHOW REPLICA STATUS\G

查找以下关键字段：

  • Replica_IO_Running: 应为 Yes。
  • Replica_SQL_Running: 应为 Yes。
  • Last_IO_Errno 和 Last_IO_Error: 与 I/O 线程相关的错误。
  • Last_SQL_Errno 和 Last_SQL_Error: 与 SQL 线程相关的错误。
  • Seconds_Behind_Source: 指示副本落后于主服务器的延迟。

以下是一些常见的错误号及其典型原因：

错误 1062: 重复条目

  • Last_SQL_Errno: 1062
  • Last_SQL_Error: Error 'Duplicate entry '...' for key '...' on query. Default database: '...'.

原因: SQL 线程正在尝试应用主服务器上的一个事件，该事件导致副本上出现重复键违反。这通常发生在副本落后并处理了可能创建了相同数据的其他写入，或者在副本上手动引入了不一致性时。

解决方案:
1. 识别有问题的查询: 错误消息通常包含失败的查询。
2. 跳过事务（谨慎操作）: 如果您确定跳过是安全的，可以使用 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 然后是 START SLAVE SQL_THREAD;（或 START REPLICA SQL_THREAD;）。警告: 跳过事务可能导致数据不一致。在继续之前，请了解其影响。
3. 调查数据不一致: 如果无法跳过，您可能需要手动协调数据或调查重复发生的原因。如果副本严重不同步，这可能涉及从特定点重置复制。

错误 1236: 在二进制日志索引中找不到第一个日志文件名

  • Last_IO_Errno: 1236
  • Last_IO_Error: Error 'Could not find first log file name in binary log index' when trying to read event from the http client side...

原因: I/O 线程无法定位主服务器指定的二进制日志文件。这通常意味着在副本能够读取它们之前，二进制日志文件已从主服务器中清除，或者副本正在尝试使用不再存在的 binlog 文件进行连接。

解决方案:
1. 检查主服务器的 binlog 保留: 确保主服务器上的 expire_logs_days（或 binlog_expire_logs_seconds）设置为足够长的时间以供副本赶上的值。
2. 重新初始化副本: 最常见的解决方案是停止复制，重置副本的主数据，并从主服务器的最新备份或快照重新初始化它，确保新的主日志文件和位置设置正确。

错误 1577: 需要主服务器的二进制日志位置

  • Last_IO_Errno: 1577
  • Last_IO_Error: Error: The primary's binary log position is required for this operation.

原因: 当您尝试在未指定副本上的正确二进制日志文件名和位置的情况下启动复制时，通常会发生此错误。这可能在某些配置更改或手动干预后发生。

解决方案:
1. 验证 CHANGE MASTER TO（或 CHANGE REPLICATION SOURCE TO）命令: 确保在设置复制时已正确指定 MASTER_LOG_FILE 和 MASTER_LOG_POS（或 SOURCE_LOG_FILE 和 SOURCE_LOG_POS）。
2. 重置并重新配置: 停止复制，重置副本状态，并使用从主服务器获取的正确参数重新应用 CHANGE MASTER TO 命令。

错误 1032: 在 '...' 表中找不到记录

  • Last_SQL_Errno: 1032
  • Last_SQL_Error: Error 'Can't find record in '...' table' on query. Default database: '...'.

原因: 与错误 1062 类似，这表明 SQL 线程正在尝试对副本上不存在的记录执行 UPDATE 或 DELETE 操作。这表示数据不一致，通常是由于之前跳过的事务或手动修改引起的。

解决方案:
1. 识别查询和表: 错误消息提供了详细信息。
2. 调查数据漂移: 比较主服务器和副本上受影响表的
状态。
3. 跳过（极度谨慎）: 如果丢失的记录无关紧要或已通过其他方式处理，您可以使用 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 和 START REPLICA SQL_THREAD; 跳过事务。
4. 手动数据更正: 在关键情况下，您可能需要手动插入丢失的记录或重新同步表/数据库。

检查复制日志

除了 SHOW REPLICA STATUS，MySQL 错误日志和二进制日志本身也是宝贵的资源。

MySQL 错误日志

通常位于 /var/log/mysql/error.log（或其他类似位置，取决于您的操作系统和配置），此日志包含有关 MySQL 服务器遇到的错误的详细信息，包括与复制线程相关的错误。

查找内容:
* 错误的详细堆栈跟踪。
* 主服务器和副本之间的连接问题。
* 超时和网络相关问题。

主服务器的二进制日志

虽然副本的中继日志对 SQL 线程至关重要，但检查主服务器的二进制日志有时有助于理解导致故障的事件序列。您可以使用 mysqlbinlog 工具来完成此目的。

示例：查看特定二进制日志文件中的事件：

mysqlbinlog /path/to/mysql-bin.000001

示例：查看特定时间或位置附近的事件：

mysqlbinlog --start-datetime="2023-10-27 10:00:00" --stop-datetime="2023-10-27 11:00:00" /path/to/mysql-bin.000001

用例:
* 理解导致副本 SQL 错误的具体事务。
* 验证正在写入的事件的一致性。

通用故障排除步骤

当复制中断时，请遵循以下步骤：

  1. 检查 SHOW REPLICA STATUS: 始终从这里开始。这是获取问题摘要的最快方法。
  2. 检查 Last_IO_Error 和 Last_SQL_Error: 理解具体的错误代码和消息。
  3. 查阅 MySQL 错误日志: 查找服务器端更详细的上下文。
  4. 验证网络连接: 确保副本可以连接到主服务器（防火墙、DNS）。
  5. 检查用户权限: 主服务器上的复制用户必须具有必要的权限（REPLICATION SLAVE）。
  6. 确保主服务器已配置复制: 验证 log_bin 已启用且 server_id 是唯一的。
  7. 检查副本的 read_only 设置: 如果副本上启用了 read_only，除非满足特定条件或临时禁用，否则它不会应用主服务器的写入。

预防故障的最佳实践

  • 监控复制延迟: 使用监控工具在 Seconds_Behind_Source 过度增长时发出警报。
  • 定期备份: 维护主服务器的一致备份，以便能够快速重新初始化副本。
  • 足够的 Binlog 保留: 在主服务器上适当地配置 expire_logs_days。
  • 唯一的 server_id: 确保复制拓扑中的每个服务器都有唯一的 server_id。
  • 测试故障转移程序: 定期练习切换角色，以确保您的复制设置是健壮的。

结论

排查 MySQL 复制故障需要系统化的方法。通过理解常见的错误代码，知道如何解释 SHOW REPLICA STATUS 的输出，并利用 MySQL 的错误日志和 mysqlbinlog 工具，您可以高效地诊断和解决大多数复制问题。积极的监控和遵循最佳实践将进一步最大限度地减少这些问题的发生，确保数据库环境的稳定性和可用性。