MYSQL数据库迁移方法和踩过的坑及问题解决

不长叶子的树

上个月接到个任务，把公司的MySQL从阿里云迁移到自建机房。听起来简单，mysqldump导出导入不就完了？结果折腾了整整一周，踩了无数坑。记录一下，给后来人避雷。

背景

原来的数据库在阿里云RDS，配置是4核8G，数据量大概200G。要迁移到公司自建机房的物理服务器上。

要求：

• 停机时间控制在30分钟内
• 不能丢数据
• 迁移后性能不能下降
  

第一个坑：mysqldump太慢了

最开始想的最简单，直接

1. mysqldump：mysqldump -h rds.xxx.com -u root -p --all-databases > all.sql

复制代码

跑了6个小时还没导完…200G的数据，单线程dump，速度大概10MB/s。算了一下，导出要6小时，导入至少也要4-5小时。光这一步就10多个小时，停机30分钟？做梦。

解决：mydumper多线程导出

1. # 安装mydumper
   
2. apt install mydumper
   
3. 
   
4. # 多线程导出（8线程）
   
5. mydumper -h rds.xxx.com -u root -p password \
   
6.   -t 8 \
   
7.   -c \
   
8.   -o /backup/dump
   
9. 
   
10. # 多线程导入
    
11. myloader -h localhost -u root -p password \
    
12.   -t 8 \
    
13.   -d /backup/dump

复制代码

8线程跑，导出时间从6小时降到了50分钟。导入也快了很多。

第二个坑：字符集不一致

数据导入后，发现有些中文变成了问号。排查了半天，发现阿里云RDS默认是utf8mb4，我们自建的MySQL是utf8。

1. -- 检查字符集
   
2. SHOW VARIABLES LIKE 'character%';

复制代码

阿里云：

1. character_set_database: utf8mb4
   
2. character_set_server: utf8mb4

复制代码

自建：

1. character_set_database: utf8
   
2. character_set_server: utf8

复制代码

解决：统一字符集

1. # my.cnf
   
2. [mysqld]
   
3. character-set-server=utf8mb4
   
4. collation-server=utf8mb4_unicode_ci
   
5. 
   
6. [client]
   
7. default-character-set=utf8mb4

复制代码

然后重新导入。这次中文正常了。


第三个坑：自增ID冲突

导入完成后，应用跑起来，几分钟后报错：

1. Duplicate entry '12345' for key 'PRIMARY'

复制代码

自增ID冲突了？原因是导入时没有导入AUTO_INCREMENT的值，新插入的数据从1开始自增，和原有数据冲突了。

解决：导出时带上自增值

1. mydumper -h rds.xxx.com -u root -p password \
   
2.   -t 8 \
   
3.   --set-names=utf8mb4 \
   
4.   --triggers \
   
5.   --routines \
   
6.   --events \
   
7.   -o /backup/dump

复制代码

或者手动修复：

1. -- 查看当前最大ID
   
2. SELECT MAX(id) FROM users;
   
3. -- 结果：98765
   
4. 
   
5. -- 设置AUTO_INCREMENT
   
6. ALTER TABLE users AUTO_INCREMENT = 100000;

复制代码

第四个坑：binlog位置找不到

迁移过程中，数据还在持续写入。导出时记录了binlog位置，准备用binlog追数据。但是发现阿里云RDS的binlog只保留7天，而且位置格式和自建MySQL不一样。

解决：用GTID

还好阿里云RDS支持GTID，改用GTID来追数据：

1. # 导出时记录GTID
   
2. mydumper ... --set-names=utf8mb4
   
3. 
   
4. # 查看导出的metadata文件，里面有GTID信息
   
5. cat /backup/dump/metadata

复制代码

导入后，配置从库从主库同步：

1. CHANGE MASTER TO
   
2.   MASTER_HOST='rds.xxx.com',
   
3.   MASTER_USER='repl',
   
4.   MASTER_PASSWORD='password',
   
5.   MASTER_AUTO_POSITION=1;
   
6. 
   
7. START SLAVE;

复制代码

这样新数据就能自动同步过来了。


第五个坑：外键约束导入失败

有几张表死活导不进去，报错：

1. Cannot add or update a child row: a foreign key constraint fails

复制代码

外键约束的表，导入顺序不对就会失败。

解决：临时关闭外键检查

1. SET FOREIGN_KEY_CHECKS=0;
   
