I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use percona tools or mysqldump itself?
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
CONCAT(' p'
, DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN
SET @stmt := CONCAT(
'ALTER TABLE testPart1 DROP PARTITION '
, ' p'
, DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' )
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
CONCAT(' p'
, DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN
SET @stmt := CONCAT(
'ALTER TABLE testPart1 ADD PARTITION ('
, 'PARTITION p'
, DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )
, ' VALUES LESS THAN ('
, TO_DAYS( CURDATE() ) + 2
, '))'
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
Best Answer
If you're going to use Percona tools, you could just use pt-archiver, which will do this part for you as well.
But mysqldump will work just as well here, since you've customized this archiving for your schema.