主页 / dba / 问题 / 440
Accepted
Hendrik Brummermann
Asked: 2011-01-10 03:20:03 +0800 CST

如何在不离线几个小时的情况下将 66,862,521 行的表从 MyISAM 转换为 InnoDB？

  • 772

是否有可能（以及如何）将一个巨大的 MyISAM 表转换为 InnoDB 而无需使应用程序脱机。它需要每秒向该表中插入几行，但可以将其暂停大约 2 分钟。

显然 ALTER TABLE ... engine=innodb 将不起作用。因此，我计划使用 innodb 引擎创建一个新表并将内容复制到其中。最后，挂起应用程序日志线程和重命名表。

不幸的是，即使以 100 行的小批量进行复制也会在一段时间后产生明显的延迟。

编辑：现有行永远不会更改，此表用于记录。

mysql innodb

4 个回答

  1. Best Answer

    创建一个 Master-Master 设置，如下所示：

    • 创建第二个master，MasterB
    • MasterB 充当从属logTable
    • 创建logTable_new为 innodb
    • INSERT INTO logTable_new SELECT * FROM logTable在 MasterB 上运行（伪代码），将复制发送到 MasterA
    • 在logTable_newMasterA 上完成同步后，换出表格
    • 15

  2. 鉴于以下约束：

    我不在乎谈话需要几天还是几周。但它必须在后台运行，不需要应用程序停机，也不会产生明显的延迟

    在进行日志记录时，如果您有一些好的方法来设置标记，这样您就可以知道您开始该过程的原因，然后您可以重新应用任何日志，或者将日志写入文本文件，这样你可以稍后摄取它们LOAD DATA INFILE

    部分问题是小批量写入意味着必须一遍又一遍地重新计算索引；您最好一次运行它，但这可能会导致系统出现一些“明显”的延迟......但您不必在生产服务器上执行此操作。

    1. 暂停日志记录或设置一些标记，以便您以后可以重新应用日志。
    2. 将您的 MyISM 表复制到另一个系统
    3. 在另一个系统上，以不同的名称创建一个 InnoDB 表并迁移数据 （转储并使用它甚至可能更快LOAD DATA INFILE）
    4. 将 InnoDB 表复制回原始系统
    5. 为日志记录设置另一个标记。
    6. 将最后两个标记之间的所有日志重新应用到新表。
    7. （如果第 6 步花费的时间超过一分钟左右，请重复第 5 步和第 6 步，直到只有几秒钟）
    8. 交换表（将旧表重命名为 table_BACKUP，新表在旧表名下）
    9. 赶上自上次标记以来的日志。
    • 10

  3. 不幸的是，即使以 100 行的小批量进行复制也会在一段时间后产生明显的延迟。

    您是在每个批次之间添加任何延迟，还是只是将更新分批并在前一个批次之后直接运行每个批次？

    如果是这样，请尝试使用您喜欢的语言编写转换脚本，例如：

    repeat
    copy oldest 100 rows that haven't been copied yet to new table
    sleep for as long as that update took
until there are <100 rows unprocessed
stop logging service
move the last few rows
rename tables
restart logging
delete the old table when you are sure the conversion has worked

    这应该确保转换不会占用服务器容量的一半以上，即使考虑到系统使用随时间变化而施加的负载差异。

    或者，如果您想在服务相对空闲但在数据库需要为其用户做一些工作时退出（可能暂停相当长的时间）时尽可能多地使用时间，请替换sleep for as long as the update took为if the server's load is above <upper measure>, sleep for some seconds then check again, loop around the sleep/check until the load drops below <lower measure>. 这意味着它可以在安静的时候继续前进，但在服务器忙于执行其正常工作负载时会完全暂停。确定负载将取决于您的操作系统 - 在 Linux 下和类似的 1 分钟负载平均值/proc/loadavg或uptime应该做的输出。<lower measure>并且<upper measure>可能是相同的值，尽管在这样的控件中通常会有差异，因此您的进程不会继续启动然后立即暂停，因为它自己的重新启动会对负载测量产生影响。

    当然，这不适用于旧行可能会被修改的表，但对于您描述的日志表来说可以正常工作。

    在这种情况下，您将需要忽略在填充新表后创建索引的通常做法。虽然当您希望事情尽可能快时确实更有效（对系统其余部分的影响被诅咒），但在这种情况下，您不希望在流程结束时出现大量负载，因为索引是一次性完全创建的，因为这是一个在事情繁忙时不能暂停的过程。

    • 9

  4. 像这样的东西会起作用吗？

    1. 暂停日志记录（因此$auto_increment您的日志记录表上的mytable 不会改变）。
    2. 注意$auto_increment使用的值SHOW TABLE STATUS LIKE 'mytable'。
    3. CREATE TABLE mytable_new LIKE mytable
    4. ALTER TABLE mytable_new AUTO_INCREMENT=$auto_increment ENGINE=Innodb
    5. RENAME TABLE mytable TO mytable_old, mytable_new TO mytable
    6. 再次启用日志记录。Innodb 表现在将开始填充。
    7. INSERT INTO mytable SELECT * FROM mytable_old.

    您可以分批或在一个语句中执行第 7 步，因为它不应该阻塞正常的日志记录。

    • 4

相关问题