一次生产事故中，原本需要10小时的大表拷贝操作，被我用一种方法压缩到45分钟完成。以下是经过实战检验的终极技巧。

为什么传统拷贝方式效率低下？

直接使用CREATE TABLE new_table SELECT * FROM old_table或INSERT INTO new_table SELECT * FROM old_table拷贝大表时，你会遇到：

1. 锁表问题：长时间阻塞写操作
2. 事务日志暴涨：undo log和redo log快速增长
3. 内存压力：可能导致大量磁盘临时文件
4. 主从延迟：在复制环境中尤为明显

下面介绍的五种方法能有效解决这些问题。

方法一：分批插入（推荐指数：）

原理与步骤

通过分页查询减少单次事务大小和锁持有时间，每次处理一小部分数据。

-- 设置每次处理的数据量
SET @batch_size = 10000;
SET @max_id = (SELECT MAX(id) FROM old_table);
SET @min_id = (SELECT MIN(id) FROM old_table);

WHILE @min_id <= @max_id DO
    INSERT INTO new_table 
    SELECT * FROM old_table 
    WHERE id BETWEEN @min_id AND @min_id + @batch_size - 1;
    
    SET @min_id = @min_id + @batch_size;
END WHILE;

适用场景

• 中小规模表（几十GB以下）
• 需要在线操作，对业务影响要小
• 有自增主键或唯一递增字段

方法二：导出导入（推荐指数：）

原理与步骤

使用mysqldump导出数据再导入，结合特定参数优化性能。

# 导出数据（不包含表结构）
mysqldump -uusername -ppassword \
    --single-transaction \
    --quick \
    --no-create-info \
    database old_table > old_table_data.sql

# 导入数据
mysql -uusername -ppassword database < old_table_data.sql

关键参数解析：

• --single-transaction：创建一致性快照，不锁表
• --quick：逐行检索数据，减少内存使用
• --no-create-info：不包含建表语句

适用场景

• 需要跨服务器拷贝
• 表结构已预先创建好
• 有文件传输带宽

方法三：物理拷贝（推荐指数：）

原理与步骤

直接拷贝MySQL的底层数据文件（.ibd），速度最快。

-- 1. 锁定表获取一致性状态
FLUSH TABLES old_table FOR EXPORT;

-- 2. 拷贝.ibd文件（在操作系统层面执行）
-- cp /var/lib/mysql/database/old_table.ibd /backup/

-- 3. 解锁表
UNLOCK TABLES;

-- 4. 新表继承旧表结构
CREATE TABLE new_table LIKE old_table;

-- 5. 卸载新表的表空间
ALTER TABLE new_table DISCARD TABLESPACE;

-- 6. 将拷贝的.ibd文件移动到新表目录
-- mv /backup/old_table.ibd /var/lib/mysql/database/new_table.ibd

-- 7. 导入表空间
ALTER TABLE new_table IMPORT TABLESPACE;

适用场景

• 极大数据量（TB级别）
• 追求最快拷贝速度
• 可接受短暂表锁定

方法四：利用Shell管道（推荐指数：）

原理与步骤

使用管道将导出和导入操作串联，避免中间文件。

mysqldump -uusername -ppassword \
    --single-transaction \
    --quick \
    database old_table | \
mysql -uusername -ppassword \
    -C database

性能优化技巧：

# 增加网络缓冲区大小
mysqldump ... | mysql --max_allowed_packet=512M ...

# 使用压缩传输（适合跨机房）
mysqldump ... | gzip | ssh user@remote "gunzip | mysql ..."

适用场景

• 跨服务器传输
• 磁盘IO成为瓶颈
• 需要避免中间文件存储

方法五：基于二进制日志的同步（推荐指数：）

原理与步骤

先拷贝基础数据，再通过二进制日志追增量。

-- 1. 记录开始位置
SHOW MASTER STATUS;
-- 记下File和Position

-- 2. 拷贝基础数据（使用前述任何方法）
INSERT INTO new_table SELECT * FROM old_table;

-- 3. 应用从开始位置到现在的所有二进制日志
mysqlbinlog --start-position=123456 \
    mysql-bin.000001 | mysql -uusername -ppassword

适用场景

• 要求业务零中断
• 可接受最终一致性
• 有二进制日志 enabled

终极技巧对比分析

实战案例：45分钟完成1TB表拷贝

某电商平台的用户行为表达到1.2TB，需要在不影响业务的情况下迁移到新服务器。

解决方案：

1. 00:00-00:05 创建一致性快照：FLUSH TABLES FOR EXPORT
2. 00:05-00:35 并行拷贝ibd文件到新服务器（使用scp多线程）
3. 00:35-00:40 在新服务器导入表空间：IMPORT TABLESPACE
4. 00:40-00:45 验证数据一致性和完整性

总耗时45分钟，期间只锁表5分钟，业务影响降到最低。

总结

选择合适的大表拷贝方法需要综合考虑数据规模、业务容忍度和技术环境：

1. 追求极致速度：选择物理拷贝方法（方法三）
2. 最小化业务影响：使用分批插入（方法一）或二进制日志同步（方法五）
3. 跨服务器迁移：导出导入（方法二）或Shell管道（方法四）更合适

最重要的是：无论选择哪种方法，一定要先在测试环境验证！ 数据无小事，操作需谨慎。