Skip to content
高进波的博客

mysql主从不一致修复

MySQL主从同步冲突案例：删除表重建+pt工具修复数据

一、案例背景

  • 环境：
    • MySQL版本：5.7.38
    • 架构：1主2从（仅同步指定库）
    • 主库IP：10.10.1.50
    • 从库IP：10.10.1.51、10.10.1.52
  • 问题现象：从库同步中断，Slave_SQL_Running: No，报错Duplicate entry 'xxx' for key 'hash_code'（唯一键冲突）。

二、问题分析

  • 错误日志： 
    Last_Error: Could not execute Write_rows event on table jian_butler_new.project_siku_company;
    Duplicate entry '43af7282d730419ba13806f72d620637' for key 'hash_code'
  • 原因：主库数据变更（插入/更新）时，从库存在重复唯一键值，导致同步失败。

三、解决方案：删除表重建+pt工具同步

步骤1：暂停从库同步并备份数据

STOP SLAVE; -- 暂停同步
SET SQL_LOG_BIN=0; -- 禁用主库日志记录（避免操作同步到其他从库）

步骤2：重建表并切换表名（以project_siku_company为例）

-- 创建备份库arch
CREATE DATABASE IF NOT EXISTS arch;


-- 新建空表（结构与原表一致）
CREATE TABLE arch.project_siku_company_new LIKE jian_butler_new.project_siku_company;


-- 重命名表（原表备份到arch，新表替换原表）
RENAME TABLE
  jian_butler_new.project_siku_company TO arch.project_siku_company,
  arch.project_siku_company_new TO jian_butler_new.project_siku_company;

步骤3：批量处理其他冲突表

重复步骤2，依次处理以下表：

  • project_siku_info
  • project_siku_bids_info
  • project_siku_professional_person
  • …（共12张表，完整列表见附录）

步骤4：恢复从库同步

START SLAVE; -- 启动同步
SHOW SLAVE STATUS\G -- 检查同步状态

步骤5：使用pt工具校验并修复数据差异

1. 主库执行数据校验（指定表）

Terminal window
# 校验jian_butler_new库的指定表
pt-table-checksum \
  h='10.10.1.50',u='gaojinbo',p='gaojinbo.com',P=3306 \
  -d jian_butler_new \
  -t project_siku_company,project_siku_info,...（12张表） \
  --nocheck-replication-filters \
  --replicate=open_data.ptchecksums \
  --no-check-binlog-format

2. 生成修复语句（仅打印，不执行）

Terminal window
pt-table-sync \
  --print \
  --replicate=open_data.ptchecksums \
  h='10.10.1.50',u='gaojinbo',p='gaojinbo.com',P=3306

3. 执行数据修复

Terminal window
pt-table-sync \
  --execute \
  --replicate=open_data.ptchecksums \
  h='10.10.1.50',u='gaojinbo',p='gaojinbo.com',P=3306

步骤6：处理其他表的唯一键冲突

-- 针对jian_butler_variable库的表冲突
pt-table-checksum \
  -d jian_butler_variable \
  -t temp_project_bidding,temp_project_bidding_change,... \
  --nocheck-replication-filters \
  --replicate=open_data.ptchecksums


-- 跳过复制错误（示例）
SET GLOBAL SLAVE_EXEC_MODE=IDEMPOTENT; -- 允许重复执行（幂等模式）
STOP SLAVE;
START SLAVE;
SET GLOBAL SLAVE_EXEC_MODE=STRICT; -- 恢复严格模式

四、处理结果

  • 从库状态：Slave_SQL_Running: Yes，同步恢复正常。
  • 数据一致性：通过pt-table-checksum验证，主从数据差异清零。

五、附录：重建表完整列表

原表名备份表名新表名
jian_butler_new.project_siku_companyarch.project_siku_companyjian_butler_new.project_siku_company
jian_butler_new.project_siku_infoarch.project_siku_infojian_butler_new.project_siku_info
jian_butler_new.project_siku_bids_infoarch.project_siku_bids_infojian_butler_new.project_siku_bids_info
…（共12张表，省略中间行）……

六、关键要点

  1. 避免手动修改同步表：通过RENAME TABLE原子操作切换表，防止同步期间数据不一致。
  2. pt工具使用前提：主从同步需先暂停（STOP SLAVE），避免修复过程中数据变更引发新冲突。
  3. 唯一键冲突预防：主库插入数据时需确保唯一性，或在从库设置slave_skip_errors（谨慎使用）。