Re: myisam table partition backup?

[Phil Hildebrand]

that depends...

The optimizer knows which partition you want to grab data from when it's included in the where clause, so if I have a table partitioned on id, and all rows with id >4 exist in a partition then you could just do a select into outfile:

create table dump_data
( id integer auto_increment primary key,
stuff varchar(128)
) engine=myisam partition by range (id)
( partition p1 values less than (2),
partition p2 values less than (3),
partition p3 values less than (4),
partition p4 values less than MAXVALUE
);

insert into dump_data (stuff) values ('one'),('two'),('three'),('four'),('five');

mysql> select * from dump_data ;
+----+-------+
| id | stuff |
+----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
+----+-------+
5 rows in set (0.00 sec)

mysql> explain partitions select * into outfile '/tmp/gorpit' from dump_data where id = 4;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | dump_data | p4 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

If by dump you mean throw away, you could :

mysql> alter table dump_data drop partition p4;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain partitions select * into outfile '/tmp/gorpit' from dump_data where id = 4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)


mysql> select * from dump_data;
+----+-------+
| id | stuff |
+----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
+----+-------+
3 rows in set (0.00 sec)


There are some differences between partition types of how you'd have to dump the data.

I don't think there is a way currently, however, to explicitly 'name' the partition during the dump/select.

IE: you can't run: select * from dump_data partition p3;