第 69 章 升级迁移踩坑

📖 本篇简介：MySQL 大版本升级和数据迁移是高风险操作——升级前不备份、跨大版本直跳、字符集乱码、回滚无预案，每个都能变成大事故。本篇基于 MySQL 8.0，梳理升级路径、备份、字符集、SQL_MODE、灰度回滚等 11 个高频坑。前置知识是主从复制与备份恢复。学完后你应能规划一条"可回滚、可灰度、可校验"的升级迁移路径。

目录

• 坑 1：跨大版本直接跳级升级
• 坑 2：升级前不备份
• 坑 3：升级后字符集乱码
• 坑 4：SQL_MODE 变更导致 SQL 报错
• 坑 5：数据字典升级失败难回滚
• 坑 6：灰度不充分直接全量切换
• 坑 7：驱动版本未同步升级
• 坑 8：升级后统计信息未更新
• 坑 9：复制协议版本不兼容
• 坑 10：存储过程触发器升级后失效
• 坑 11：回滚预案缺失
• 升级常见误区对照
• 升级迁移方法论
• 速查表：升级检查清单
• 自测练习

坑 1：跨大版本直接跳级升级

场景 / 错误示范：

业务从 MySQL 5.5 直接升到 8.0，跳过 5.6、5.7。升级过程中数据字典转换多次失败，折腾一天还没起来，业务停摆。

# 错误：跨多个大版本直接跳级升级
# 5.5 -> 8.0，数据字典要经历多次不兼容转换

为什么是坑：MySQL 大版本之间数据字典、系统表结构变化很大。官方推荐逐级升级（5.5→5.6→5.7→8.0），每级都有专门的转换逻辑。跨大版本直跳，转换链路过长，每一步都可能出问题，回滚也复杂。升级不是"装个新版覆盖"，而是数据字典的多次迁移。

正确做法：

逐级升级，或用逻辑迁移（dump + 新库导入）替代原地升级。

# 正确：要么逐级升级，要么逻辑迁移
# 方式 1：逐级原地升级 5.5->5.6->5.7->8.0
# 方式 2：更稳妥——逻辑迁移：老库 dump，导入全新 8.0 库
mysqldump --single-transaction old_db > all.sql
mysql80 new_db < all.sql

加分点：逻辑迁移（dump 到全新 8.0）反而比原地升级可控——新库干净，出问题不影响老库，回滚容易。

坑 2：升级前不备份

场景 / 错误示范：

DBA 自信升级没问题，直接在生产库执行升级，没做备份。升级中途数据字典转换失败，老库已经被改坏，又没有备份，数据救不回来。

# 错误：不备份就升级
mysql_upgrade   # 直接跑，失败了老库也坏了，没退路

为什么是坑：升级是对数据库结构的大改动，任何一步失败都可能损坏数据。没有备份，升级失败就是不可逆事故。升级前备份不是"可选项"，是"铁律"——哪怕你再自信，也要留好退路。

正确做法：

升级前做完整备份（物理备份最稳）。

# 正确：升级前先做物理备份（本示例为 xtrabackup 思路）
xtrabackup --backup --target-dir=/backup/pre_upgrade
# 确认备份可用后，再开始升级

加分点：除了全量备份，升级前还应在预发环境用同一份数据完整演练一遍升级流程。

坑 3：升级后字符集乱码

场景 / 错误示范：

5.7 升到 8.0 后，中文数据变乱码。原因是 8.0 默认字符集从 latin1 变成 utf8mb4，表/连接字符集不一致，升级后中文展示全乱。

-- 错误：升级前没统一字符集，升级后连接字符集对不上
-- 本示例基于 MySQL 8.0
SHOW VARIABLES LIKE 'character_set%';   -- 连接字符集与表字符集不一致

为什么是坑：MySQL 5.7 默认字符集是 latin1，8.0 默认 utf8mb4。升级时如果表字符集、连接字符集、客户端字符集没对齐，中文就会乱码。字符集问题在升级后暴露，是因为默认值变了。升级前必须把字符集策略理清楚，而不是升级后再救火。

正确做法：

升级前统一字符集为 utf8mb4，确认连接配置。

-- 升级前：确认表和连接都是 utf8mb4（本示例基于 MySQL 8.0）
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'character_set_database';
-- my.cnf 统一设置：
-- [mysqld]
-- character_set_server=utf8mb4
-- collation_server=utf8mb4_0900_ai_ci

# 正确：my.cnf 明确字符集，不依赖默认值
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci

加分点：8.0 的 utf8mb4 排序规则和 5.7 不同（utf8mb4_0900_ai_ci vs utf8mb4_general_ci），升级后 JOIN/排序结果可能细微变化，需测试。

