Wednesday, April 7, 2010

[Level 2] How to backup MySQL partitioning table.

Sometimes, we will use partitioning to split the data into different files.
But how do we backup the data by the command mysqldump?
The same as the following:

Create table and insert data:
mysql> create table test.tr1 (
    ->   id int
    -> ) engine = InnoDB
    ->   partition by range (id)
    ->   (
    ->     partition p0 values less than (10),
    ->     partition p1 values less than (20),
    ->     partition pm values less than (maxvalue)
    ->   );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into test.tr1 values (1),(2),(11),(12),(21),(22);
Query OK, 6 rows affected (0.30 sec)
Records: 6  Duplicates: 0  Warnings: 0


Dump data and list files:
# mysqldump --user=root --password=password --no-create-info --where="id<10" test tr1 > ./test.tr1.p0.sql;
# mysqldump --user=root --password=password --no-create-info --where="id>=10 and id<20" test tr1 > ./test.tr1.p1.sql;
# mysqldump --user=root --password=password --no-create-info --where="id>=20" test tr1 > ./test.tr1.p2.sql;
# ls -al ./test.tr1.p*.sql
-rw-r--r-- 1 root root 1476 2010-04-07 09:02 ./test.tr1.p0.sql
-rw-r--r-- 1
root root 1489 2010-04-07 09:02 ./test.tr1.p1.sql
-rw-r--r-- 1
root root 1479 2010-04-07 09:02 ./test.tr1.p2.sql




Wish this helps.

regards,
Stanley Huang