09 数据迁移与版本升级——零停机变更的工程实践

摘要： 对一张存量 5 亿行的 orders 表加一列，或者修改一个字段的长度，在开发环境只需要几毫秒，但在生产环境却可能是一个需要精心规划的”手术”。MySQL 的 ALTER TABLE 在大表上可能执行数小时，期间表被锁定，所有写入请求排队等待，业务中断。本文深入解析 InnoDB 的 Online DDL 机制——为什么有些 DDL 操作可以不锁表、有些不行，以及 pt-online-schema-change 和 gh-ost 这两个业界主流工具的工作原理和适用场景。除了 Schema 变更，本文还涵盖跨库数据迁移（MySQL 到 MySQL、MySQL 到其他系统）的常用工具链，以及 MySQL 大版本升级的风险控制要点。

第 1 章 ALTER TABLE 的代价与风险

1.1 传统 ALTER TABLE 的阻塞机制

在 MySQL 5.5 及更早版本，ALTER TABLE 的执行过程是：

对表加元数据排他锁（MDL X Lock），阻塞所有对该表的读写操作
创建一张新的临时表，按照新的 Schema 定义建表
将原表的所有数据逐行 COPY 到临时表
在 COPY 期间，所有新写入的数据也被记录在日志中
COPY 完成后，将 COPY 期间的增量日志应用到新表
原子地将原表替换为新表

在第 1 步到第 6 步的整个过程中，原表被锁定，无法写入。对于一张 5 亿行的表，这个过程可能持续数小时，等同于业务中断。

1.2 在线 DDL 的需求

“零停机变更”是大型互联网系统的刚性需求：系统 24 小时运行，任何分钟级的停机都会导致巨大的业务损失和用户体验下降。

解决这个问题有两个方向：

数据库内置 Online DDL：MySQL 官方逐步在内核层面支持不阻塞 DML 的 Schema 变更
外部工具模拟：pt-osc 和 gh-ost 等工具通过创建影子表 + 增量同步的方式，绕过数据库的锁机制

第 2 章 InnoDB Online DDL 的工作原理

2.1 Online DDL 的三个阶段

MySQL 5.6+ 引入了 InnoDB Online DDL，支持部分 Schema 变更在不阻塞 DML 的情况下进行。整个过程分为三个阶段：

阶段一：初始化（Initialization）

获取元数据共享锁（MDL Shared Lock），确定 Online DDL 的执行策略（INPLACE 还是 COPY，是否允许并发 DML）。这一阶段时间极短。

阶段二：执行（Execution）

这是耗时最长的阶段。InnoDB 在不持有 MDL 排他锁的情况下，在线重建表结构或重建索引。

关键机制：Online DDL Row Log（行日志）。在执行阶段，如果允许并发 DML（ALLOW DML 模式），InnoDB 会在后台创建一个 Row Log 缓冲区，将执行阶段期间对原表的所有 DML 操作（INSERT/UPDATE/DELETE）记录下来。当主体 DDL 工作完成后，在最终应用阶段将 Row Log 中的增量操作应用到新结构上。

Row Log 缓冲区的大小由 innodb_sort_buffer_size 和 innodb_online_alter_log_max_size（默认 128MB）参数控制。如果执行期间的 DML 量过大（写入过于密集），Row Log 可能填满，此时 DDL 会报错失败。

阶段三：提交（Commit）

短暂获取 MDL 排他锁，将 Row Log 中的增量应用到新结构，完成表的切换（原子替换）。这一阶段通常只需要几秒。

2.2 ALGORITHM 与 LOCK 参数

MySQL 5.6+ 允许通过显式参数控制 DDL 的执行方式：

ALTER TABLE orders 
ADD COLUMN remark VARCHAR(256),
ALGORITHM=INPLACE,  -- 使用 INPLACE 算法（不 COPY 数据）
LOCK=NONE;          -- 不加锁，允许并发读写

ALGORITHM 参数：

INSTANT（MySQL 8.0.12+）：仅修改元数据，不重建数据，几乎瞬间完成，完全不阻塞
INPLACE：在原表上就地修改，不创建全量临时表，期间允许并发 DML（通过 Row Log 记录增量）
COPY：传统方式，创建临时表全量复制，期间阻塞写入

LOCK 参数：

NONE：不加锁，允许并发读写（最优，但并非所有操作都支持）
SHARED：允许并发读，阻塞写
EXCLUSIVE：完全锁表，阻塞读写
DEFAULT：由 InnoDB 根据操作类型自动选择

2.3 不同 DDL 操作的支持矩阵

