印刻数据库

外观

MySQL 复制失败处理 ​

复制失败的常见原因 ​

网络问题 ​

  • 网络中断或不稳定
  • 防火墙设置导致连接被阻止
  • 网络延迟过高
  • DNS解析问题

数据不一致 ​

  • 主库和从库数据结构不一致
  • 从库存在主库没有的数据
  • 主库执行的SQL在从库无法执行
  • 主键或唯一键冲突

配置问题 ​

  • 主从配置参数不一致
  • 复制账号权限不足
  • 二进制日志格式不兼容
  • GTID配置错误

资源问题 ​

  • 从库磁盘空间不足
  • 从库内存不足
  • 从库CPU负载过高
  • 主库二进制日志被删除或损坏

SQL错误 ​

  • 从库执行主库的SQL时发生错误
  • 存储过程或函数在从库执行失败
  • 触发器执行失败
  • 权限不足导致SQL执行失败

复制状态检查 ​

查看复制状态 ​

sql
-- 在从库上执行
SHOW SLAVE STATUS\G

关键状态字段 ​

字段名说明正常状态
Slave_IO_RunningIO线程状态Yes
Slave_SQL_RunningSQL线程状态Yes
Last_Error最后一个错误信息空
Seconds_Behind_Master复制延迟0 或较小值
Master_Log_File当前读取的主库二进制日志文件主库最新日志文件
Read_Master_Log_Pos当前读取的主库二进制日志位置主库最新日志位置
Relay_Master_Log_File当前执行的主库二进制日志文件接近主库最新日志文件
Exec_Master_Log_Pos当前执行的主库二进制日志位置接近主库最新日志位置

复制失败诊断流程 ​

步骤1：检查复制状态 ​

使用SHOW SLAVE STATUS\G命令查看复制状态，重点关注Slave_IO_Running、Slave_SQL_Running和Last_Error字段。

步骤2：分析错误日志 ​

查看从库的错误日志，获取详细的错误信息：

bash
# 默认错误日志位置
cat /var/log/mysql/error.log

# 或通过配置变量查看
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'log_error';"

步骤3：验证主从连接 ​

检查从库是否能正常连接到主库：

bash
mysql -h master_host -u repl_user -p

步骤4：检查主库状态 ​

在主库上检查二进制日志状态：

sql
SHOW MASTER STATUS;
SHOW BINARY LOGS;

步骤5：检查从库资源 ​

检查从库的磁盘空间、内存和CPU使用情况：

bash
df -h
free -m
top

常见复制失败场景及解决方案 ​

场景1：IO线程失败 (Slave_IO_Running: No) ​

可能原因 ​

  • 主库地址或端口配置错误
  • 复制账号密码错误
  • 主库二进制日志文件不存在或已被删除
  • 网络连接问题

解决方案 ​

  1. 检查主从连接配置：
sql
SHOW SLAVE STATUS\G; -- 查看主库地址和端口
CHANGE MASTER TO MASTER_HOST='master_host', MASTER_PORT=3306, MASTER_USER='repl_user', MASTER_PASSWORD='repl_pass', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=123456;
  1. 检查主库二进制日志状态：
sql
-- 在主库上执行
SHOW BINARY LOGS;
  1. 重启IO线程：
sql
START SLAVE IO_THREAD;

场景2：SQL线程失败 (Slave_SQL_Running: No) ​

可能原因 ​

  • 主键或唯一键冲突
  • 从库缺少主库执行SQL所需的表或字段
  • 权限不足
  • SQL模式不兼容

解决方案 ​

  1. 跳过错误（仅适用于非关键错误）：
sql
-- 跳过一个错误
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;
  1. 手动修复数据不一致：
  • 在从库上执行相应的SQL语句，修复数据不一致
  • 或使用pt-table-sync工具同步数据
  1. 重新初始化从库：
  • 从主库获取最新备份
  • 在从库上恢复备份
  • 重新配置复制

场景3：主键或唯一键冲突 ​

错误信息示例 ​

Last_Error: Error 'Duplicate entry '123' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO users VALUES (123, 'test')'

解决方案 ​

  1. 查看冲突数据：
sql
-- 在从库上执行
SELECT * FROM test.users WHERE id = 123;
  1. 根据业务需求选择解决方案：

  • 如果从库数据错误，删除从库的冲突数据：

    sql
    DELETE FROM test.users WHERE id = 123;
START SLAVE SQL_THREAD;

  • 如果需要跳过该错误：

    sql
    SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;

场景4：表或字段不存在 ​

错误信息示例 ​

Last_Error: Error 'Table 'test.new_table' doesn't exist' on query. Default database: 'test'. Query: 'INSERT INTO new_table VALUES (1, 'test')'

解决方案 ​

  1. 在从库上创建缺失的表或字段：
sql
-- 在主库上获取表结构
SHOW CREATE TABLE test.new_table;

-- 在从库上执行创建表语句
CREATE TABLE test.new_table (...);

-- 启动SQL线程
START SLAVE SQL_THREAD;
  1. 或重新初始化从库

场景5：权限不足 ​

错误信息示例 ​

Last_Error: Error 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation' on query. Default database: ''. Query: 'SET GLOBAL innodb_flush_log_at_trx_commit=1'

解决方案 ​

  1. 为复制用户授予所需权限：
sql
-- 在主库上执行
GRANT SUPER ON *.* TO 'repl_user'@'slave_host';
  1. 或跳过该错误（如果是非关键操作）：
sql
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;