坑 4：SQL_MODE 变更导致 SQL 报错

场景 / 错误示范：

8.0 默认 SQL_MODE 更严格（含 ONLY_FULL_GROUP_BY）。升级后，以前能跑的 GROUP BY 语句突然报错，因为 select 的非聚合列没都在 group by 里。

-- 错误：8.0 严格模式下，旧 SQL 报错
-- 本示例基于 MySQL 8.0
SELECT dept, name, COUNT(*) FROM emp GROUP BY dept;
-- 报错：name 没在 GROUP BY 里，ONLY_FULL_GROUP_BY 拒绝执行

为什么是坑：8.0 默认 SQL_MODE 比 5.7 严格很多，ONLY_FULL_GROUP_BY、严格类型检查等都会让旧 SQL 突然报错。这些 SQL 在老版本"能跑"是因为模式宽松，升级后暴露出来。不预先排查不兼容 SQL，升级后线上会批量报错。

正确做法：

升级前扫描不兼容 SQL，或先兼容调整 SQL_MODE 过渡。

-- 1) 查看 8.0 默认 SQL_MODE（本示例基于 MySQL 8.0）
SELECT @@sql_mode;
-- 通常含 ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES 等

-- 2) 过渡期可先放宽，逐步改造 SQL（不推荐长期，但可过渡）
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
-- 3) 根治：把不兼容 SQL 改对，例如 group by 补全字段或用 ANY_VALUE()

-- 正确：改造不兼容 SQL
SELECT dept, ANY_VALUE(name), COUNT(*) FROM emp GROUP BY dept;

加分点：用 8.0 的"升级检查工具"提前扫描不兼容 SQL，比升级后线上报错好。

坑 5：数据字典升级失败难回滚

场景 / 错误示范：

原地升级中途数据字典转换失败，进程起不来。老版本的二进制也没法再启动（数据字典已被部分改坏），进退两难。

# 错误：原地升级失败，老版本起不来、新版本也起不来
# 数据字典处于中间状态，两头不靠

为什么是坑：原地升级会直接改数据字典，一旦中途失败，老版本读不新字典、新版本又没转完，数据库就卡死在中间态。原地升级的回滚代价极高——这也是为什么更推荐逻辑迁移（新库导入），老库全程不动。

正确做法：

优先逻辑迁移，老库保持不动作为回滚退路。

# 正确：逻辑迁移——新库导入，老库只读保留
# 1) 在全新 8.0 实例上导入老库数据
# 2) 新库验证无误后，再切流量
# 3) 出问题？切回老库即可，老库从没被动过

加分点：原地升级只在有完善备份、且逻辑迁移不可行时才用，并且必须能快速回滚到备份。

坑 6：灰度不充分直接全量切换

场景 / 错误示范：

新库升级后只测了几条 SQL，就直接全量切流量。结果某个冷门业务 SQL 在 8.0 上行为不同，全量用户报错。

# 错误：测几条就全量切，没有灰度
# 本示例为通用流程
switch_traffic --to new_db --all   # 一把全量切

为什么是坑：升级后即使核心 SQL 都测了，也总有冷门 SQL、边界场景没覆盖。全量切换等于把所有用户一次押上，一出问题就是大面积故障。正确做法是灰度——先小流量、部分业务、部分用户，观察没问题再逐步放大。

正确做法：

灰度切流，逐步放大。

# 正确：灰度路径
1) 先切 1% 流量到新库，观察半天
2) 无异常，切 10% -> 50% -> 100%
3) 每一步都监控错误率、RT，异常立即切回老库

加分点：灰度期间保留"一键切回老库"的能力，是灰度的安全垫。

坑 7：驱动版本未同步升级

场景 / 错误示范：

数据库升到 8.0，但应用还用着老版本的 JDBC 驱动。老驱动不认识 8.0 的认证插件（caching_sha2_password），连接直接报错。

// 错误：老驱动连 8.0，认证插件不兼容
// 老 mysql-connector-java 版本不支持 caching_sha2_password
Connection conn = DriverManager.getConnection(url, user, pwd);
// 报错：Unable to load authentication plugin 'caching_sha2_password'

为什么是坑：8.0 默认认证插件从 mysql_native_password 改成了 caching_sha2_password。老版本 JDBC/驱动不支持这个新插件，连不上。升级数据库必须同步升级应用驱动，否则应用连新库都连不上。

正确做法：

升级数据库同时升级客户端驱动版本。

// 正确：用支持 8.0 的驱动版本（本示例为 JDBC）
// mysql-connector-java 8.x 支持 caching_sha2_password
// Maven: mysql:mysql-connector-java:8.0.x

