What are ways to move data older than 'Y' days to an archive/history table in MySQL?

Looking to move data from a table A to history table B every X days for data that is Y days old and then remove the data from history table B that is older than Z days.

Just exploring different ways to accomplish this. So any suggestions would be appreciated.

Example for variables X - 7days Y - 60days z - 365days

Thank you

From stackoverflow

• CREATE PROCEDURE prc_clean_tables (Y INT, Z INT)
  BEGIN
          BEGIN TRANSACTION;
  
          DECLARE _now DATETIME;
          SET _now := NOW();
  
          INSERT
          INTO    b
          SELECT  *
          FROM    a
          WHERE   timestamp < _now - INTERVAL Y DAY;
          FOR UPDATE;
  
          DELETE
          FROM    a
          WHERE   timestamp < _now - INTERVAL Y DAY;
  
          DELETE
          FROM    b
          WHERE   timestamp < _now - INTERVAL Z DAY;
  
          COMMIT;
  END
  

  Josh Harris : Is there a way to get this procedure to run every 7 days or you have to CRON a script to run it? Thanks again

  Quassnoi : No, you'll have to use cron.

  yetanotherdave : this is a cool solution. One variation you may want to consider: add new rows to history at the same time they are added to the main table (perhaps via a trigger). This has the effect of a always being a subset of b instead of two disjoint sets, which has its advantages in some contexts. Thus clean up is reduced to two delete statments.

• The simplest way of course would be to insert the rows from the online table to the history table then do your deletes. Wrapping that up in a SPROC as Quassnoi suggests would be perfect.

  Another potentially better way would be to take advantage of partitioning. If you have your tables partitioned by date, you should be able to speed up at least the DELETE parts by simply dropping the relevant partition. The INSERT part may also be faster since all the inserted rows would come from one partition (if you create your partitions right).

  If the schemas are the same, there may even be some kind of trick that would allow you to just move the partitions from one table to the other relatively instantaneously.

  Josh Harris : Ill check into that, thank you

• This seems straight forward.

  You want a nightly cron job to run a script.

  #crontab -e 
  
  50 11 * * * $HOME/scripts/MyWeeklyArchive.sh
  

  The script file itself is pretty simple as well. We'll just use mysqldump and the Now() function;

  #! /bin/bash
  
  /usr/bin/mysqldump -uUser -pPassword Current_DB Table --where='date < NOW() - INTERVAL 7 DAY' | /usr/bin/mysql -uUser -pPassword archive_DB
  

  You could just include that line in the cron file, but for scalability and such I reccomend making it a script file.

  Josh Harris : Thanks, that helps a lot, I am quite new to CRON

  Eddie : @Josh Depending on your configuration you may want to type export editor=vi before the crontab command. it will allow you to use vi which is much easier than the default crontab editor

• If you are using MySQL 5.1 you maybe able to use the event scheduler, instead of cron. I have not used it but I have used something similar in SQL Server.