Mysql Dump and partitions

Recently I was looking at archiving tools.  We finally got an agreement to keep a smaller subset of data available as long as could restore more if required.


The main tools we use are MEB and mysqldump.

So I created a partition table and populated it:
CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (2010),
    PARTITION p4 VALUES LESS THAN (2020),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);
insert into members values ('Joe','Bloggs','1981/10/10');
insert into members values ('Julia','Roberts','1981/06/06');
insert into members values ('John','Doe','1991/10/10');
insert into members values ('Bruce','Willis','1991/10/02');
insert into members values ('William', 'Shatner','2001/03/09');
insert into members values ('Samuel L', 'Jackson','2001/01/01');
insert into members values ('Meryl', 'Streep','2011/07/07');
insert into members values ('Robert', 'DeNiro','2005/08/08');
insert into members values ('Al', 'Pacino','1995/09/09');
insert into members values ('Angela', 'Basset','1985/06/10');
insert into members values ('Johnny', 'Depp','2002/01/01');
insert into members values ('James', 'Gandolfini','2006/02/02');
commit;


It's not straight forward but fairly simple:





mysqldump -u root --no-create-info --where=" YEAR(joined) < 2010" [database] [table] > [filename].sql
If you don't include --no-create-info it includes a drop table statement in the dump file which could be an issue when restoring.
Don't forget '--single-transaction' for larger partition, this may help locking the entire table.



Test the restore
alter table members truncate partition p3;

Restore a partition from mysqldump
mysql -u root [table name]  < [filename].sql

As much as archiving is useful an other consideration is if there is a table change and restoring a partition i.e if a partition was archived and then the table was change e.g. a column was added.

3 comments: