How to store data in a table to use as a backup

[zorgi]

Hi

Basically I have table TABLE_1 with number of fields being updated all the time. Once a month I run cron job and all the records in TABLE_1 become not needed for my next cron job. Now I need to somehow back up all used records and prepare my TABLE_1 for new updates. Old records are needed mainly for stats purposes. What from the following would be the best practice to do this:

1. Create new table identical to TABLE_1 and copy all the used records into it and than delete all the records from TABLE_1 OR
2. Include an extra field to mark record as used so that next cron job can skip it OR
3. something else

Thank you for your input.

[Atli]

Hey.

Ideally you would want to avoid having to actually delete the data after using it, so I would recommend trying your second method.

If there is a LOT of activity on the primary table, you could also consider things like Replication. By which I mean; you could set up a second server which replicates the active server. Then when it comes to running the Cron, you disconnect the slave from the master, clear the table on the master and run your Cron job and archive scripts on the slave. - This way your master server doesn't have to suffer any performance hits when the Cron is running.

[zorgi]

Thank you Atli for clearing up for me.

Replication sounds good. Must admit solution No1 did sound bit sloppy :)