Archive MySQL data using PHP every week

I have a MySQL DB that receives a lot of data from a source once every week on a certain day of the week at a given time (about 1.2million rows) and stores it in, lets call it, the "live" table.

I want to copy all the data from "live" table into an archive and truncate the live table to make space for the next "current data" that will come in the following week.

Can anyo开发者_如何学运维ne suggest an efficient way of doing this. I am really trying to avoid -- insert into archive_table select * from live --. I would like the ability to run this archiver using PHP so I cant use Maatkit. Any suggestions?

EDIT: Also, the archived data needs to be readily accessible. Since every insert is timestamped, if I want to look for the data from last month, I can just search for it in the archives

---

The sneaky way:

Don't copy records over. That takes too long.

Instead, just rename the live table out of the way, and recreate:

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

It should be quite fast and painless.

EDIT: The method I described works best if you want an archive table per-rotation period. If you want to maintain a single archive table, might need to get trickier. However, if you're just wanting to do ad-hoc queries on historical data, you can probably just use UNION.

If you only wanted to save a few periods worth of data, you could do the rename thing a few times, in a manner similar to log rotation. You could then define a view that UNIONs the archive tables into one big honkin' table.

EDIT2: If you want to maintain auto-increment stuff, you might hope to try:

RENAME TABLE live TO archive1; 
CREATE TABLE live (...); 
ALTER TABLE LIVE AUTO_INCREMENT = (SELECT MAX(id) FROM archive1);

but sadly, that won't work. However, if you're driving the process with PHP, that's pretty easy to work around.

---

Write a script to run as a cron job to:

1. Dump the archive data from the "live" table (this is probably more efficient using mysqldump from a shell script)
2. Truncate the live table
3. Modify the INSERT statements in the dump file so that the table name references the archive table instead of the live table
4. Append the archive data to the archive table (again, could just import from dump file via shell script, e.g. mysql dbname < dumpfile.sql)

---

This would depend on what you're doing with the data once you've archived it, but have you considered using MySQL replication?

You could set up another server as a replication slave, and once all the data gets replicated, do your delete or truncate with a SET BIN-LOG 0 before it to avoid that statement also being replicated.