归档实时MySQL数据库的最佳方式 [英] Best way to archive live MySQL database

问题描述

我们有一个活动的MySQL数据库，99％的INSERT，大约每秒100。我们希望每天存档数据，以便我们可以在其上运行查询而不影响主，实时数据库。此外，一旦归档完成，我们要清除活动数据库。

We have a live MySQL database that is 99% INSERTs, around 100 per second. We want to archive the data each day so that we can run queries on it without affecting the main, live database. In addition, once the archive is completed, we want to clear the live database.

如果没有（如果可能）锁定INSERT，最好的方法是什么？我们对查询使用INSERT DELAYED。

What is the best way to do this without (if possible) locking INSERTs? We use INSERT DELAYED for the queries.

推荐答案

http://www.maatkit.org/ 有mk-archiver

http://www.maatkit.org/ has mk-archiver

存档或清除行到另一个表和/或文件的表。它设计为在非常小的块中高效地半字节数据，而不干扰关键在线事务处理（OLTP）查询。它通过非回溯查询计划实现这一点，它在查询到查询之间保持在表中的位置，因此每个后续查询只能查找更多可存档的行。

archives or purges rows from a table to another table and/or a file. It is designed to efficiently "nibble" data in very small chunks without interfering with critical online transaction processing (OLTP) queries. It accomplishes this with a non-backtracking query plan that keeps its place in the table from query to query, so each subsequent query does very little work to find more archivable rows.

另一种方法是每天简单地创建一个新的数据库表。 MyIsam确实有一些优势，因为INSERT到表的末尾通常不会阻塞，并有一个合并表类型，以使它们都回到一起。许多网站将httpd流量记录到这样的表。

Another alternative is to simply create a new database table each day. MyIsam does have some advantages for this, since INSERTs to the end of the table don't generally block anyway, and there is a merge table type to being them all back together. A number of websites log the httpd traffic to tables like that.

对于Mysql 5.1，还有分区表可以做同样的事情。

With Mysql 5.1, there are also partition tables that can do much the same.

这篇关于归档实时MySQL数据库的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！