Re: How to make a backup from a partition

[Phil Hildebrand]

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)