场景6：主库二进制日志被删除 ​

错误信息示例 ​

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

解决方案 ​

  1. 检查主库二进制日志配置：
sql
-- 在主库上执行
SHOW GLOBAL VARIABLES LIKE 'expire_logs_days';
SHOW GLOBAL VARIABLES LIKE 'binlog_expire_logs_seconds';
  1. 重新初始化从库：
  • 从主库获取最新备份
  • 在从库上恢复备份
  • 重新配置复制
  1. 调整主库二进制日志保留时间：
sql
-- 设置二进制日志保留7天
SET GLOBAL binlog_expire_logs_seconds = 604800;

复制失败预防措施 ​

定期监控复制状态 ​

  • 使用脚本定期检查复制状态
  • 设置告警机制，当复制失败时及时通知
  • 监控复制延迟，及时发现潜在问题

保持主从配置一致 ​

  • 主从使用相同的MySQL版本
  • 主从配置参数保持一致，特别是SQL模式
  • 定期比对主从配置

合理配置二进制日志 ​

  • 设置合理的二进制日志保留时间
  • 定期备份二进制日志
  • 避免在主库上执行大事务

加强数据一致性管理 ​

  • 定期使用pt-table-checksum检查主从数据一致性
  • 及时修复发现的数据不一致问题
  • 避免在从库上执行写操作（除了复制相关操作）

提高从库资源配置 ​

  • 确保从库有足够的磁盘空间
  • 配置充足的内存和CPU资源
  • 使用高性能存储设备

版本差异 ​

MySQL 5.7 及更早版本 ​

  • 复制配置较为复杂
  • 缺少一些高级复制功能
  • 错误信息相对简单
  • 跳过错误的方式有限

MySQL 8.0 及以上版本 ​

  • 简化了复制配置，支持自动配置
  • 增强了复制监控和管理功能
  • 提供了更详细的错误信息
  • 支持更多的复制拓扑结构
  • 引入了Group Replication等新特性

最佳实践 ​

  1. 定期备份：定期备份主库和从库，确保在复制失败时能够快速恢复
  2. 监控复制状态：使用监控工具实时监控复制状态，设置合理的告警阈值
  3. 保持配置一致：主从配置参数保持一致，特别是SQL模式、字符集等
  4. 避免从库写操作：除非必要，否则不要在从库上执行写操作
  5. 合理设置二进制日志保留时间：根据业务需求和磁盘空间设置合适的保留时间
  6. 定期检查数据一致性：使用pt-table-checksum等工具定期检查主从数据一致性
  7. 准备应急预案：制定详细的复制失败应急预案，包括恢复步骤和责任分工
  8. 测试恢复流程：定期测试复制失败的恢复流程，确保预案的有效性

常见问题（FAQ） ​

Q1: 如何判断复制是否正常？ ​

A1: 可以通过SHOW SLAVE STATUS\G命令查看复制状态，当Slave_IO_Running和Slave_SQL_Running都为Yes，且Seconds_Behind_Master为0或较小值时，复制正常。

Q2: 复制失败时是否可以直接跳过错误？ ​

A2: 跳过错误需要谨慎，仅适用于非关键错误。跳过错误可能导致主从数据不一致，影响数据完整性。在跳过错误前，建议仔细分析错误原因，评估跳过错误的影响。

Q3: 如何避免复制失败？ ​

A3: 可以通过定期监控复制状态、保持主从配置一致、合理配置二进制日志、加强数据一致性管理和提高从库资源配置等方式避免复制失败。

Q4: 复制延迟和复制失败有什么区别？ ​

A4: 复制延迟是指从库执行SQL的时间落后于主库，复制仍然在进行；而复制失败是指复制进程停止，不再同步主库的数据。复制延迟可能导致复制失败，例如当延迟过大时，主库的二进制日志可能被删除。

Q5: 如何处理大规模复制失败？ ​

A5: 对于大规模复制失败，建议重新初始化从库：从主库获取最新备份，在从库上恢复备份，然后重新配置复制。这种方法可以确保主从数据完全一致，但会导致从库短暂不可用。

Q6: 使用GTID复制时如何处理复制失败？ ​

A6: 使用GTID复制时，处理复制失败的方法与传统复制类似，但需要注意GTID的一致性。可以使用SET GTID_NEXT='automatic'来恢复自动复制，或使用SET GTID_NEXT='uuid:N'来跳过特定的GTID事务。

Q7: 复制失败会导致数据丢失吗？ ​

A7: 复制失败本身不会导致数据丢失，但如果处理不当，可能会导致主从数据不一致。在主库发生故障需要切换到从库时，如果从库数据与主库不一致，可能会导致数据丢失。

Q8: 如何监控复制状态？ ​

A8: 可以使用脚本定期执行SHOW SLAVE STATUS\G命令，或使用监控系统（如Zabbix、Prometheus）监控复制状态。监控系统可以提供更直观的可视化界面和告警机制。

Q9: 主从复制和组复制的故障处理有什么区别？ ​

A9: 主从复制是异步复制，故障处理主要关注IO线程和SQL线程的状态；而组复制是同步复制，故障处理需要关注集群成员状态、视图变更和冲突检测等。组复制提供了自动故障转移机制，可以自动处理部分故障。

Q10: 如何测试复制故障恢复流程？ ​

A10: 可以通过手动停止复制进程、修改从库数据或删除主库二进制日志等方式模拟复制故障，然后按照应急预案进行恢复，测试恢复流程的有效性和时效性。