还在为 MySQL 数据迁移方案纠结？
本文从场景分析 → 工具选型 → 安装部署 →离线/在线迁移全流程，手把手带你掌握三大主流迁移工具（mydumper / XtraBackup / mysqldump）的使用技巧与避坑指南。
无论你是 DBA、运维还是后端开发者，都能快速找到最适合你业务的迁移策略！

第一章 迁移场景分析与决策

1.1 全实例迁移 vs 业务库迁移

类型	定义	适用场景	风险与注意事项	推荐方案
全实例迁移	迁移整个 MySQL 实例，包括 mysql、sys、performance_schema 等系统库及所有业务库	- 灾备环境重建。 - 整库克隆用于测试 - 物理机到云迁移	⚠️ 高风险： - 目标实例必须是全新初始化的空库 - 若在已有实例上恢复，会导致用户权限混乱、系统表冲突 - 不适用于仅需部分业务的场景	✅ XtraBackup（首选） ⚠️ mydumper（仅限同版本+全新实例）
业务库迁移	仅迁移指定的业务数据库（如 xwb、order）	- 搭建只读从库 - 微服务拆分 - 跨环境同步特定业务	✅ 低风险： - 安全性高，不影响目标库现有用户和权限 - 可精准控制迁移范围 - 支持跨版本、跨架构	✅ mydumper（首选） ✅ mysqldump（小库/无安装权限时）

💡 核心原则：

• 除非重建整实例，否则绝不迁移 mysql 库
• 业务迁移优先使用 mydumper，避免 mysqldump 单线程瓶颈

---

1.2 在线迁移 vs 离线迁移

类型	定义	关键考量	推荐方案
在线迁移	业务不停机，边写入边迁移	- 主库负载是否允许额外 I/O - 网络带宽是否充足 - RPO（数据丢失容忍度）要求	✅ XtraBackup（无锁热备） ✅ mydumper（短时 FTWRL） ❌ mysqldump（长锁，不推荐）
离线迁移	业务停机窗口内完成迁移	- 停机时间是否可接受 - 数据量大小决定恢复时长	✅ XtraBackup（恢复最快） ✅ mydumper（中小库） ✅ mysqldump（极小库 < 10GB）

📌 决策口诀：

• 能不停机？→ 选 XtraBackup 或 mydumper
• 数据量 > 500GB？→ 必选 XtraBackup
• 只需部分库？→ mydumper > mysqldump

---

第二章 工具选型：mysqldump vs mydumper vs XtraBackup

2.1 mysqldump（逻辑备份，MySQL 自带）

• 原理：单线程导出 SQL，通过 -master-data 获取 binlog 位点。
• 优点：
  • ✅ 无需额外安装（MySQL 自带）
  • ✅ 跨版本兼容
  • ✅ 人类可读
• 缺点：
  • ❌ 单线程，大库备份/恢复极慢
  • ❌ -single-transaction 仅对 InnoDB 有效，MyISAM 仍需锁表
  • ❌ 无法并行压缩
• 适用场景：
  • 极小库（< 10GB）
  • 临时应急（无权限安装第三方工具）

---

2.2 mydumper（逻辑备份，多线程增强版）

• 原理：多线程导出 SQL，支持库/表级并行，记录 binlog 位点。
• 优点：
  • ✅ 多线程（备份/恢复快 3～5 倍于 mysqldump）
  • ✅ 支持库/表筛选（-regex）
  • ✅ 内置压缩（gzip/zstd）
  • ✅ 跨版本兼容
• 缺点：
  • ❌ 需额外安装（但 Percona 提供 RPM 包）
  • ❌ 大库恢复仍慢于物理备份
• 适用场景：
  • 业务库迁移（10GB ～ 500GB）
  • 跨版本升级
  • 需要排除敏感表

---

2.3 XtraBackup（物理备份，Percona 官方）

