MySQL 完全指南 / 第 19 章：数据迁移
第 19 章：数据迁移
版本升级、跨库迁移、在线表结构变更——数据迁移是 DBA 最高危的操作之一。
19.1 版本升级
19.1.1 升级路径
| 当前版本 | 目标版本 | 升级方式 |
|---|---|---|
| 5.7 | 8.0 | 支持原地升级 |
| 5.7 | 8.4 | 先升到 8.0，再升到 8.4 |
| 8.0 | 8.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
工作原理：
- 创建与原表结构相同的新表
- 在新表上执行 ALTER
- 创建触发器，同步原表的增量变更
- 分批将原表数据复制到新表
- 原子 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-osc | gh-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 - 10GB | MySQL 8.0 Instant DDL | 秒级（如果支持） |
| 10GB - 100GB | pt-osc 或 gh-ost | 分钟到小时 |
| > 100GB | gh-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