一个并不安全的MySQL升级经历

# 一次并不安全的 MySQL 安全升级 来源：https://blog.elis.cc/articles/a-safe-mysql-upgrade-that-wasnt-so-safe/ 我收到通知，我的数据库版本已停止支持，必须立即升级。AWS 的扩展支持费用是促使我尽快升级的好动力。 我有一个可用的绿色副本，于是我升级了它，检查一切正常，然后进行了切换。 快捷又轻松，对吧？ 我也曾这么认为。然而，事实并非一切正确。一小时后，我收到奇怪的 bug 报告，于是检查数据库，发现一个特定表（我们称之为*表 X*）的 ID 分配顺序异常。在旧数据库中 ID 为 1 的行，现在变成了 ID 26。 这个表在另外 6 个表中被引用，其中 5 个表正确引用了新 ID，但有一个表不知何故使用了旧数据库的 ID，现在这些 ID 指向了完全不同的行。 这真是令人费解。事情怎么会变得如此混乱？ ## 迁移过程 不久前，在升级之前，执行了一个迁移操作，为*表 X* 添加了新的自增主键： ```sql ALTER TABLE X ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY; ``` 迁移还更新了 6 个相关表，让它们引用新的 ID 而非旧 ID。每个表的更新大致如下： ```sql UPDATE some_table JOIN x ON x.old_id = some_table.x_old_id SET some_table.x_id = x.id; ``` ## AUTO_INCREMENT 列 事实证明，向复制表添加 `AUTO_INCREMENT` 列可能导致源表和副本表的行获得不同的 ID。 根据 MySQL 关于复制与 AUTO_INCREMENT 的文档 (https://dev.mysql.com/doc/refman/8.0/en/replication-features-auto-increment.html)，通过 `ALTER TABLE` 添加 `AUTO_INCREMENT` 列可能不会在源和副本上产生相同的行排序。ID 的分配顺序取决于存储引擎和行处理顺序。 好吧，我当时并不知道这一点。 但为什么新字段在 6 个表中有 5 个能正确引用，而另一个表却完全出错了？ ## 二进制日志格式 这是更令人困惑的地方。 MySQL 复制使用“二进制日志 (https://dev.mysql.com/doc/refman/8.0/en/binary-log.html)”记录源上发生的更改。然后这些更改被发送到副本，副本用它们来重现相同的事务。 记录的内容以及在副本上应用的方式取决于二进制日志的“格式 (https://dev.mysql.com/doc/refman/8.0/en/binary-log-formats.html)”。MySQL 支持 3 种“格式”： - `STATEMENT`：SQL 语句本身被写入二进制日志，副本执行该语句。 - `ROW`：对单行的更改被写入二进制日志，这些行更改直接应用到副本。 - `MIXED`：在混合日志记录中，默认使用基于语句的日志记录，但在某些情况下会自动切换到基于行的记录模式。 显然，我的源数据库配置了 `binlog_format` 为 `MIXED`。 因此，那 5 个表能正确引用新 ID 的原因，是它们的更新语句是使用 `STATEMENT` 模式复制的。完全相同的 `UPDATE` 语句在副本上再次执行。它查找副本本地的 `x.id` 版本，并将正确的本地 ID 写入每个相关表。 但对于剩下的那个表，MySQL 决定改用 `ROW` 模式……在这种模式下，副本不会运行原始的 `UPDATE` 语句。它接收来自源的行变更结果，并直接应用。 因此，源上生成的 `x_id` 值被复制到了副本。但由于*表 X* 在副本上具有不同的 ID，这些值现在指向了完全不同的行。 你可能会问，为什么 MySQL 只对一个表决定使用 `ROW` 模式？ 我唯一能找到的这个表与其他 5 个表的区别在于，这个表有一个 `AUTO_INCREMENT` 列。 MySQL 确实有特定情况涉及 `AUTO_INCREMENT`，它认为这对基于语句的复制不安全，因此在 `MIXED` 模式下会使用 `ROW` 格式记录。 这真是讽刺，不是吗？一切似乎最终都归结于 `AUTO_INCREMENT` 功能。 ## 结论 请小心对待 MySQL 副本。这类问题的可怕之处在于它不可预期且容易被忽略，但它可能迅速演变成生产环境灾难，让你最终疑惑事情是如何变成这样的。