[ACCEPTED]-Best way to archive live MySQL database-database
http://www.maatkit.org/ has mk-archiver
archives or purges rows 18 from a table to another table and/or a file. It 17 is designed to efficiently “nibble” data 16 in very small chunks without interfering 15 with critical online transaction processing 14 (OLTP) queries. It accomplishes this with 13 a non-backtracking query plan that keeps 12 its place in the table from query to query, so 11 each subsequent query does very little work 10 to find more archivable rows.
Another alternative 9 is to simply create a new database table 8 each day. MyIsam does have some advantages 7 for this, since INSERTs to the end of the 6 table don't generally block anyway, and 5 there is a merge table type to being them 4 all back together. A number of websites 3 log the httpd traffic to tables like that.
With 2 Mysql 5.1, there are also partition tables 1 that can do much the same.
I use mysql partition tables and I've achieve 1 wonderful results in all aspects.
Sounds like replication is the best solution 3 for this. After the initial sync the slave 2 gets updates via the Binary Log, thus not affecting 1 the master DB at all.
MK-ARCHIVER is a elegant tool to archive 1 MYSQL data.
MySQL replication would work perfectly for 2 this.
Master -> the live server.
Slave -> a 1 different server on the same network.
Could you keep two mirrored databases around? Write 12 to one, keep the second as an archive. Switch 11 every, say, 24 hours (or however long you 10 deem appropriate). Into the database that 9 was the archive, insert all of todays activity. Then 8 the two databases should match. Use this 7 as the new live db. Take the archived database 6 and do whatever you want to it. You can 5 backup/extract/read all you want now that 4 its not being actively written to.
Its kind 3 of like having mirrored raid where you can 2 take one drive offline for backup, resync 1 it, then take the other drive out for backup.
More Related questions