存档实时 MySQL 数据库的最佳方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/65093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to archive live MySQL database
提问by DavidM
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.
我们有一个实时的 MySQL 数据库，它有 99% 的 INSERT，大约每秒 100 个。我们希望每天存档数据，以便我们可以在不影响主实时数据库的情况下对其运行查询。此外，一旦存档完成，我们要清除实时数据库。
What is the best way to do this without (if possible) locking INSERTs? We use INSERT DELAYED for the queries.
在不（如果可能）锁定 INSERT 的情况下执行此操作的最佳方法是什么？我们使用 INSERT DELAYED 进行查询。
采纳答案by Alister Bulman
http://www.maatkit.org/has mk-archiver
http://www.maatkit.org/有 mk-archiver
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.
将表中的行存档或清除到另一个表和/或文件。它旨在以非常小的块有效地“蚕食”数据，而不会干扰关键的在线事务处理 (OLTP) 查询。它通过一个非回溯查询计划来实现这一点，该计划在每次查询之间保持其在表中的位置，因此每个后续查询几乎不需要做任何工作来查找更多可归档的行。
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.
另一种选择是每天简单地创建一个新的数据库表。MyIsam 在这方面确实有一些优势，因为表末尾的 INSERT 通常不会阻塞，并且有一种合并表类型可以将它们全部重新组合在一起。许多网站将 httpd 流量记录到这样的表中。
With Mysql 5.1, there are also partition tables that can do much the same.
在 Mysql 5.1 中，也有分区表可以做很多相同的事情。
回答by Jacob
Sounds like replication is the best solution for this. After the initial sync the slave gets updates via the Binary Log, thus not affecting the master DB at all.
听起来复制是最好的解决方案。初始同步后，从站通过二进制日志获取更新，因此根本不会影响主数据库。
回答by Eduardo Xavier
I use mysql partition tables and I've achieve wonderful results in all aspects.
我用的是mysql分区表，各方面都取得了不错的成绩。
回答by Eduardo Xavier
MK-ARCHIVER is a elegant tool to archive MYSQL data.
MK-ARCHIVER 是一个优雅的工具来归档 MYSQL 数据。
回答by Seun Osewa
MySQL replication would work perfectly for this.
Master -> the live server.
Slave -> a different server on the same network.
MySQL 复制可以完美地解决这个问题。
主 -> 实时服务器。
从 -> 同一网络上的不同服务器。
回答by Doug T.
Could you keep two mirrored databases around? Write to one, keep the second as an archive. Switch every, say, 24 hours (or however long you deem appropriate). Into the database that was the archive, insert all of todays activity. Then the two databases should match. Use this as the new live db. Take the archived database and do whatever you want to it. You can backup/extract/read all you want now that its not being actively written to.
你能保留两个镜像数据库吗？写给一个，将第二个保存为存档。每隔 24 小时（或您认为合适的时间）切换一次。在作为存档的数据库中，插入今天的所有活动。然后这两个数据库应该匹配。将其用作新的实时数据库。获取存档的数据库并对其进行任何操作。您可以备份/提取/读取所有您想要的内容，因为它没有被主动写入。
Its kind of like having mirrored raid where you can take one drive offline for backup, resync it, then take the other drive out for backup.
这有点像镜像raid，您可以将一个驱动器脱机进行备份，重新同步它，然后将另一个驱动器取出进行备份。