-- 过渡期也可以让新账号用老认证插件（本示例基于 MySQL 8.0）
CREATE USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY 'xxx';
-- 但根治还是升级驱动

加分点：升级清单里"驱动版本"必须和"数据库版本"一起核对，别漏。

坑 8：升级后统计信息未更新

场景 / 错误示范：

升级完，数据库起来了，但查询比升级前慢。因为升级后优化器统计信息是旧的，执行计划选错了索引。

-- 错误：升级后不收集统计信息，优化器基于旧统计选错计划
-- 本示例基于 MySQL 8.0
SELECT * FROM t_order WHERE user_id = 10086;   -- 走了全表扫描，实际有索引

为什么是坑：优化器靠统计信息（行数、基数分布）选执行计划。升级迁移后，统计信息可能不准，导致执行计划变差——明明有索引却走全表。升级后慢，很多时候不是 8.0 本身慢，而是统计信息没刷新。

正确做法：

升级后更新统计信息。

-- 正确：升级后分析所有表（本示例基于 MySQL 8.0）
ANALYZE TABLE t_order;
-- 或对关键表逐一分析

-- 8.0 也支持持久化统计信息，升级后确认自动采样正常
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';

加分点：升级后发现"某些查询突然变慢"，先 ANALYZE TABLE 再怀疑版本问题。

坑 9：复制协议版本不兼容

场景 / 错误示范：

主库升了 8.0，从库还是 5.7。8.0 的 binlog 格式和 5.7 复制协议有差异，从库 IO/SQL 线程报错，复制中断。

-- 错误：8.0 主库配 5.7 从库，版本差距太大复制中断
-- 本示例基于 MySQL 8.0
-- Replica_IO_Running 报错：binlog 版本不兼容

为什么是坑：主从版本要兼容——官方要求从库版本 >= 主库版本（高版本从库能读低版本主库 binlog，反之不行）。主库升 8.0、从库还是 5.7，复制协议对不上，复制中断。升级顺序必须是"先升从库，再升主库"。

正确做法：

先升从库到 8.0，最后升主库。

# 正确：升级顺序——先从后主
# 1) 所有从库升到 8.0
# 2) 把主库切换到某个新从库（见高可用篇）
# 3) 老主库升级后作为新从库

-- 升级后确认复制正常（本示例基于 MySQL 8.0）
SHOW REPLICA STATUS\G
-- Replica_IO_Running=Yes, Replica_SQL_Running=Yes

加分点：8.0 主从复制建议统一小版本，避免 binlog 格式细微差异。

坑 10：存储过程触发器升级后失效

场景 / 错误示范：

升级后，业务报某个存储过程调用失败。因为存储过程里用了 5.7 特有的语法或行为，8.0 上不兼容， silently 失效。

-- 错误：存储过程用了 5.7 行为，8.0 上结果不对
-- 本示例基于 MySQL 8.0
CREATE PROCEDURE old_proc()
BEGIN
  -- 里面依赖了 5.7 的 group by 宽松行为或废弃函数
END;

为什么是坑：存储过程、触发器、事件、视图这些"数据库内部对象"在升级后可能因为语法、函数、行为变化而失效或结果不对。它们不像应用 SQL 那样容易被测试覆盖，升级后很容易成为漏网之鱼。

正确做法：

升级后逐一回归所有内部对象。

-- 升级后检查内部对象状态（本示例基于 MySQL 8.0）
SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES;
SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS;
-- 逐一调用关键存储过程，对比升级前后结果

加分点：升级前把所有存储过程/触发器列清单，升级后逐个回归，别漏。

坑 11：回滚预案缺失

场景 / 错误示范：

升级进行到一半出问题，DBA 慌了——没有回滚预案，不知道怎么退回去。硬着头皮往前修，越修越糟，业务停摆数小时。

# 错误：升级只准备了"怎么升"，没准备"升坏了怎么退"

为什么是坑：升级是高风险操作，必然要有"失败了怎么回滚"的预案。没有回滚预案，一旦中途出问题，DBA 只能临场发挥，往往越弄越糟。回滚预案和升级步骤同等重要——甚至更重要，因为出事时才用得上。

正确做法：

升级前写好回滚预案并演练。

# 正确：回滚预案
# 触发回滚的条件：错误率超 X% / RT 涨 Y% / 升级 Z 分钟未完成
# 回滚步骤：
#   1) 切流量回老库（老库只读保留着）
#   2) 新库下线
#   3) 事后分析，再安排下次升级
# 回滚要能在几分钟内完成，平时演练过

