怎样快速地迁移 MySQL 中的数据？

一、迁移前的关键准备

1.1 评估数据规模与结构

在启动迁移前，必须对源数据库进行全面分析。使用SHOW TABLE STATUS命令获取每张表的行数、数据长度和索引长度，结合information_schema.TABLES视图计算总数据量。例如：

SELECT 
    table_name, 
    round(data_length/1024/1024,2) as data_size_mb,
    round(index_length/1024/1024,2) as index_size_mb,
    table_rows
FROM information_schema.TABLES 
WHERE table_schema = 'your_database';

对于超过100GB的大型数据库，建议采用分表迁移策略；对于包含大量BLOB/TEXT字段的表，需考虑特殊处理方案。

1.2 网络环境优化

跨机房迁移时，网络带宽往往是瓶颈。建议：

• 使用万兆网络接口（10Gbps）
• 启用TCP窗口缩放（net.ipv4.tcp_window_scaling=1）
• 关闭Nagle算法（net.ipv4.tcp_nodelay=1）
• 采用压缩传输：在mysqldump中添加--compress参数可减少30%-50%传输量

二、核心迁移技术方案

2.1 物理迁移方案（推荐）

对于TB级数据库，物理迁移效率远高于逻辑迁移。主要方法：

Percona XtraBackup方案

# 完整备份
xtrabackup --backup --target-dir=/backup/base --user=backup --password=secret
# 增量备份（基于上次备份）
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
# 准备备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
xtrabackup --prepare --incremental-dir=/backup/inc1 --target-dir=/backup/base
# 恢复
xtrabackup --copy-back --target-dir=/backup/base

优势：接近实时迁移，对源库压力小，支持增量同步。

MySQL Enterprise Backup
类似XtraBackup，但提供更完善的图形化管理界面，适合企业级环境。

2.2 逻辑迁移优化方案

并行导出导入

# 并行导出（分表）
mysqldump -u user -p --single-transaction --where="id BETWEEN 1 AND 1000000" db table > part1.sql
mysqldump -u user -p --single-transaction --where="id BETWEEN 1000001 AND 2000000" db table > part2.sql
# 并行导入（GNU parallel示例）
parallel -j 4 "mysql -u user -p db < {}" ::: part*.sql

关键参数：

• --quick：避免缓存整个结果集
• --max_allowed_packet=1G：处理大字段
• --net_buffer_length=16384：优化网络包大小

数据类型优化

• 将TEXT/BLOB字段单独处理
• 对DATETIME类型考虑使用TIMESTAMP（节省存储空间）
• 规范化ENUM类型使用

三、高级加速技术

3.1 主从复制迁移

1. 在目标服务器搭建从库

   CHANGE MASTER TO
   MASTER_HOST='source_host',
   MASTER_USER='repl_user',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.000123',
   MASTER_LOG_POS=456789;
   START SLAVE;

2. 监控复制进度：

   SHOW SLAVE STATUS\G

3. 达到同步后提升为主库

优势：零停机时间，适合生产环境。

3.2 使用专业ETL工具

• AWS Database Migration Service：支持持续复制
• Alibaba Cloud DTS：提供图形化配置界面
• Talend Open Studio：开源ETL解决方案

典型配置参数：

• 批处理大小：1000-5000行/批
• 并行线程数：CPU核心数×2
• 重试机制：指数退避策略

四、迁移后验证

4.1 数据一致性校验

-- 行数校验
SELECT COUNT(*) FROM source.table;
SELECT COUNT(*) FROM target.table;
-- 校验和比对（需安装pt-table-checksum）
pt-table-checksum --user=user --password=pass --host=source_host h=target_host,D=database,t=table

4.2 性能基准测试

使用sysbench进行对比测试：

sysbench oltp_read_write --db-driver=mysql --mysql-host=target_host --mysql-db=test --tables=10 --table-size=1000000 prepare
sysbench oltp_read_write --threads=16 --time=300 run

五、常见问题解决方案

5.1 大表迁移超时

• 调整innodb_lock_wait_timeout（默认50秒）
• 分批提交：每10万行提交一次
• 使用LOAD DATA INFILE替代INSERT语句

5.2 字符集问题

迁移前确认：

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

建议统一使用utf8mb4字符集。

5.3 外键约束处理

临时禁用外键检查：

SET FOREIGN_KEY_CHECKS=0;
-- 执行迁移
SET FOREIGN_KEY_CHECKS=1;

六、最佳实践建议

1. 分阶段迁移：先迁移静态数据，再同步增量变更
2. 蓝绿部署：准备两套环境交替验证
3. 自动化脚本：使用Ansible/Puppet管理迁移流程
4. 回滚方案：保留最近3天的备份
5. 监控告警：设置迁移进度和错误监控

对于超大型数据库（>10TB），建议采用：

• 物理迁移+逻辑校验的混合方案
• 使用存储级快照（如EBS卷克隆）
• 考虑分库分表架构重构

通过合理选择迁移策略、优化配置参数和实施严格验证，MySQL数据迁移的效率可提升3-10倍，同时将数据不一致风险控制在0.01%以下。实际案例中，某金融企业通过XtraBackup+并行导入方案，成功在4小时内完成2TB数据库的跨机房迁移，较传统方法提速8倍。