• 原理：直接拷贝 InnoDB 数据文件，结合 redo log 和 binlog 实现一致性。
• 优点：
  • ✅ TB 级数据库秒级恢复
  • ✅ 无锁热备（InnoDB）
  • ✅ 自动记录精确 binlog 位点
• 缺点：
  • ❌ 必须同主版本（5.7 备份不能用于 8.0）
  • ❌ 无法筛选库表（全库备份）
  • ❌ 占用大量磁盘 I/O
• 适用场景：
  • 全实例迁移
  • 超大库（> 500GB）主从搭建
  • RTO 要求极低

---

2.4 三工具对比总结表

维度	mysqldump	mydumper	XtraBackup
备份类型	逻辑（SQL）	逻辑（SQL）	物理（文件）
并行能力	❌ 单线程	✅ 多线程	✅ 多线程
数据量上限	< 10GB	< 500GB	无上限
跨版本支持	✅	✅	❌
库表筛选	✅（--databases）	✅（--regex）	❌
恢复速度	极慢	中等	极快
主从位点	--master-data	--replica-data	xtrabackup_binlog_info
安装依赖	无	需安装	需安装
学习成本	低	中	中

✅ 最终建议：

• 日常业务迁移 → mydumper
• 超大库/全实例 → XtraBackup
• 仅当无安装权限时 → mysqldump（小库）

---

第三章 工具的安装部署

3.1 XtraBackup的安装部署

3.1.1 下载mysql适用的xtrabackup版本

MySQL 5.7 及以下点击跳转下载

MySQL 8.0 及以上 点击跳转下载

3.1.2 下载qpress（压缩工具）

XtraBackup 默认使用 qpress 进行压缩，需单独安装

yum install qpress
或者
https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-3.el7.x86_64.rpm 下载

⚠️注意：https://repo.percona.com/yum/release/7/ 我这里是centos 7的，需根据自己服务器的版本进行替换

3.1.3 安装

# 解压 XtraBackup
tar -xf /mydata/soft/xtrabackup.tar.gz --strip-components=1 -C /usr/local/xtrabackup

# 安装 qpress（解压工具）
rpm -ivh qpress-11-3.el8.x86_64.rpm 

3.1.4 创建备份账号

## 创建一个专用于备份操作的本地用户 'bkpuser'，仅允许从 localhost 连接，密码为 'Bkpuser_2020'
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'Bkpuser_2026';

## 授权账号权限
mysql5.6: grant reload,process,lock tables,replication client on *.* to 'bkpuser'@'localhost';
mysql8.0: GRANT SELECT, BACKUP_ADMIN, RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';

## 刷新
FLUSH PRIVILEGES;

3.1.5 编辑my.cnf配置,在mysql配置文件的尾部加入

##Xtrabackup备份配置
[xtrabackup]
defaults_file=/etc/my.cnf
user=bkpuser
password=Bkpuser_2026
parallel=4
compress
compress-threads=4

配置段	参数名	值	说明
[xtrabackup]	user	bkpuser	XtraBackup 连接 MySQL 所用的用户名（需具备 BACKUP_ADMIN 等权限）。
[xtrabackup]	password	Bkpuser_2026	XtraBackup 用户密码
[xtrabackup]	parallel	4	XtraBackup 并行备份线程数（加速备份过程）。
[xtrabackup]	compress	（无值）	启用备份压缩（使用 zlib）。
[xtrabackup]	compress-threads	4	压缩所用的线程数（与 parallel 解耦，可单独设置）。

3.1.6 重启mysql

systemctl restart mysqld

---

3.2 mydumper的安装部署

3.2.1 下载mydumper，建议下载稳定版本，例如每个大版本的最后一个版本。

mydumper点击跳转下载

3.2.2在目标库安装mydumper

yum -y install /mydata/soft/mydumper-0.20.2-5.el7.x86_64.rpm 

3.3 mysqldump的安装部署

mysqldump为mysql自带的工具，无需另外安装。

---

第四章 离线迁移操作指南

适用场景：可接受业务停机窗口

