MySQL Forums
Forum List  »  Partitioning

Re: How to make a backup from a partition
Posted by: Phil Hildebrand
Date: May 07, 2008 04:39PM

Yea -

For a list partition you would have to use a different order because there's not a 'catchall' partition if you drop the one that you backed up... (it would work with a range partition though)

For list it would work something like:

mysql> create table test_backup
( id int not null primary key
) partition by list (id)
( partition p1 values in (1,2,3),
partition p2 values in (4,5,6),
partition p3 values in (7,8,9)
);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_backup values (1),(2),(3),(4),(5),(6),(7),(8),(9);Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name = 'test_backup';
+-------------+----------------+------------+
| table_name | partition_name | table_rows |
+-------------+----------------+------------+
| test_backup | p1 | 3 |
| test_backup | p2 | 3 |
| test_backup | p3 | 3 |
+-------------+----------------+------------+
3 rows in set (0.00 sec)

mysql> quit

$ mysqldump -u root --no-create-info --where="id > 6" test test_backup > test_backup.dmp

mysql> alter table test_backup drop partition p3;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name = 'test_backup';
+-------------+----------------+------------+
| table_name | partition_name | table_rows |
+-------------+----------------+------------+
| test_backup | p1 | 3 |
| test_backup | p2 | 3 |
+-------------+----------------+------------+
2 rows in set (0.01 sec)

mysql> alter table test_backup add partition (partition p3 values in (7,8,9));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name = 'test_backup';
+-------------+----------------+------------+
| table_name | partition_name | table_rows |
+-------------+----------------+------------+
| test_backup | p1 | 3 |
| test_backup | p2 | 3 |
| test_backup | p3 | 0 |
+-------------+----------------+------------+
3 rows in set (0.00 sec)

mysql> quit

$ mysql -u root test < test_backup.dmp

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name = 'test_backup';
+-------------+----------------+------------+
| table_name | partition_name | table_rows |
+-------------+----------------+------------+
| test_backup | p1 | 3 |
| test_backup | p2 | 3 |
| test_backup | p3 | 3 |
+-------------+----------------+------------+
3 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to make a backup from a partition
3582
May 07, 2008 04:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.