mysql - 处理从添加时间戳列到 mysql 表的尴尬

Question

我有一个非常大的表，我想在其中添加一个时间戳列。这是一个获得比插入更多更新的表。我正试图找出一种方法来做到这一点，而不会在很长一段时间内将表从生产中取出，这让我陷入了困境。我能做到:

alter table stuff add column mod_time timestamp;

好吧，我可以这样做，然后 table 会被锁定 3-5 个小时。对用户来说可不是什么开心的时光。

例如，为了添加一个 varchar 列，我可以创建一个新表，添加该列，复制数据，然后用新表替换旧表。在缓慢的时间内，最后一次切换可以在一两秒内完成。没有问题。当然，我可能需要分阶段进行复制。我可以做一个副本，然后对第一个副本开始后发生变化的所有内容做一个副本。我可以重复这个直到没有任何改变并进行交换。当然，如果我有一个“mod_time”列，就更容易确定发生了什么变化。

我真正愿意做的是:(1) 创建新表，(2) 复制数据，(3) 添加时间戳列，(4) 交换表。

但是第 (3) 步需要花费数小时。

我希望在添加此列之前更新行，以便将 mod_time 值设置为 NULL。

如果我在上面的步骤中切换 (2) 和 (3)，交换是可行的，但是当我执行此操作以添加列时，我得到一个 mod_time =。我想要 NULL。

我可以尝试在进行交换之前将值设置为 NULL，但是当然，如​​果您更新行以将其设置为 NULL，那么您正在更新行并且 mod_time 列被设置为当前时间。 :-)

我希望我可以这样做:(1) 创建新表，(2) 添加“mod_time”作为日期时间列 (2) 复制数据，(3) 将 mod_time 更改为时间戳列，(4) 交换表格。

理论上 (3) 可以非常非常快地完成(假设 datetime 和 timestamp 的存储是兼容的)因为我在将列更改为 tmestamp 时真正做的是改变它 future 的行为，而不是它当前的存储。所以这根本不需要时间，是吗？可能不是。

澄清一下，如果我这样做:

 alter table stuff add column mod_time timestamp;

我得到:

 +----------+--------+----------+---------------------+
 |     col1 |   col2 |     col3 | mod_time            |
 +----------+----- --+----------+---------------------+
 |     5001 |     50 |     2463 | 0000-00-00 00:00:00 |
 |     5002 |     50 |     2467 | 0000-00-00 00:00:00 |
 |     5003 |     50 |     2459 | 0000-00-00 00:00:00 |

这就是我想要的。只是 alter 语句会花费太长时间。我正在尝试的其他一切都给我:

 +----------+--------+----------+---------------------+
 |     col1 |   col2 |     col3 | mod_time            |
 +----------+----- --+----------+---------------------+
 |     5001 |     50 |     2463 | 2013-12-05 18:11:21 |
 |     5002 |     50 |     2467 | 2013-12-05 18:11:21 |
 |     5003 |     50 |     2459 | 2013-12-05 18:11:21 |

Answer 1

你应该使用 pt-online-schema-change ，一种用于自动执行 MySQL 的 ALTER TABLE 操作的工具，没有锁定表。它是 Percona Toolkit 的一部分，Percona Toolkit 是 MySQL 开发人员和 DBA 不可或缺的免费帮助工具集合。

$ pt-onlines-chema-change h=localhost,D=mydatabase,t=mytable \
  --alter="add column mod_time timestamp"

你可以在pt-online-schema-change工作的同时继续读写原来的表。完成后，它会自动交换表的名称，并删除原来的名称。

您可以查看其设计者关于该工具的网络研讨会的录像。免费观看，但需要注册:http://www.percona.com/webinars/2012-05-02-zero-downtime-schema-changes-in-mysql