DDL 操作	ALGORITHM	LOCK	说明
加列（INSTANT 支持的类型）	INSTANT	NONE	MySQL 8.0.12+，只修改元数据，瞬间完成
加列（普通类型）	INPLACE	NONE	需要重建表，但允许并发 DML
删列	INPLACE	NONE	需要重建表
加索引	INPLACE	NONE	在线建索引，允许并发 DML
删索引	INPLACE	NONE	只修改元数据
修改列名	INSTANT	NONE	MySQL 8.0，只修改元数据
修改列类型（兼容扩展）	INPLACE	NONE	如 `VARCHAR(100)` 改 `VARCHAR(200)`
修改列类型（不兼容）	COPY	SHARED	如 INT 改 VARCHAR，全量重建，阻塞写
修改字符集	COPY	EXCLUSIVE	全量重建，完全锁表
加主键	INPLACE	NONE	需要重建聚簇索引

生产避坑

ALGORITHM=INPLACE, LOCK=NONE 并非总是可用。在执行前先用 ALGORITHM=INPLACE, LOCK=NONE 的语法测试，如果 MySQL 报错说该操作不支持这个配置，不要强行降级到 ALGORITHM=COPY——这意味着业务需要停机窗口。此时应该考虑使用 pt-osc 或 gh-ost 外部工具来绕过这个限制。

2.4 INSTANT 算法：MySQL 8.0 的重大改进

MySQL 8.0.12 引入的 INSTANT 算法将”在表尾部加列”从 O(数据量) 的操作变成了 O(1) 的元数据操作：

原理：InnoDB 在数据字典（Data Dictionary）中记录”这张表在某个版本后增加了某些列，新列的默认值是 X”。物理数据页不做任何修改。读取行时，如果行的版本号比列添加时的版本号早，InnoDB 动态补充新列的默认值。

这使得以下操作可以在毫秒级完成，对业务完全无感知：

在表尾部加列（NOT NULL DEFAULT value 或 NULL）
修改列的默认值
添加/删除虚拟列

限制：INSTANT 不适用于在表中间位置插入列（需要重组数据页中列的排列），也不适用于需要修改行物理存储的操作。

第 3 章外部工具：pt-osc 与 gh-ost

3.1 为什么需要外部工具

InnoDB Online DDL 虽然好用，但有两个关键局限：

Row Log 大小限制：写入非常密集的表在 DDL 执行期间可能撑爆 Row Log，导致 DDL 失败。失败后需要回滚，重新开始
不支持主从一致性检查：Online DDL 完成后没有内置机制验证主从数据是否一致

外部工具通过完全不同的方式来规避这些问题。

3.2 pt-online-schema-change（pt-osc）

pt-osc 是 Percona 开发的 Schema 变更工具，核心思路是影子表 + 触发器同步：

执行过程：

1. 创建影子表（_orders_new），按新 Schema 定义
2. 在原表（orders）上创建三个触发器：
   - INSERT 触发器：新行同时插入影子表
   - UPDATE 触发器：原表更新同步到影子表
   - DELETE 触发器：原表删除同步到影子表
3. 将原表数据分批 COPY 到影子表（小批量，可控速率）
4. 等待影子表数据与原表完全同步
5. 短暂锁表，进行最终一致性校验
6. 原子重命名：RENAME TABLE orders TO _orders_old, _orders_new TO orders
7. 删除触发器，清理旧表

优点：

成熟稳定，功能丰富（自动重试、流量限速、暂停/恢复）
原表和影子表通过触发器实时同步，不依赖 Row Log

缺点：

触发器有额外的性能开销（每次 DML 都要多执行一次触发器逻辑）
在主从复制环境下，触发器可能引入复杂性（主库的触发器执行不会复制到从库）
如果原表已经有触发器，pt-osc 无法工作（MySQL 5.x 不支持同一表同类型多触发器）

pt-online-schema-change \
  --alter "ADD COLUMN remark VARCHAR(256)" \
  --host=127.0.0.1 \
  --user=root \
  --password=xxx \
  --execute \
  D=ecommerce,t=orders

3.3 gh-ost（GitHub Online Schema Transmogrifier）

gh-ost 是 GitHub 开发并开源的 Schema 变更工具，核心思路是影子表 + Binlog 增量同步，完全不使用触发器：

执行过程：

1. 创建影子表（_orders_ghc），按新 Schema 定义
2. gh-ost 伪装成 MySQL 从库，连接主库，接收 Binlog
3. 将原表数据分批 COPY 到影子表
4. 实时解析 Binlog，将 COPY 期间对原表的变更应用到影子表
5. 当影子表数据追上原表时（Binlog 位点基本同步），
   短暂锁表，完成最终同步
6. 原子重命名完成切换

优点：

