how to drop partition without dropping data in MySQL?
ALTER TABLE tbl REMOVE PARTITIONING;
You can reorganize the partition p0
using the ALTER TABLE .. REORGANIZE PARTITION
command.
http://dev.mysql.com/doc/refman/5.5/en/partitioning-management-range-list.html
If you intend to change the partitioning of a table without losing data, use ALTER TABLE ... REORGANIZE PARTITION
ALTER TABLE registrations REORGANIZE PARTITION p0 INTO ( PARTITION p0 VALUES LESS THAN (10000), PARTITION p0 VALUES LESS THAN (20000));
Note that this will not make sense until you actually create several partitions e.g.
ALTER TABLE registrations REORGANIZE PARTITION p0 INTO ( PARTITION p0 VALUES LESS THAN (10000), PARTITION p1 VALUES LESS THAN (20000), PARTITION p2 VALUES LESS THAN MAXVALUE);
Have a look at RANGE partitioning in MySQL
If your partition p2
is becoming too large you can split it the same way.
If you want to rearrange the data while keeping the partitions,
you can take a look at REORGANIZE PARTITION and COALESCE PARTITION clauses of ALTER TABLE
command.
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html