强曰为道
与天地相似，故不违。知周乎万物，而道济天下，故不过。旁行而不流，乐天知命，故不忧.
文档目录

MySQL 完全指南 / 第 19 章：数据迁移

第 19 章：数据迁移

版本升级、跨库迁移、在线表结构变更——数据迁移是 DBA 最高危的操作之一。

19.1 版本升级

19.1.1 升级路径

当前版本目标版本升级方式
5.78.0支持原地升级
5.78.4先升到 8.0，再升到 8.4
8.08.4支持原地升级

19.1.2 原地升级步骤

# 1. 备份（必须！）
mysqldump -uroot -p --single-transaction --all-databases > full_backup.sql

# 2. 检查兼容性
mysqlcheck -uroot -p --all-databases --check-upgrade

# 3. 停止 MySQL
sudo systemctl stop mysql

# 4. 更新软件包
sudo apt update && sudo apt install -y mysql-server-8.4

# 5. 启动 MySQL（自动执行升级）
sudo systemctl start mysql

# 6. 执行升级检查
mysql_upgrade -uroot -p  # 8.0.16+ 自动执行

# 7. 验证
mysql -uroot -p -e "SELECT VERSION();"

19.1.3 常见升级问题

问题解决方案
SQL Mode 更严格修改 sql_mode，修复不兼容的 SQL
认证插件变更ALTER USER ... IDENTIFIED WITH mysql_native_password
保留字冲突用反引号包裹或重命名
字符集变更检查并修正 utf8mb4_0900_ai_ci
密码策略调整 validate_password 参数

19.2 跨库迁移

19.2.1 MySQL → MySQL

# 方案 1：mysqldump + mysql
mysqldump -h source_host -uroot -p --single-transaction myapp | \
  mysql -h target_host -uroot -p myapp

# 方案 2：XtraBackup 物理迁移
# 适合大数据库（> 100GB）
xtrabackup --backup --target-dir=/backup/full
# 复制到目标服务器
rsync -avz /backup/full target_host:/backup/
# 在目标服务器恢复
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

19.2.2 不同数据库间迁移

# MySQL → PostgreSQL（使用 pgloader）
pgloader mysql://user:pass@source_host/myapp \
         postgresql://user:pass@target_host/myapp

# 使用 AWS DMS（数据库迁移服务）
# 支持 MySQL → Aurora、RDS 等云数据库迁移

19.3 在线表结构变更

19.3.1 问题背景

直接 ALTER TABLE 会锁表，在大表上执行可能阻塞业务数小时。

-- ❌ 直接 ALTER（大表会锁很久）
ALTER TABLE big_table ADD COLUMN new_col VARCHAR(100);

19.3.2 pt-online-schema-change（Percona Toolkit）

# 安装
sudo apt install percona-toolkit

# 在线添加列
pt-online-schema-change \
  --alter "ADD COLUMN new_col VARCHAR(100) DEFAULT ''" \
  --user=root --password=MyPass \
  --host=127.0.0.1 \
  --execute \
  D=myapp,t=big_table

# 在线添加索引
pt-online-schema-change \
  --alter "ADD INDEX idx_email (email)" \
  --user=root --password=MyPass \
  --host=127.0.0.1 \
  --execute \
  D=myapp,t=users

# 在线修改列类型
pt-online-schema-change \
  --alter "MODIFY COLUMN phone VARCHAR(20) NOT NULL DEFAULT ''" \
  --user=root --password=MyPass \
  --host=127.0.0.1 \
  --execute \
  D=myapp,t=users

工作原理：

  1. 创建与原表结构相同的新表
  2. 在新表上执行 ALTER
  3. 创建触发器，同步原表的增量变更
  4. 分批将原表数据复制到新表
  5. 原子 RENAME TABLE 替换

19.3.3 gh-ost（GitHub Online Schema Change）

# 安装
# https://github.com/github/gh-ost/releases

# 基本用法
gh-ost \
  --host=127.0.0.1 \
  --port=3306 \
  --user=root --password=MyPass \
  --database=myapp \
  --table=users \
  --alter="ADD COLUMN nickname VARCHAR(50) DEFAULT ''" \
  --allow-on-master \
  --execute

# 使用从库减轻主库压力
gh-ost \
  --host=slave_host \
  --port=3306 \
  --user=root --password=MyPass \
  --database=myapp \
  --table=users \
  --alter="ADD INDEX idx_phone (phone)" \
  --initially-drop-ghost-table \
  --initially-drop-old-table \
  --execute

gh-ost vs pt-osc：

维度pt-oscgh-ost
增量同步触发器Binlog
对主库影响中（触发器开销）低（读 Binlog）
可暂停不支持支持
可动态调速不支持支持
外键支持有限不支持
推荐简单场景大表、复杂场景

19.4 MySQL 8.0 Instant DDL

MySQL 8.0 支持即时 DDL，部分 ALTER 操作不需要重建表：

-- ✅ 即时操作（INSTANT）
ALTER TABLE users ADD COLUMN age INT DEFAULT 0;           -- 瞬间完成
ALTER TABLE users ADD COLUMN bio VARCHAR(500) DEFAULT '';  -- 瞬间完成
ALTER TABLE users DROP COLUMN age;                         -- 瞬间完成（8.0.29+）
ALTER TABLE users ALTER COLUMN status SET DEFAULT 2;       -- 瞬间完成

-- ❌ 需要重建表
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);     -- 需要重建
ALTER TABLE users ADD INDEX idx_bio (bio);                 -- 需要重建

19.5 大表 DDL 操作策略

表大小推荐方案预计时间
< 1GB直接 ALTER TABLE秒级
1GB - 10GBMySQL 8.0 Instant DDL秒级（如果支持）
10GB - 100GBpt-osc 或 gh-ost分钟到小时
> 100GBgh-ost + 低峰期小时级

业务场景

场景 1：生产大表加字段

# 需要：在 50GB 的 orders 表上添加 remark 字段

# 方案：使用 gh-ost
gh-ost \
  --host=mysql-master \
  --port=3306 \
  --user=ghost_user \
  --password='Ghost!Pass' \
  --database=ecommerce \
  --table=orders \
  --alter="ADD COLUMN remark VARCHAR(500) DEFAULT ''" \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --serve-socket-file=/tmp/gh-ost.sock \
  --initially-drop-ghost-table \
  --execute

扩展阅读