1

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;
1

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.

  • @How will pt-archiver work in comparision to mysqldump. Which is a better option in terms of speed and table locking etc? – biz14 Apr 13 '13 at 14:15
  • @biz14 If performance is an issue, and you are using MySQL 5.6, you can use ALTER TABLE...EXCHANGE partition and the InnoDB transportable tablespaces to manage your partitioned data: dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html – jynus Aug 20 '14 at 11:55
0

I have been trying to find a tool to archive partitions, but couldn't find any.

So I programmed a tool in golang, in order to extract one partition from a partitioned table, import it to another database, and copy the partition files to a third server used as backup, just in case. Hope it helps

https://github.com/netlabs-argentina/partition_archiving

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.