• 离线迁移前置准备工作（必做！）

  ⚠️ 目标：确保迁移期间无任何写入，保证备份数据绝对一致。

  ## 通知业务方
  1.邮件，通讯工具等方式进行通知业务方停机时间，时长等
  2.确认无定时任务，批处理作业正在窗口运行
  
  ## 关闭业务账号
  ALTER USER 'app_user'@'%' ACCOUNT LOCK;
  

4.1 全实例迁移（离线）

方案 A：XtraBackup（推荐）

# 源库：全量备份
xtrabackup --backup --target-dir=/backup/full_$(date +%Y%m%d)

# 源库 --》 目标库
将数据文件拷贝到目标库上

# 目标库： 1.解压（若为压缩格式）
xtrabackup --parallel=2 --decompress --target-dir=/mydata/temp_bak

# 目标库： 2.应用 redo log，完成一致性准备
xtrabackup --parallel=2 --prepare --target-dir=/mydata/temp_bak

# 目标库： 3.关闭mysql
systemctl stop mysqld

# 目标库： 4.备份当前数据目录（强烈推荐）
mv /mydata/data /mydata/data_bak
mkdir /mydata/data

# 目标库： 5.执行数据还原
xtrabackup --copy-back --target-dir=/mydata/temp_bak/full

# 目标库： 6.修复文件权限
chown -R mysql:mysql /mydata/data
chmod -R 750 /mydata/data

# 目标库：7.启动mysql
systemctl start mysqld

⚠️ 更加详细的步骤可参考以下文档，含xtrabackup的安装，部署，备份，恢复的全过程

• 安装部署，备份文档：
  1.【MySQL】从零搭建高性能、高可用的 MySQL 5.7 环境（附 XtraBackup 自动备份方案）
  2.【MySQL】从零搭建高性能、高可用的 MySQL 8.0 环境（附 XtraBackup 自动备份方案）

• 恢复文档：
  【MySQL】XtraBackup 全量备份还原操作指南（MySQL 5.7 / 8.0 通用）

方案 B：mydumper（仅限同版本）

1. 导出全实例

nohup mydumper \
  --host=192.168.237.145 \
  --port=3306 \
  --user=u_admin \
  --password=Power_2022 \
  --outputdir=/mydata/dump/alldb_$(date +%Y%m%d_%H%M) \
  --logfile=/mydata/dump/md_alldb_$(date +%Y%m%d_%H%M).log \
  --threads=8 \
  --compress \
  --skip-tz-utc \
  --routines \
  --events \
  --triggers \
  --verbose=3 \
  --no-trx-tables \
	--replica-data \
  --kill-long-queries \
  --long-query-guard=30 \
  > /mydata/dump/nohup.out 2>&1 &

