每周使用PHP归档MySQL数据

Question

我有一个MySQL数据库，每周在给定时间的某一天从数据源接收大量数据(大约120万行)，并将其存储在，我们称之为"活动"表中。

我想把"活动"表中的所有数据复制到存档中，并截断活动表，以便为下一个"当前数据"腾出空间，这些数据将在下一周出现。

谁能提出一个有效的方法来做这件事?我真的试图避免——insert into archive_table select * from live——。我想使用PHP运行这个归档器的能力，所以我不能使用Maatkit。有什么建议吗?

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

Answer 1

偷偷摸摸的方式:

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

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

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

应该很快而且无痛。

EDIT:如果您希望每个旋转周期都有一个存档表，那么我描述的方法最有效。如果要维护单个归档表，可能需要更复杂一些。但是，如果您只是想对历史数据进行临时查询，那么您可能只需要使用UNION。

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

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语句，使表名引用归档表而不是活动表
4. 将归档数据附加到归档表中(同样，可以通过shell脚本从转储文件中导入，例如mysql dbname <dumpfile.sql)>

Answer 3

这取决于你存档后对数据做了什么，但是你考虑过使用MySQL复制吗?

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