Mysql – Archive partition before delete it

MySQLpartitioningpercona

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.