hwo to handle periodic archiving/truncating mysql data

[RedHat123]

lets say i want to run an online store and every few months i want to archive the old orders by exporting to a file for saving.

do applications such as this usually truncate certain tables periodically in order to improve performance ? i would think so.

what is a good approach for this ?

will any of the new tools like mysql administrator help automate any of this ?

is it as simple as doing a backup first, then deleting the old records ?


thanks

[chrism01]

Conceptually, yes :)

You might want to do it in 2 steps:
Create history tables eg <yourtable>_hist for each table as copies of the orig eg

create table sometab_hist
as
select * from sometab
where 1=2;

since 1 !=2, you should only get the table structure, not the data.

Then write a purge script to be run periodically eg weekly, to copy recs to the _hist equiv table.
Next week use mysqldump to dump the _hist tables, truncate them, the run purge the script.
This way, you have old recs online for a week in case you need to check them; of course you can make the period anything you want in cron eg week, mth, yr etc.

another option is to rsync to another box and gzip that.