无触发器：不增加原表的写入负担，对性能影响更小
可暂停/恢复/限速：通过修改 gh-ost 的控制文件，可以在 DDL 执行期间动态调整 chunk 大小、限制速率，甚至暂停
可测试：支持在从库上测试 DDL，确认无误后再在主库执行

缺点：

依赖 Binlog（主库必须开启 Binlog，且 binlog_format=ROW）
配置相对复杂

gh-ost \
  --mysql-password=xxx \
  --host=127.0.0.1 \
  --database=ecommerce \
  --table=orders \
  --alter="ADD COLUMN remark VARCHAR(256)" \
  --execute

选择建议：

场景	推荐工具
简单场景，原表无触发器，写入不太密集	`pt-osc`（成熟简单）
高写入压力，需要可控速率，需要暂停能力	`gh-ost`（更灵活）
MySQL 8.0，操作支持 INSTANT	直接 `ALTER TABLE ... ALGORITHM=INSTANT`
操作支持 INPLACE + NONE，Row Log 不会满	直接 `ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE`

第 4 章跨库数据迁移

4.1 mysqldump + 导入：适合小表和停机窗口

# 导出
mysqldump -h source_host -u root -p \
  --single-transaction \    # 一致性快照导出（不锁表）
  --master-data=2 \          # 记录导出时的 Binlog 位点
  ecommerce orders > orders.sql
 
# 导入
mysql -h target_host -u root -p ecommerce < orders.sql

--single-transaction 通过开启一个 RR 事务来获取一致性快照，导出期间不锁表（仅 InnoDB 支持）。适合数据量较小（GB 级别以内）或允许短暂数据延迟的场景。

4.2 物理备份 + 增量同步：适合大表迁移

步骤：

使用 xtrabackup 进行物理热备（不锁表，直接复制数据文件）
在目标库上恢复物理备份
使用 Binlog 将备份期间的增量变更同步到目标库（主从复制原理）
当主从延迟为 0 时，切换流量

# 物理备份（不锁表，支持 TB 级别的大库）
xtrabackup --backup --host=source --user=root --password=xxx \
  --target-dir=/backup/full/
 
# 恢复到目标库
xtrabackup --prepare --target-dir=/backup/full/
xtrabackup --copy-back --target-dir=/backup/full/
 
# 然后配置主从复制，追上 Binlog 增量

4.3 Canal + 消息队列：异构数据迁移

从 MySQL 迁移到 Elasticsearch、ClickHouse、Kafka 等异构系统时，Canal 订阅 Binlog 是标准方案：

MySQL → Binlog → Canal → Kafka → 目标系统（ES/ClickHouse/Redis等）

这不只是迁移工具，而是持续的数据同步架构——目标系统可以实时接收 MySQL 的变更，维持与 MySQL 的数据一致性。

第 5 章 MySQL 版本升级的风险控制

5.1 版本升级的核心风险

MySQL 大版本升级（如 5.7 → 8.0）的核心风险包括：

SQL 兼容性变化：某些在旧版本有效的 SQL 语法在新版本可能报错（如 GROUP BY 行为变化、ONLY_FULL_GROUP_BY 模式默认开启）
默认参数变化：innodb_flush_log_at_trx_commit、sync_binlog、character_set_server 等参数的默认值可能变化
废弃特性：旧版本的某些函数、语法、存储引擎可能在新版本被废弃或移除
性能变化：优化器的改进可能导致某些查询的执行计划变化，原本快的 SQL 可能变慢

5.2 升级前的检查清单

# 使用 mysqlcheck 检查所有表是否与新版本兼容
mysqlcheck -u root -p --all-databases --check-upgrade
 
# MySQL 官方提供的升级检查工具（MySQL 8.0 Shell）
mysqlsh -- util checkForServerUpgrade \
  --user=root \
  --host=localhost \
  --port=3306 \
  --targetVersion=8.0

重点检查项：

是否使用了 MySQL 8.0 的保留关键字（如 rank、groups 等）作为表名/列名
sql_mode 是否需要调整（8.0 默认开启 ONLY_FULL_GROUP_BY 等更严格的模式）
是否使用了 utf8 字符集（8.0 中 utf8 仍等于 utf8mb3，不是真正的 utf8mb4，但升级后最好统一改为 utf8mb4）
是否使用了已废弃的 query_cache（8.0 已彻底移除）
Binlog 格式是否兼容（建议升级前改为 ROW 格式）

5.3 推荐的升级流程

蓝绿升级方案（零停机）：

