MySQL Forums
Forum List  »  Partitioning

Re: myisam table partition backup?
Posted by: Phil Hildebrand
Date: May 02, 2008 11:14AM

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;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: myisam table partition backup?
4025
May 02, 2008 11:14AM


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.