每周使用 PHP 归档 MySQL 数据

Question

我有一个 MySQL 数据库，它每周在一周中的某一天的给定时间从源接收大量数据（大约 120 万行），并将其存储在“实时”表中。

我想将“实时”表中的所有数据复制到存档中，并截断实时表，以为下周将出现的下一个“当前数据”腾出空间。

任何人都可以建议一种有效的方法来做到这一点。我真的想避免 -- insert into archive_table select * from live --。我希望能够使用 PHP 运行此存档器，因此我无法使用 Maatkit。有什么建议吗？

编辑：此外，存档数据需要易于访问。由于每次插入都有时间戳，如果我想查找上个月的数据，我可以在档案中搜索它

Answer 1

偷偷摸摸的方法：

不要复制记录。那需要太长时间了。

相反，只需重命名活动表，然后重新创建：

RENAME TABLE live_table TO archive_table;
CREATE TABLE live_table (...);

它应该非常快速且轻松。

编辑：如果您想要每个循环周期的存档表，我描述的方法最有效。如果您想维护单个存档表，可能需要变得更加棘手。但是，如果您只想对历史数据进行即席查询，则可能可以使用 UNION。

如果您只想保存几个周期的数据，则可以以类似于日志轮转的方式进行几次重命名。然后，您可以定义一个视图，将存档表合并为一个大表。

EDIT2：如果你想维护自动增量的东西，你可能希望尝试一下：

RENAME TABLE live TO archive1; 
CREATE TABLE live (...); 
ALTER TABLE LIVE AUTO_INCREMENT = (SELECT MAX(id) FROM archive1);

但遗憾的是，这行不通。但是，如果您使用 PHP 来驱动该流程，那么就很容易解决这个问题。

Answer 2

编写一个脚本作为 cron 作业运行，以便：

1. 从“实时”表中转储存档数据（从 shell 脚本中使用 mysqldump 可能更有效）
2. 截断实时表
3. 修改转储文件中的 INSERT 语句，以便表name 引用存档表而不是实时表
4. 将存档数据附加到存档表（同样，可以通过 shell 脚本从转储文件导入，例如 mysql dbname < dumpfile.sql）

Answer 3

这取决于您在归档数据后对数据执行的操作，但是您是否考虑过使用 MySQL 复制？

您可以将另一台服务器设置为复制从属服务器，一旦所有数据都被复制，请在其之前使用 SET BIN-LOG 0 进行删除或截断，以避免该语句也被复制。