1. 搭建新版本的 MySQL 实例（从库）
2. 将旧版本主库的数据复制到新版本从库（xtrabackup + 主从复制）
3. 在新版本从库上进行兼容性测试（运行 SQL 回放工具，验证查询结果）
4. 流量灰度：将 1% 的读流量切到新版本从库观察
5. 如果没问题，逐步增大流量到新版本（10% → 50% → 100%）
6. 最终：将新版本提升为主库，旧版本降为从库（回滚路径保留）
7. 观察稳定后，关闭旧版本

第 6 章小结

本文的核心知识框架：

Online DDL 三阶段：短暂获取 MDL 共享锁（初始化）→ 在线重建表（执行，Row Log 记录增量）→ 短暂获取 MDL 排他锁（提交，应用增量）
ALGORITHM 选择优先级：能用 INSTANT 用 INSTANT（8.0.12+，瞬间完成）→ 能用 INPLACE+NONE 用 INPLACE → 需要 COPY 则考虑外部工具
pt-osc vs gh-ost：pt-osc 用触发器同步（简单但有额外开销）；gh-ost 用 Binlog 同步（无触发器，更灵活，支持暂停/限速）
数据迁移工具链：小表用 mysqldump；大表用 xtrabackup + 主从增量；异构系统用 Canal + Kafka
版本升级核心：升级前用官方工具做兼容性检查，使用蓝绿升级保留回滚路径，灰度切流量降低风险

思考题

MySQL 的权限系统基于’用户@主机’的身份认证。GRANT SELECT ON db.* TO 'reader'@'10.0.0.%' 授予特定 IP 段的只读权限。在微服务架构中（每个服务使用独立的数据库用户），如何管理数十个数据库用户的权限？LDAP 集成是否能简化管理？

MySQL 8.0 引入了角色（Role）——可以将权限集合赋给角色，再将角色赋给用户。在一个有’开发’、‘运维’、‘DBA’三种角色的团队中，你如何设计角色权限——开发只读生产数据、运维可以执行特定的管理命令、DBA 拥有所有权限？

SQL 注入是 Web 应用最常见的安全漏洞之一。除了使用 PreparedStatement（参数化查询），MySQL 层面有什么额外的防护措施？mysql_native_password 和 caching_sha2_password（MySQL 8.0 默认）在安全性方面有什么区别？

汀的知识碎片

文章导航

09 数据迁移与版本升级——零停机变更的工程实践

09 数据迁移与版本升级——零停机变更的工程实践

第 1 章 ALTER TABLE 的代价与风险

1.1 传统 ALTER TABLE 的阻塞机制

1.2 在线 DDL 的需求

第 2 章 InnoDB Online DDL 的工作原理

2.1 Online DDL 的三个阶段

2.2 ALGORITHM 与 LOCK 参数

2.3 不同 DDL 操作的支持矩阵

2.4 INSTANT 算法：MySQL 8.0 的重大改进

第 3 章外部工具：pt-osc 与 gh-ost

3.1 为什么需要外部工具

3.2 pt-online-schema-change（pt-osc）

3.3 gh-ost（GitHub Online Schema Transmogrifier）

第 4 章跨库数据迁移

4.1 mysqldump + 导入：适合小表和停机窗口

4.2 物理备份 + 增量同步：适合大表迁移

4.3 Canal + 消息队列：异构数据迁移

第 5 章 MySQL 版本升级的风险控制

5.1 版本升级的核心风险

5.2 升级前的检查清单

5.3 推荐的升级流程

第 6 章小结

On this page

汀的知识碎片

文章导航

09 数据迁移与版本升级——零停机变更的工程实践

09 数据迁移与版本升级——零停机变更的工程实践

第 1 章 ALTER TABLE 的代价与风险

1.1 传统 ALTER TABLE 的阻塞机制

1.2 在线 DDL 的需求

第 2 章 InnoDB Online DDL 的工作原理

2.1 Online DDL 的三个阶段

2.2 ALGORITHM 与 LOCK 参数

2.3 不同 DDL 操作的支持矩阵

2.4 INSTANT 算法：MySQL 8.0 的重大改进

第 3 章 外部工具：pt-osc 与 gh-ost

3.1 为什么需要外部工具

3.2 pt-online-schema-change（pt-osc）

3.3 gh-ost（GitHub Online Schema Transmogrifier）

第 4 章 跨库数据迁移

4.1 mysqldump + 导入：适合小表和停机窗口

4.2 物理备份 + 增量同步：适合大表迁移

4.3 Canal + 消息队列：异构数据迁移

第 5 章 MySQL 版本升级的风险控制

5.1 版本升级的核心风险

5.2 升级前的检查清单

5.3 推荐的升级流程

第 6 章 小结

On this page

第 3 章外部工具：pt-osc 与 gh-ost

第 4 章跨库数据迁移

第 6 章小结