精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

快速排查常见MySQL复制故障

通过本实用指南快速解决常见MySQL复制故障。学习解读`SHOW REPLICA STATUS`中的错误代码、检查MySQL错误日志，并理解二进制日志的作用。本文提供可操作的步骤和最佳实践，用于诊断重复条目、丢失binlog文件、数据不一致等问题，帮助您维护健康的复制环境。

快速排查常见MySQL复制故障

当你能区分两个问题：副本能否从源获取事件，以及能否应用已获取的事件，MySQL复制故障就更容易修复。这是两种不同的故障。网络问题、二进制日志缺失、密码错误或主机授权不当通常会停止I/O线程。重复键、行缺失、DDL不匹配或数据漂移通常会停止SQL线程。

从状态输出开始。在现代MySQL上：

SHOW REPLICA STATUS\G

在旧版本上：

SHOW SLAVE STATUS\G

使用你的服务器支持的命令。新版本输出使用诸如Replica_IO_Running、Replica_SQL_Running和Seconds_Behind_Source等名称。旧版本输出使用Slave_IO_Running、Slave_SQL_Running和Seconds_Behind_Master。

首先关注：

  • Replica_IO_Running：副本是否连接并读取源二进制日志。
  • Replica_SQL_Running：副本是否应用中继日志事件。
  • Last_IO_Errno和Last_IO_Error：获取失败的原因。
  • Last_SQL_Errno和Last_SQL_Error：应用失败的原因。
  • Relay_Master_Log_File、Exec_Master_Log_Pos或更新的源位置字段：副本在流中的位置。

不要直接跳到修复。先将完整的状态输出复制到事件记录中。一旦运行RESET REPLICA、跳过事务或重新指向副本，一些最佳证据就会消失。

如果I/O线程已停止

当Replica_IO_Running为No时，副本未能成功从源读取。SQL线程可能仍会继续应用旧的中继日志事件一段时间，但最终会耗尽。

常见原因包括：

  • 源主机或端口错误。
  • 防火墙、安全组或路由规则阻止连接。
  • 复制用户密码错误。
  • 复制用户允许的主机与实际副本使用的主机不同。
  • 源上禁用了二进制日志。
  • 源已清除副本请求的二进制日志文件。
  • TLS设置更改，副本无法再认证。

从副本主机测试：

mysql -h source-db.example.com -u repl_user -p

如果直接登录失败，复制也会失败。在源上检查账户：

SHOW GRANTS FOR 'repl_user'@'replica_host_or_ip';

账户需要REPLICATION SLAVE权限。在MySQL授权中，权限名称仍使用"SLAVE"。

同时检查二进制日志是否启用：

SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

在新版本上，可能可以使用SHOW BINARY LOG STATUS。要点相同：源必须有二进制日志，且请求的文件必须仍然存在。

错误1236：二进制日志缺失或不可读

Last_IO_Errno: 1236是表示副本请求的二进制日志文件或位置源无法提供的错误之一。具体消息可能不同。它可能说找不到第一个日志文件、无法读取日志事件，或读取时源关闭了连接。

最常见的操作情况很简单：副本宕机时间过长，源清除了它需要的二进制日志。

检查源上剩余的日志：

SHOW BINARY LOGS;

然后将该列表与副本状态中命名的文件进行比较。如果副本需要mysql-bin.000120而源现在从mysql-bin.000140开始，副本无法从二进制日志追赶。

你有三个现实的选择：

  • 从源的新备份恢复或重建副本。
  • 如果流程支持，使用另一个仍有所需数据的副本作为克隆源。
  • 如果使用GTID且缺失的事务存在于其他地方，从可提供它们的有效源重新配置。

不要为了启动复制而猜测一个更新的日志位置。那会创建一个缺少事务的副本。它可能看起来健康，同时悄悄返回错误数据。

恢复后，如果磁盘容量允许，增加二进制日志保留时间：

[mysqld]
binlog_expire_logs_seconds=604800

该示例大约为7天。根据副本在维护或事件期间可能离线的时间选择值。

如果SQL线程已停止

当Replica_SQL_Running为No时，副本已获取事件但无法应用一个。这通常是数据一致性问题，而非连接问题。

阅读完整的Last_SQL_Error。它通常会告诉你表、键、失败操作，有时还有源日志位置。然后在更改任何内容之前检查源和副本上的相关行。

对于已知二进制日志位置附近失败的事件，mysqlbinlog可以显示该事件：

mysqlbinlog --start-position=123456 --stop-position=124500 /var/lib/mysql/mysql-bin.000321

如果源二进制日志不在本地主机上，使用远程选项或检查复制的日志文件。注意基于行的事件：它们可能需要解码选项和表元数据才能可读。

错误1062：重复条目

Last_SQL_Errno: 1062表示副本尝试插入或更新一行，但遇到了已存在的唯一键。

典型原因包括：

  • 有人直接写入副本。
  • 副本从错误的快照初始化。
  • 跳过了之前的复制错误。
  • 在多源或主动-主动设计中自增设置错误。
  • 应用程序错误地写入两个可写服务器。

诱人的修复是：

STOP REPLICA;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START REPLICA;

旧语法使用STOP SLAVE和START SLAVE。对于可丢弃的报告副本，在确认该行不重要后，这可能可以接受。但对于可能稍后提升的副本来说很危险。跳过意味着副本不再具有与源相同的事务历史。

