MySQL数据库迁移（基于mysqldump）总结

一、迁移前准备

权限验证

# 确保源数据库用户有导出权限
GRANT SELECT, LOCK TABLES ON source_db.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

目标数据库初始化​

# 创建目标数据库（若不存在）
mysql -u target_user -p -e "CREATE DATABASE target_db 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_general_ci;

二、数据导出流程

完整数据库导出（含结构+数据）

mysqldump -u source_user -p \
  --single-transaction \  # InnoDB引擎适用，保证事务一致性
  --master-data \           # 记录二进制日志位置（适合主从迁移）
  --default-character-set=utf8mb4 \  # 强制字符集
  source_db > full_dump.sql

按表选择性导出

# 导出特定表结构+数据
mysqldump -u source_user -p source_db table1 table2 \
  --no-indexes \            # 排除索引加快导出
  --where="status=1" \      # 添加查询条件过滤数据
  > filtered_data.sql

# 仅导出表结构
mysqldump -u source_user -p source_db table1 table2 \
  --no-data --skip-triggers > schema_only.sql

大数据量分块导出（示例：按ID分批）

# 使用shell脚本分页导出
split -d' ' -l 1000 $(mysql -N -s source_db "SELECT id FROM table1") | while read ids; do
  mysqldump -u source_user -p source_db table1 \
    --where="id IN ($ids)" > table1_part_$(date +%s).sql
done

三、数据导入流程

基础导入命令

# 导入完整备份
mysql -u target_user -p target_db < full_dump.sql

# 导入结构+数据（带字符集声明）
mysql -u target_user -p --default-character-set=utf8mb4 target_db < schema_only.sql

# 导入数据覆盖现有表（慎用！）
mysql -u target_user -p --force target_db < data_only.sql

增量数据同步（基于时间戳）

# 导出自某个时间点后的变更
mysqldump -u source_user -p source_db \
  --since-time="2024-01-01" table1 > incremental_update.sql
mysql -u target_user -p target_db < incremental_update.sql

四、特殊迁移场景

跨版本迁移

# 从MySQL 5.7迁移到8.0时添加兼容性选项
mysqldump -u source_user -p --compatible=mysql56 source_db > compat_dump.sql

加密数据库迁移

# 导出加密表数据
mysqldump -u source_user -p --ssl \
  --master-data source_db > encrypted_dump.sql

# 导入时指定SSL证书
mysql -u target_user -p --ssl \
  --ca=/path/to/ca.pem \
  --cert=/path/to/client-cert.pem \
  target_db < encrypted_dump.sql

五、数据一致性验证

结构校验

# 比较表结构
diff -u $(mysql -N -s source_db "SHOW CREATE TABLE table1") \
  $(mysql -N -s target_db "SHOW CREATE TABLE table1")

数据量校验

# 统计记录数
source_count=$(mysql -N -s source_db "SELECT COUNT(*) FROM table1")
target_count=$(mysql -N -s target_db "SELECT COUNT(*) FROM table1")
echo "Source: $source_count | Target: $target_count"

哈希值校验

# 计算数据校验和
source_hash=$(mysql -N -s source_db "SELECT MD5(GROUP_CONCAT(id)) FROM table1")
target_hash=$(mysql -N -s target_db "SELECT MD5(GROUP_CONCAT(id)) FROM table1")
echo "Source MD5: $source_hash | Target MD5: $target_hash"

六、遇到问题及对应解决方案

七、性能优化

并行处理​

# 使用多个线程并行导出
mysqldump -u source_user -p source_db \
  --parallel-servers=4 table1 table2 > parallel_dump.sql

压缩传输

# 压缩导出文件（节省带宽）
mysqldump -u source_user -p source_db | gzip > dbdump.gz

# 压缩后导入
zcat dbdump.gz | mysql -u target_user -p target_db

调整MySQL配置​

# 临时增大排序缓冲区（my.cnf）
[mysqldump]
sort_buffer_size = 4G
read_buffer_size = 4G

八、数据安全

敏感数据保护

# 加密导出文件
mysqldump ... | openssl aes-256-cbc -in -out encrypted_dump.enc -k passphrase

# 解密导入
openssl aes-256-cbc -d -in encrypted_dump.enc -out decrypted_dump.sql -k passphrase

权限隔离

-- 创建只读迁移用户
CREATE USER 'migrator'@'%' IDENTIFIED BY 'StrongPass!' 
  WITH SELECT, LOCK TABLES, SHOW DATABASES;

追踪审计

# 记录所有操作日志
mysqldump ... 2> migration.log

九、简单完整迁移示例

# 导出阶段
mysqldump -u src_user -p \
  --single-transaction \
  --master-data \
  --default-character-set=utf8mb4 \
  src_db > backup_$(date +%F_%T).sql

# 压缩传输
gzip backup_$(date +%F_%T).sql

# 导入阶段
scp backup_$(date +%F_%T).sql.gz user@target_host:/tmp/

# 解压并导入
gunzip /tmp/backup_$(date +%F_%T).sql.gz
mysql -u tgt_user -p tgt_db < /tmp/backup_$(date +%F_%T).sql

# 验证
mysql -N -s tgt_db "SHOW TABLE STATUS LIKE 'src_db%'";

​