参数	作用说明	注意事项 / 最佳实践
--host	指定 MySQL 主库的 IP 地址	确保网络可达，且防火墙开放 3306 端口
--port	指定 MySQL 服务端口	默认为 3306；若使用非标端口需显式指定
--user	指定连接数据库的用户名	该用户需具备：SELECT, RELOAD, PROCESS, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER 等权限
--password	指定用户密码	⚠️ 密码明文暴露在命令行历史中，建议改用 --ask-password 或配置文件（如 ～/.my.cnf）提升安全性
--outputdir	指定备份输出目录，路径中包含时间戳以避免覆盖	确保磁盘空间充足（建议 ≥ 原库压缩后大小的 1.5 倍）
--logfile	指定 mydumper 自身的日志文件路径	用于排查错误、监控进度，区别于 nohup.out
--threads	设置并行线程数（用于同时导出多个表）	建议设为 CPU 核数或略低；过高可能导致 I/O 瓶颈或主库负载飙升
--compress	启用 gzip 压缩（输出 .sql.gz 文件）	减少磁盘占用和网络传输体积；恢复时 myloader 自动解压
--skip-tz-utc	禁用默认的 SET TIME_ZONE='+00:00' 会话设置	✅ 推荐开启：保留源库原始时区，避免 DATETIME/TIMESTAMP 转换错误
--routines	导出存储过程和函数	若业务依赖存储过程，必须启用
--events	导出事件调度器（Event Scheduler）	需确保目标库已启用事件调度器（event_scheduler=ON）
--triggers	导出触发器	触发器依赖表结构，需与表一同恢复
--verbose	设置日志详细级别（0～3）3 = 最详细（含 SQL 语句）	调试时建议用 3，生产可降为 2
--no-trx-tables	对非事务引擎表（如 MyISAM）不使用 START TRANSACTION	避免 MyISAM 表因不支持事务而报错；配合 --less-locking 更安全
--replica-data	关键参数！ 在 metadata 中记录 SHOW MASTER STATUS 的 binlog 位点	✅ v0.21+ 版本必须显式指定，否则默认不记录位点，无法搭建主从
--kill-long-queries	自动 kill 阻塞 FLUSH TABLES WITH READ LOCK (FTWRL) 的长查询	防止备份卡住；需用户有 CONNECTION_ADMIN 或 SUPER 权限
--long-query-guard	设置长查询超时阈值（秒），超过则视为“阻塞查询”	与 --kill-long-queries 配合使用；根据业务调整（OLTP 建议 10～30 秒）

1. 还原

nohup myloader \
  --host=192.168.237.146 \
  --port=3306 \
  --user=u_admin \
  --password='Power_2022' \
  --threads=4 \
  --overwrite-tables \
  --verbose=3 \
  --directory=/mydata/dump/alldb_20260121_1137 \
  --logfile=/mydata/dump/myloader_alldb.log \
  > /dev/null 2>&1 &

参数	作用说明	注意事项 / 最佳实践
--host	指定目标 MySQL 从库的 IP 地址	确保网络可达，且防火墙开放 3306 端口
--port	指定目标 MySQL 服务端口	默认为 3306；若使用非标端口需显式指定
--user	指定连接目标数据库的用户名	该用户需具备：CREATE, INSERT, DROP（因 --overwrite-tables）、ALTER, CREATE ROUTINE, EVENT, TRIGGER 等权限
--password	指定用户密码	⚠️ 密码明文暴露在命令行历史中，建议改用 --defaults-file 配置文件提升安全性
--threads	设置并行恢复线程数（同时导入多个表）	重要：在 mydumper v0.21.x 中，建议设为 1 以避免“Unknown database”等并发竞态错误；v0.22+ 可安全使用多线程
--overwrite-tables	如果目标表已存在，则先执行 DROP TABLE 再重建	✅ 推荐开启：避免“Table already exists”错误；但会丢失原表数据，确保目标库无重要业务数据
--verbose	设置日志详细级别（0～3）3 = 最详细（含 SQL 执行信息）	调试时建议用 3，生产可降为 2；日志将写入 --logfile 指定文件
--directory	指定 mydumper 备份目录路径	必须包含 metadata 文件和 .sql.gz 数据文件；路径需存在且可读
--logfile	指定 myloader 的结构化日志输出文件	用于监控恢复进度、排查错误；区别于 shell 重定向日志

⚠️ 警告：mydumper 全库恢复必须在全新初始化的 MySQL 实例上执行！

---

4.2 业务库迁移（离线）

方案 A：mydumper（推荐）

1. 导出业务库

nohup mydumper \
  --host=192.168.237.145 \
  --port=3306 \
  --user=u_admin \
  --password=Power_2022 \
  --outputdir=/mydata/dump/alldb_$(date +%Y%m%d_%H%M) \
  --logfile=/mydata/dump/md_alldb_$(date +%Y%m%d_%H%M).log \
  --threads=8 \
  --compress \
  --skip-tz-utc \
  --routines \
  --events \
  --triggers \
  --verbose=3 \
  --no-trx-tables \
	--replica-data \
  --kill-long-queries \
  --long-query-guard=30 \
  --regex='^(?!(mysql\.|sys\.|information_schema\.|performance_schema\.))'
  > /mydata/dump/nohup.out 2>&1 &

