☰

几乎无需停机即可修改大型 mysql 表的列

tag-icon • tag-icon mysql database database-administration innodb master-slave
几乎无需停机即可修改大型 mysql 表的列

我定期需要对 mysql 5.1 中的表进行更改，主要是添加列。使用 alter table 命令非常简单。但是我的表现在有多达 4000 万行，而且它们增长得很快……所以这些 alter table 命令需要几个小时。我猜几个月后它们会花几天时间。

由于我使用的是 amazon RDS，因此我无法使用从服务器，然后再将其提升为主服务器。所以我的问题是，是否有办法以最少的停机时间做到这一点？当然，如果用户仍然可以使用数据库，我不介意操作花费数小时甚至数天的时间……他们至少可以在添加列时读取吗？如果我的应用程序尝试写入会发生什么？插入还是更新？如果它立即失败，那实际上并不是那么糟糕，如果它只是挂起并导致数据库服务器出现问题，那就是一个大问题了。

这肯定是一个相当常见的扩展问题，每个人都需要添加列。通常对生产数据库做什么？从数据库 -> 主数据库迁移？

更新- 我忘了说我使用的是 innodb 存储引擎

答案1

我需要定期对 mysql 5.1 中的表进行更改，主要是添加列。

不要。真的不要。不要。这种情况应该很少见曾经必要的。

假设您的数据确实是从一开始就规范化的，那么解决问题的正确方法是添加一个与基表具有 1：1 关系的新表（在新表上不是强制性的）。

必须定期添加列通常表明数据库未规范化 - 如果您的模式未规范化，那么这就是您需要修复的问题。

最后，如果您的模式确实已经规范化，并且您确实必须继续添加列，那么：

  1. 确保数据库中有一个时间戳列，或者它正在生成复制日志
  2. 创建表 (A) 的副本 (B)
  3. 将新列添加到 B（这仍然会阻止 myisam）
  4. 禁用交易
  5. 将原始表（A）重命名为其他名称（备份）
  6. 将新表 (B) 重命名为原始表 (A) 的名称
  7. 从复制日志或备份表中重播操作开始处的事务
  8. 启用交易。

答案2

我最近不得不这么做。亚马逊推荐使用 Percona Toolkit。我下载了它并能够运行如下命令：

./pt-online-schema-change h=databasenameHostName,D=databasename,t=tablename --recursion-method=none --execute --user username --password password --alter "MODIFY someColumn newDataType"

而且效果很好。它会告诉你这个过程还剩多少时间。

它实际上会创建一个包含新列的新表，然后复制现有数据。此外，它还会创建一个触发器，以便将新数据也推送到新表中。然后它会自动重命名表，删除旧表，然后您就可以使用新列开始运行，并且在等待更新时不会出现停机时间。

答案3

軟件提供一些可靠的建议。

回答你的问题，减轻影响的最简单和最佳方法是让多个数据库进行复制。双主服务器具有适当的故障转移程序，可停止活动服务器上的复制，从而允许在不影响活动服务器的情况下对非活动服务器进行更改。

您可以在单个实时数据库上执行此操作，并使用与我在这个答案。诚然，这与 symcbean 描述的类似，但包含技术细节。您也可以使用 auto_increment 字段，而不仅仅是时间戳。

最后，如果你的数据集变得如此之大，你还需要考虑OLTP和联机分析处理数据库。如果设计得当，您的交易数据集不需要那么大。

答案4

我遇到过类似的情况，我必须更改我的 1 个事务表，该表几乎有 65GB。我听到了 2 个解决方案

  1. 使用直接 ALTER 并让其运行（X 小时或天）
  2. 确保数据库中有一个时间戳列，或者它正在生成复制日志
    • 创建表 (A) 的副本 (B)
    • 将新列添加到 B（这仍然会阻止 myisam）
    • 禁用交易
    • 将原始表（A）重命名为其他名称（备份）
    • 将新表 (B) 重命名为原始表 (A) 的名称

相关内容