2. -- 导入数据
   
3. source /backup/dump.sql
   
4. SET FOREIGN_KEY_CHECKS=1;

复制代码

或者用myloader时加参数：

1. myloader -h localhost -u root -p password \
   
2.   -t 8 \
   
3.   --overwrite-tables \
   
4.   -d /backup/dump

复制代码

myloader会自动处理外键顺序。


第六个坑：迁移后查询变慢

数据迁移完成，应用跑起来，发现有些查询特别慢。原来在阿里云RDS上只要100ms的查询，现在要5秒。用EXPLAIN看了一下，发现没走索引。再一看，索引是有的，但是没生效。

原因：统计信息过期
数据导入后，表的统计信息还是旧的，优化器选错了执行计划。

1. -- 更新统计信息
   
2. ANALYZE TABLE users;
   
3. ANALYZE TABLE orders;
   
4. 
   
5. -- 或者批量更新所有表
   
6. mysqlcheck -u root -p --analyze --all-databases

复制代码

跑完之后，查询速度恢复正常。


第七个坑：时区问题

有些时间字段差了8小时。
阿里云RDS默认时区是Asia/Shanghai，我们自建的是UTC。

1. -- 检查时区
   
2. SHOW VARIABLES LIKE '%time_zone%';

复制代码

解决：统一时区

1. # my.cnf
   
2. [mysqld]
   
3. default-time-zone = '+08:00'

复制代码

最终的迁移方案

踩完这些坑，最后总结出来的迁移步骤：

1. 准备阶段（T-1天）

1. # 在新服务器上配置好MySQL，确保：
   
2. # - 字符集：utf8mb4
   
3. # - 时区：+08:00
   
4. # - 开启GTID

复制代码

2. 全量同步

1. # mydumper导出
   
2. mydumper -h rds.xxx.com -u root -p password \
   
3.   -t 8 \
   
4.   --set-names=utf8mb4 \
   
5.   --triggers --routines --events \
   
6.   -o /backup/dump
   
7. 
   
8. # myloader导入
   
9. myloader -h localhost -u root -p password \
   
10.   -t 8 \
    
11.   --overwrite-tables \
    
12.   -d /backup/dump

复制代码

3. 增量同步

1. -- 配置主从复制
   
2. CHANGE MASTER TO
   
3.   MASTER_HOST='rds.xxx.com',
   
4.   MASTER_USER='repl',
   
5.   MASTER_PASSWORD='password',
   
6.   MASTER_AUTO_POSITION=1;
   
7. 
   
8. START SLAVE;
   

复制代码

1. -- 确认同步状态
   
2. SHOW SLAVE STATUS\G

复制代码

-- Seconds_Behind_Master: 0 表示追上了

4. 切换（停机时间）
# 1. 应用停写（或切只读）
# 2. 等待从库追上（Seconds_Behind_Master=0）
# 3. 停止复制STOP SLAVE;
# 4. 修改应用配置，连接新数据库
# 5. 应用恢复

实际停机时间：不到10分钟。


异地迁移的额外问题

后来又做了一次跨城市的迁移，从北京机房到上海机房。遇到的问题是：两个机房网络不通，北京是内网IP，上海访问不了。
试了几个方案：

• 开公网 - 安全风险大，被否了
• VPN - 配置复杂，而且VPN带宽不够
• 专线 - 太贵，临时迁移不值当
  

最后用的组网软件（星空组网），把两边服务器组到一个虚拟局域网里：

1. 北京MySQL：10.26.0.1:3306
   
2. 上海服务器：10.26.0.2
   
3. 
   
4. # 上海服务器直接连北京的MySQL
   
5. mysql -h 10.26.0.1 -u root -p

复制代码

这样主从复制的配置也不用改，直接用内网IP就行。迁移完成后把组网关掉就行。


总结

MySQL迁移看似简单，实际坑不少：

坑	解决方案
mysqldump慢	mydumper多线程
字符集不一致	统一utf8mb4
自增ID冲突	导出时带AUTO_INCREMENT
binlog追不上	用GTID
外键导入失败	关闭外键检查
查询变慢	ANALYZE TABLE
时区问题	统一时区设置
跨网络迁移	组网打通

如果你也要做MySQL迁移，建议先在测试环境完整跑一遍，把坑踩完再上生产。



来自：花宝宝 后端老兵 | 星空组网 starvpn.cn