#如果你只想导出用户自定义的业务库，通常排除以下系统库即可
--regex='^(?!(mysql\.|sys\.|information_schema\.|performance_schema\.))'

#只导出指定的数据库，例如导出school库
--database=school

参数	作用说明	注意事项 / 最佳实践
--regex	正则表达	可以通过这个排除或选择导出某些库
--database	指定某个库	可以指定某几个库导出，例如—database=school,zoo

1. 还原，同全实例迁移的导入命令

nohup myloader \
  --host=192.168.237.146 \
  --port=3306 \
  --user=u_admin \
  --password='Power_2022' \
  --threads=4 \
  --overwrite-tables \
  --verbose=3 \
  --directory=/mydata/dump/alldb_20260121_1137 \
  --logfile=/mydata/dump/myloader_alldb.log \
  > /dev/null 2>&1 &

方案 B：mysqldump（小库备用）

1. 导出业务库

nohup mysqldump \
  --host=192.168.237.145 \
  --port=3306 \
  --user=u_admin \
  --password='Power_2022' \
  --single-transaction \
  --master-data=2 \
  --routines \
  --events \
  --triggers \
  --set-gtid-purged=OFF \
  --skip-tz-utc \
  --compress \
  --quick \
  --lock-tables=false \
  --databases xwb> xwb.sql

注意：导出多库需在databases后面空格添加对应库，例如导出school、zoo两个库

--databases school zoo

1. 还原

mysql -h192.168.4.16 -P3306 -uroot -p'Power_2020' < xwb.sql &> my_demo.log &

💡 优势对比：

• mydumper 比 mysqldump 快 3～5 倍（多线程 + 压缩）

---

第五章 在线迁移与主从同步一体化操作指南

适用场景：业务不能停机
核心思想：在线迁移 = 全量备份（离线快照） + 主从复制（增量同步）

通过先恢复一个一致性全量备份，再配置主从自动追平增量，最终实现业务无感知切换

5.1 方案原理与流程

✅ 为什么在线迁移必须依赖主从？

• 全量备份阶段：业务仍在写入，备份结束时数据已“过时”
• 增量同步阶段：通过 MySQL 原生 binlog 复制机制，自动追平备份后产生的所有变更
• 切换阶段：当从库延迟（Seconds_Behind_Master）≈ 0 时，可秒级切换流量

🔁 标准流程图

[源主库]
   │
   ├─(1) 执行全量备份（mydumper / XtraBackup）
   │
   ├─(2) 将备份恢复到目标服务器 → [新从库]
   │
   ├─(3) 配置主从：从库连接主库，基于备份位点开始复制
   │
   └─(4) 监控延迟 → 切换应用连接 → 完成迁移

💡 优势：

• 业务写入全程不停机
• 数据强一致性（基于 binlog 位点）
• 切换窗口极短（仅需改 DNS 或连接串）

---

5.2 主从同步前置检查

5.2.1 主库前置条件检查

在源库（主库） 上执行以下检查：

✅ 1. 确认 binlog 已启用

SHOW VARIABLES LIKE 'log_bin';

• 期望结果：ON

• ❌ 若为 OFF，必须修改 my.cnf 并重启：

  [mysqld]
  log-bin=/mydata/binlog/mysql-bin
  server-id=1    # 必须唯一且非0
  

✅ 2. 确认 binlog 格式为 ROW（推荐）或 MIXED

SHOW VARIABLES LIKE 'binlog_format';

• 推荐值：ROW（避免 statement 模式下的不一致风险）

• 若为 STATEMENT，建议修改：

  binlog_format=ROW
  

✅ 3. 确认 server_id 已设置且唯一

SHOW VARIABLES LIKE 'server_id';

• 要求：非 0，且与所有从库不同
• 修改后需重启 MySQL