加分点：逻辑迁移之所以稳，正是因为老库全程不动，回滚就是切个流量。

升级常见误区对照

升级事故大多来自"想当然"。下表把最常见的错误心态和正确做法摆在一起，方便对照自查。

| 误区 | 正解 |

|---|---|

| "新版本肯定更好，直接升" | 升级是高风险操作，必须备份+演练+灰度 |

| "我在测试库升过了" | 测试库数据量和真实业务差远了，预发要近似生产 |

| "升坏了大不了重装" | 数据字典坏了重装也救不回数据，备份才是退路 |

| "SQL 都测过了" | 冷门 SQL、存储过程、触发器最容易漏，要全量回归 |

| "一次升完省事" | 灰度逐步放大，出问题能立刻切回 |

| "数据库升完就完了" | 驱动、字符集、统计信息、复制都要同步处理 |

一句话总结：升级迁移的成败，70% 在升级前的准备（备份、演练、扫描不兼容项），30% 在升级中的灰度与回滚。真正动手升级的那几分钟，反而是最不需要"临场发挥"的——所有决定都应该在准备阶段做完。

升级迁移方法论

升级迁移的核心是"可控"——每一步都知道怎么进、怎么退。以下是一套经过验证的升级路径。

| 阶段 | 做什么 | 关键风险 |

|---|---|---|

| 准备 | 备份、预发演练、扫描不兼容项 | 不备份、不测就上 |

| 字符集/模式 | 统一 utf8mb4、评估 SQL_MODE | 乱码、SQL 报错 |

| 灰度 | 小流量逐步放大 | 一把全量切 |

| 验证 | 统计信息、内部对象、复制 | 慢查询、对象失效 |

| 回滚 | 预案就绪、一键切回 | 无预案临场慌 |

核心认知：升级迁移最怕的不是"升级失败"，而是"失败了退不回去"。逻辑迁移（新库导入、老库保留只读）之所以比原地升级稳，就是因为它天然带了退路——出问题切回老库即可。能逻辑迁移就别原地升级。

版本顺序铁律：先升从库、后升主库；先升应用驱动、后升数据库；先小流量灰度、后全量切换。顺序反了，复制中断、应用连不上、全量故障都可能发生。

速查表：升级检查清单

| 检查项 | 常见坑 | 正确做法 |

|---|---|---|

| 升级路径 | 跨大版本直跳 | 逐级或逻辑迁移 |

| 备份 | 升级前不备份 | 物理备份+预发演练 |

| 字符集 | 升级后乱码 | 统一 utf8mb4 |

| SQL_MODE | 旧 SQL 报错 | 扫描不兼容 SQL 改造 |

| 回滚 | 无预案 | 逻辑迁移老库保留 |

| 驱动 | 老驱动连不上 | 同步升级驱动 |

| 统计信息 | 升级后变慢 | ANALYZE TABLE |

| 复制 | 主从版本倒挂 | 先从后主 |

自测练习

1. 为什么不推荐跨大版本直接原地升级？

<details>

<summary>参考答案</summary>

大版本间数据字典变化大，跨多级直跳转换链路过长易失败，且原地升级中途失败会卡在"老版本读不了、新版本没转完"的中间态。应逐级升级，或更稳妥地用逻辑迁移（dump 导入全新 8.0 库，老库保留作回滚退路）。

</details>

2. 8.0 升级后旧的 GROUP BY SQL 突然报错，为什么？

<details>

<summary>参考答案</summary>

8.0 默认 SQL_MODE 含 ONLY_FULL_GROUP_BY，select 的非聚合列必须都在 GROUP BY 里。应升级前扫描不兼容 SQL 并改造（补字段或用 ANY_VALUE()），过渡期可放宽 SQL_MODE。

</details>

3. 升级后查询突然变慢，先怀疑什么？

<details>

<summary>参考答案</summary>

先怀疑统计信息不准导致优化器选错执行计划，ANALYZE TABLE 关键表后再观察。其次才考虑 8.0 本身的行为差异。

</details>

4. 主从版本升级的正确顺序是什么？

<details>

<summary>参考答案</summary>

先升从库到 8.0，再把主库切到新从库，老主升级后作为新从库。因为从库版本要 >= 主库版本才能正确解析 binlog，主从倒挂会复制中断。

</details>

5. 为什么逻辑迁移比原地升级更安全？

<details>

<summary>参考答案</summary>

逻辑迁移在全新 8.0 实例导入数据，老库全程只读保留，升级失败只需把流量切回老库，天然带退路。原地升级直接改数据字典，中途失败两头不靠，回滚代价极高。

</details>

下一篇预告：第 70 章 安全与合规踩坑