Best way to archive live MySQL database

Question :

Best way to archive live MySQL database,

Answer :

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.

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


 has 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.

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.

Read More  Add .NET 2.0 SP1 as a prerequisite for deployment project

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

That’s the answer Best way to archive live MySQL database, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.

Disclaimer :

The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you

Read More  User Control Property Designer Properties