✅ 4. 创建专用复制用户（最小权限）

CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

🔒 安全建议：限制 IP 范围（如 ‘repl’@‘192.168.237.%’）

5.2.2 从库前置条件检查

在目标库（从库） 上执行：

✅ 1. 确认 server_id 已设置且与主库不同

SHOW VARIABLES LIKE 'server_id';

• 必须修改（默认常为 0 或与主库冲突）：

  [mysqld]
  server-id=2    # 唯一值，如 2, 3, 4...
  

✅ 2. 确认 关键配置项 是否配置

[mysqld]
# ... 其他配置 ...

server_id=200
master_info_repository=table
relay_log_info_repository=table
relay_log=/mydata/binlog/relay-log
relay_log_recovery=ON

• 修改后需重启 MySQL

---

5.3 全实例迁移（在线）

唯一推荐：XtraBackup + 主从同步

5.2.1 执行全量备份

参照4.1章节的A方案「XtraBackup 全实例迁移」，对数据库进行备份

5.2.2 获取binlog位点

位点文件：xtrabackup_binlog_info

在导出的备份文件中，有一个xtrabackup_binlog_info文件，进行查看可以获取到binlog位点

注意，若使用了压缩需要先解压，根据4.1章节的备份解压，我们的备份的解压文件在/mydata/temp_bak目录中，因此直接查看即可

5.2.3 配置主从同步

参考5.4章节「主从同步配置」进行配置

5.3 业务库迁移（在线）

方案 A：mydumper（推荐）

1. 执行业务库的全量备份

   参照4.2章节的A方案「mydumper业务库迁移」，对数据库进行备份

2. 获取binlog位点

   位点文件：metadata

   在导出的备份文件中，有一个metadata文件，进行查看一般在头部可以获取到binlog位点

   • 示例1（低版本mydumper）

   

   • 示例2（高版本mydumper）
     
     如以上示例，MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;

3. 配置主从同步

   参考5.4章节「主从同步配置」进行配置

方案 B：mysqldump（不推荐）

1. 执行全量备份

   参照4.2章节的B方案「mysqldump业务库迁移」，对数据库进行备份

2. 获取binlog位点

   位点文件：导出的sql文件头部

   示例：
   

3. 配置主从同步

   参考5.4章节「主从同步配置」进行配置

---

5.4 主从同步配置

5.4.1 登录从库的数据库

5.4.2 连接主库,修改以下对应的参数项

change master to
master_host='192.168.237.145',
master_user='repl',
master_password='Power_2020',
master_port=3306,
master_log_file='mysql-bin.000005',
master_log_pos=154;

5.4.3 启动同步

start slave;

5.4.4 监控同步情况

SHOW SLAVE STATUS\G
-- 关键字段：Slave_IO_Running=Yes, Slave_SQL_Running=Yes

5.4.5 完成数据同步，进行新旧库切换

当Seconds_Behind_Master为0 说明主从数据已同步完成,主从数据完全一致，此刻可以进行切换。

---

第六章 总结：迁移不是选择题，而是组合拳

在实际生产环境中，MySQL 数据迁移从来不是“用哪个工具最好”的单选题，而是一场需要精准判断 + 灵活组合 + 风险控制的系统工程。

✅ 核心决策逻辑回顾

你的需求	推荐方案
全实例重建（灾备/上云）	✅ XtraBackup（物理备份，秒级恢复）
只迁移部分业务库（微服务拆分等）	✅ mydumper（多线程逻辑导出，安全灵活）
小库临时迁移 & 无安装权限	⚠️ mysqldump（仅限 <10GB 场景）
业务不能停机	🔁 全量备份 + 主从同步（XtraBackup 或 mydumper 打底）

💡 最后忠告：“能不停机，就别停；能不碰 mysql 库，就别碰；能用 mydumper，就别用 mysqldump。”把这三句话刻在工牌背面，你的迁移成功率至少提升 80%！

---