一、当数据复制遇到红灯时

最近在项目上遇到个有意思的案例：开发小哥用INSERT INTO SELECT语句迁移百万级用户数据时，系统突然弹出了"Duplicate entry '12345' for key 'PRIMARY'"的错误提示。就像高速公路突然遇到施工路段，整个数据迁移流程被迫中断。这种场景在数据同步、备份恢复、分库分表等操作中其实并不少见，今天我们就来聊聊当MySQL表数据复制出现异常时，该如何快速定位和解决问题。

二、常见故障红黑榜

2.1 主键冲突（经典错误案例）

-- 错误示例重现
INSERT INTO user_new SELECT * FROM user_old;
-- 报错内容：Duplicate entry '10086' for key 'PRIMARY'

这种错误就像在停车场找车位，发现自己的专属车位被占了。常见于目标表已存在相同主键记录，或自增ID未重置的情况。

2.2 网络连接异常

mysqldump -h 192.168.1.100 -u root -p dbname | mysql -h 192.168.1.200 -u root -p

这类错误如同快递运输途中货车抛锚，常发生在跨机房数据同步或大数据量传输时。

2.3 数据类型不兼容

-- 源表结构
CREATE TABLE source (
    price DECIMAL(10,2)
);

-- 目标表结构
CREATE TABLE target (
    price VARCHAR(20)
);

-- 执行复制时可能出现隐式转换错误

这种情况就像把安卓充电线插进iPhone，虽然看起来相似，但实际可能不兼容。

三、故障应急工具箱

3.1 主键冲突解决方案

-- 方法1：清空自增计数器
ALTER TABLE user_new AUTO_INCREMENT = 1;

-- 方法2：跳过重复记录（注意数据完整性）
INSERT IGNORE INTO user_new SELECT * FROM user_old;

-- 方法3：替换重复记录
REPLACE INTO user_new SELECT * FROM user_old;

适用场景：

  • 方法1适合全新表初始化
  • 方法2适合容忍少量数据丢失
  • 方法3适合强制覆盖旧数据

3.2 断点续传方案

mysqldump -h source_host -u user -p dbname table --where="id>100000" > chunk.sql

mysql -h target_host -u user -p dbname < chunk.sql

优势：就像下载大文件时支持断点续传，避免重新开始

3.3 C#批量处理方案

// 使用MySqlConnector库（需要安装NuGet包）
using MySqlConnector;

public class DataMigrator {
    public void SafeBatchInsert(int batchSize = 1000) {
        using var sourceConn = new MySqlConnection("源库连接字符串");
        using var targetConn = new MySqlConnection("目标库连接字符串");
        
        sourceConn.Open();
        targetConn.Open();

        using var transaction = targetConn.BeginTransaction();
        var reader = sourceConn.ExecuteReader("SELECT * FROM user_old");
        
        while (reader.Read()) {
            var cmd = new MySqlCommand(
                "INSERT INTO user_new VALUES(@id, @name)", 
                targetConn, 
                transaction);
            
            // 添加参数...（此处省略参数化代码）
            
            if (++count % batchSize == 0) {
                transaction.Commit();
                transaction.Begin();
            }
        }
        transaction.Commit();
    }
}

代码要点：

  1. 使用参数化查询防止SQL注入
  2. 批次提交避免大事务
  3. 事务保障原子性
  4. 使用轻量级的MySqlConnector驱动

四、场景选择指南针

4.1 小数据量场景（<10万行）

  • 推荐方案：INSERT INTO SELECT 语句
  • 优点：简单快捷，单条SQL完成
  • 缺点：无断点续传能力

4.2 中数据量场景（10万-500万行）

  • 推荐方案：mysqldump分块导出
  • 优点：可控制传输节奏
  • 缺点：需要手动处理文件

4.3 大数据量场景（>500万行）

  • 推荐方案：编程语言批量处理（如C#）
  • 优点：灵活可控，支持复杂逻辑
  • 缺点：开发成本较高

五、技术方案红黑榜

方法 执行速度 可靠性 灵活性 学习成本
原生SQL语句 ★★★★☆ ★★☆☆☆ ★☆☆☆☆ ★☆☆☆☆
mysqldump工具 ★★★☆☆ ★★★☆☆ ★★☆☆☆ ★★☆☆☆
编程语言实现 ★★☆☆☆ ★★★★☆ ★★★★★ ★★★★☆

选择建议：

  • 赶时间选原生SQL
  • 要稳定选mysqldump
  • 求灵活用编程实现

六、避坑备忘录

  1. 预检查清单：

    • 对比源表和目标表的字符集（SHOW CREATE TABLE）
    • 检查自增ID当前值（SHOW TABLE STATUS）
    • 验证外键约束是否一致

  2. 网络调优参数：

    max_allowed_packet=256M
wait_timeout=28800
net_read_timeout=3600

  3. 应急工具箱：

    • 使用pt-table-checksum校验数据一致性
    • 善用EXPLAIN分析查询执行计划
    • 定期执行ANALYZE TABLE更新统计信息

七、从故障中成长

经历过这次数据迁移的波折，我们发现：处理数据复制错误就像医生治病，既要快速缓解症状（解决报错），更要找出病根（分析原因），最后还要开预防药方（优化流程）。记住这几个原则：

  1. 预防优于治疗：迁移前做好结构对比和空表测试
  2. 分而治之：大数据量操作要分解执行
  3. 留好后路：始终保留原始数据备份
  4. 监控护航：使用SHOW PROCESSLIST监控执行状态

下次当你面对数据复制的"红色警报"时，希望这份指南能成为你的应急手册。记住，好的系统不是从不报错，而是知道如何优雅地处理错误。