印刻数据库

外观

MySQL 复制故障排查 ​

复制故障的分类 ​

1. 复制停止 ​

  • 主从连接断开
  • 二进制日志损坏
  • 复制SQL线程执行错误
  • 权限问题导致无法读取二进制日志

2. 复制延迟 ​

  • 网络带宽不足
  • 主库写入压力过大
  • 从库硬件性能不足
  • 大事务导致的延迟
  • 长查询阻塞复制线程

3. 数据不一致 ​

  • 复制过滤规则配置错误
  • 从库执行了写入操作
  • 复制跳过了错误导致数据丢失
  • 主从版本差异导致的兼容性问题

故障诊断步骤 ​

1. 检查复制状态 ​

sql
SHOW SLAVE STATUS\G

2. 查看错误日志 ​

bash
tail -n 100 /var/log/mysql/error.log

3. 检查网络连接 ​

bash
ping master_host
mysql -h master_host -u replication_user -p

4. 验证二进制日志 ​

sql
-- 在主库上检查二进制日志状态
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- 检查二进制日志文件是否存在
SHOW GLOBAL VARIABLES LIKE 'log_bin_basename';

5. 检查从库线程状态 ​

sql
-- 检查IO线程和SQL线程状态
SHOW PROCESSLIST\G

常见复制故障及解决方案 ​

1. IO线程无法连接到主库 ​

症状 ​

Slave_IO_Running: No
Last_IO_Error: error connecting to master 'replication_user@master_host:3306' - retry-time: 60  retries: 10

解决方案 ​

  • 检查主库是否正常运行
  • 验证复制用户的权限和密码
  • 检查网络连接和防火墙设置
  • 验证主库的bind-address配置

2. SQL线程执行错误 ​

症状 ​

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

解决方案 ​

方法1：跳过错误 ​
sql
-- MySQL 5.6及以下
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- MySQL 5.7及以上
STOP SLAVE SQL_THREAD;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;
方法2：手动修复数据 ​
  • 在从库上手动执行修复操作，确保数据与主库一致
  • 然后重新启动复制
方法3：重新初始化从库 ​
bash
# 在主库上创建备份
mysqldump -u root -p --single-transaction --master-data=2 --all-databases > master_backup.sql

# 在从库上恢复备份
mysql -u root -p < master_backup.sql

3. 复制延迟 ​

症状 ​

Seconds_Behind_Master: 3600

解决方案 ​

  • 优化主库写入性能
  • 提升从库硬件配置
  • 启用并行复制
    sql
    -- MySQL 5.6
SET GLOBAL slave_parallel_workers = 4;

-- MySQL 5.7+
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
  • 减少从库的查询负载
  • 配置适当的binlog格式（ROW格式通常更高效）

4. 二进制日志损坏 ​

症状 ​

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'

解决方案 ​

  • 检查主库的二进制日志索引文件
  • 在主库上重新创建二进制日志
    sql
    FLUSH BINARY LOGS;
RESET MASTER;
  • 重新初始化从库复制

复制监控与预防 ​

1. 监控复制状态 ​

  • 定期检查SHOW SLAVE STATUS输出
  • 监控Seconds_Behind_Master指标
  • 监控复制线程状态

2. 设置复制告警 ​

  • 使用监控工具（如Prometheus+Grafana、Zabbix等）设置复制延迟告警
  • 监控IO线程和SQL线程的运行状态

3. 定期验证数据一致性 ​

bash
# 使用pt-table-checksum验证数据一致性
pt-table-checksum h=master_host,u=checksum_user,p=password

# 使用pt-table-sync修复数据不一致
pt-table-sync h=master_host,u=sync_user,p=password h=slave_host --databases test

4. 最佳实践 ​

  • 定期备份主从库
  • 避免在从库上执行写入操作
  • 使用适当的binlog格式（推荐ROW格式）
  • 确保主从服务器时间同步
  • 定期检查和清理二进制日志

常见问题（FAQ） ​

Q1: 如何快速判断复制是否正常运行？ ​

A1: 可以通过以下命令快速检查复制状态：

sql
SHOW SLAVE STATUS\G

主要关注以下字段：

  • Slave_IO_Running: 应为Yes
  • Slave_SQL_Running: 应为Yes
  • Seconds_Behind_Master: 应为0或较小值

Q2: 复制延迟高时，如何定位瓶颈？ ​

A2: 可以通过以下步骤定位复制延迟瓶颈：

  1. 检查从库的硬件资源使用情况（CPU、内存、磁盘I/O）
  2. 分析从库的慢查询日志
  3. 检查主库的写入压力
  4. 使用SHOW PROCESSLIST查看阻塞的线程
  5. 检查网络带宽使用情况

Q3: 如何安全地跳过复制错误？ ​

A3: 跳过复制错误应谨慎操作，建议：

  1. 先分析错误原因，确定是否可以安全跳过
  2. 记录跳过的错误信息，以便后续分析
  3. 跳过错误后，验证数据一致性
  4. 对于关键业务，建议手动修复数据而非跳过错误

Q4: 主库重启后，从库需要重新配置复制吗？ ​

A4: 通常不需要，从库会自动重连主库。但如果主库重启后二进制日志文件发生变化，可能需要重新配置复制位点。

Q5: 如何防止复制数据不一致？ ​

A5: 可以采取以下措施：

  1. 禁止在从库上执行写入操作
  2. 使用read_only参数
  3. 定期使用pt-table-checksum验证数据一致性
  4. 配置适当的复制过滤规则
  5. 避免使用sql_slave_skip_counter跳过错误

Q6: MySQL 8.0的复制故障排查与之前版本有何不同？ ​

A6: MySQL 8.0引入了一些新的复制特性和监控指标：

  • 增强的并行复制（WRITESET）
  • 新增的复制监控表（performance_schema.replication_*）
  • 改进的错误信息
  • 支持复制通道（适合多源复制）
  • 新增的复制相关状态变量

在排查MySQL 8.0的复制故障时，可以利用这些新特性获取更详细的监控信息。