更安全的过程是：

  1. 识别冲突的表和键。
  2. 比较源和副本上的行。
  3. 决定是删除、更新副本行，还是重建副本。
  4. 记录决定，因为现在这是一个数据一致性事件。

如果副本用于故障转移，重建通常比手动修补几个未知差异更干净。

错误1032：找不到记录

Last_SQL_Errno: 1032通常表示副本尝试更新或删除本地不存在的行。这是许多重复键问题的镜像。源有一行，副本没有。

常见原因包括：

  • 行在副本上被手动删除。
  • 之前的事务被跳过。
  • 初始转储遗漏了数据。
  • 复制过滤器排除了早期的写入。

不要假设缺失的行无害。如果UPDATE找不到行，副本已经与源不同。比较受影响键周围的计数和样本数据。如果表很小，重新加载表可能合理。如果表很大或关键，使用一致性工具或重建副本。

认证和主机授权问题

密码轮换或网络更改后一个非常常见的失败是看起来像访问被拒绝的I/O错误：

Access denied for user 'repl_user'@'10.0.2.15'

错误中的主机是MySQL看到的主机。它可能与你期望的主机名不匹配，尤其是在使用NAT、代理或容器网络时。

在源上检查用户：

SELECT user, host, plugin FROM mysql.user WHERE user = 'repl_user';

如果副本从10.0.2.15连接，'repl_user'@'replica.internal'的授权可能不匹配，除非名称解析和授权对齐。优先使用与网络设计匹配的显式主机模式。

如果插件不同，旧客户端可能无法使用新认证插件的账户。通常更新客户端比削弱认证更好，但在混合版本环境中，你可能需要计划兼容性更改。

中继日志问题

有时源连接正常，但副本有中继日志损坏或本地磁盘问题。错误可能提到中继日志读取失败、截断事件或中继日志位置。

首先检查磁盘健康和可用空间。磁盘满可能导致几个奇怪的复制症状：

df -h
iostat -xz 1

如果中继日志损坏但源仍有需要的二进制日志，通常可以重置中继日志并让副本重新获取。确切命令取决于版本和拓扑。不要随意运行重置命令；确认你知道已执行的源日志文件和位置。

在许多情况下，这种问题表明副本主机存在底层存储问题。在信任副本之前先修复它。

复制延迟不总是故障

当两个线程都在运行时，Seconds_Behind_Source可能很高。这意味着复制存活但落后。将延迟与停止的线程区别对待。

检查：

  • 副本磁盘是否饱和？
  • 源是否产生写入突发？
  • 副本上的长读取是否与SQL线程竞争？
  • 副本是否比源更小或更慢？
  • 备份作业或快照是否同时启动？

如果延迟在下降，副本正在追赶。如果延迟在增长，移除负载或增加容量。重启延迟的副本很少能解决持续的资源瓶颈。

过滤器和多源复制

复制过滤器可能使故障更难读取。副本可能有意忽略某些数据库或表，但应用程序可能仍期望相关数据存在。如果使用过滤器，在假设副本损坏之前检查它们：

SHOW REPLICA STATUS\G

查找提及Replicate_Do_DB、Replicate_Ignore_DB、Replicate_Do_Table或重写规则的字段。旧输出在SHOW SLAVE STATUS下使用相同的一般名称。

过滤对于跨数据库写入尤其危险。如果事务更新了app.orders和audit.order_events，但副本过滤掉了audit，结果副本可能在技术上与过滤器一致，但对于期望审计行的工作流仍然无用。基于语句的日志记录可能使数据库过滤器更令人惊讶，因为选定的默认数据库可能影响事件是否被复制。

多源复制增加了另一层。一个通道可能健康而另一个停止。在这种情况下，检查所有通道的状态，而不是只读取输出的第一个块：

SHOW REPLICA STATUS\G

在基于通道的设置中，状态输出包括通道名称。修复失败的通道而不重置健康通道。如果两个源可以将重叠的键写入同一表，重复键错误通常是设计问题而非一次性复制故障。

避免隐藏的数据漂移

最糟糕的复制故障是那些说Yes但仍然包含错误数据的故障。漂移可能发生在跳过事务、直接写入副本、失败的导入、错误的过滤器或手动修复之后。

对于重要的副本，安排一致性检查。Percona Toolkit的pt-table-checksum常用于此，pt-table-sync可以在受控情况下帮助修复差异。这些工具可能产生负载，因此先测试它们，并以匹配生产环境的限制运行。

同时保护副本免受意外写入：

[mysqld]
read_only=ON
super_read_only=ON

为应用程序读取使用单独的凭据。不要让应用程序用户对副本拥有广泛的写权限"以防万一"。

快速事件检查清单

当复制中断时使用此顺序：

  1. 保存SHOW REPLICA STATUS\G输出。
  2. 检查I/O线程或SQL线程是否停止。
  3. 读取Last_IO_Error或Last_SQL_Error；不要仅依赖错误号。
  4. 检查MySQL错误日志中匹配的时间戳。
  5. 对于I/O故障，测试网络、凭据、授权、TLS和二进制日志可用性。
  6. 对于SQL故障，检查源和副本上受影响的行或表。
  7. 决定是修复、跳过（记录风险）、重新加载表还是重建副本。
  8. 恢复后，运行真实的写入测试并监控延迟。

大多数MySQL复制故障不是通过一个神奇命令解决的。它们通过保留证据、识别哪个线程失败以及选择不会让你得到一个运行但不可信的副本的修复来解决。