Rotate MySQL table data within the data store selectively, using a date range on a column

table-rotation.sql

-- If your table is really large, and it very well may be if you've the need to rotate it, you'll want to make sure the column that holds the timestamp/date is indexed.

-- WARNING: This script incurs a write lock for the duration of the trigger recreation and table rename. If your application can't handle this, you might lose data!

-- Create our new skeleton table
CREATE TABLE x_copy LIKE x;

-- Replace the interval with the timespan of data you'd like to keep, or
-- replace this statement with one that stores an id in the local variable of which all higher will be retained
SELECT MIN(id) INTO @end_id FROM x WHERE created_at >= DATE(NOW()) - INTERVAL 3 WEEK;

-- Store current time so we can copy writes that occur after the insert begins into the new table
SELECT NOW() INTO @time_now;

-- Begin copy of data we want to retain
INSERT INTO x_copy
  SELECT * FROM x WHERE id >= @end_id;

-- Restore records we may have missed while the copy took place
INSERT INTO x
  SELECT `old`.* FROM x AS `old`
  LEFT JOIN x_copy AS `new` ON `old`.id = `new`.id
  WHERE 
    `old`.created_at >= @time_now
    AND `new`.id IS NULL;

-- Lock tables so that trigger processing does not lapse during the following operations
--- WARNING! Potential write failure will occur during this time!
LOCK TABLE
  x WRITE,
  x_copy WRITE;

-- Drop old table's triggers and recreate on that which will take its place
DROP TRIGGER tg_x_type;
--- NOTE! You'll need to specify the table wihch the new trigger will be created on as as x_copy
SOURCE tg_x_type.sql;

-- Let skeleton table take the old's place
ALTER TABLE x RENAME archive_x;
ALTER TABLE x_copy RENAME x;

UNLOCK TABLES;