Spreading a table's partitions across drives

Question

With nearly a billion rows, we have to split up individual partitions across SSDs with about 4 partitions of them in each SSD.

The table's current schema:

CREATE TABLE IF NOT EXISTS `a` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `special_key` varchar(20) NOT NULL DEFAULT '0',
  `data1` varchar(20) NOT NULL,
  `data2` varchar(20) NOT NULL,
  `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`special_key`),
  UNIQUE KEY `special_key` (`special_key`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
PARTITION BY KEY (special_key)
PARTITIONS 12;

If we'd done this while partitioning initially, the command that works looks like this:

ALTER TABLE `a`
partition by key(special_key) PARTITIONS 12  
(
PARTITION p0 ,
PARTITION p1 ,
PARTITION p2 ,
PARTITION p3 DATA DIRECTORY='/media/p1/mysql_table_a',
PARTITION p4 DATA DIRECTORY='/media/p1/mysql_table_a',
PARTITION p5 DATA DIRECTORY='/media/p1/mysql_table_a',
PARTITION p6 DATA DIRECTORY='/media/p1/mysql_table_a',
PARTITION p7 DATA DIRECTORY='/media/p2/mysql_table_a',
PARTITION p8 DATA DIRECTORY='/media/p2/mysql_table_a',
PARTITION p9 DATA DIRECTORY='/media/p2/mysql_table_a',
PARTITION p10 DATA DIRECTORY='/media/p3/mysql_table_a',
PARTITION p11 DATA DIRECTORY='/media/p3/mysql_table_a',
);

But now that we've already partitioned this table, is there a way to get MySQL to 'move' specific partitions without re-creating the whole table?

Copying & Symlinking these tables manually works but it's not the 'right' way and this is reset on any further alters.

Answer 1

Is

ALTER TABLE `a` REORGANIZE PARTITION
     p0 INTO (PARTITION p0 DATA DIRECTORY='/media/p666/mysql_table_a');

what you're looking for?

Docs suggest it should be safe to run on existing partitions, meaning it won't repartition the whole table.

ALTER TABLE with ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, or REORGANIZE PARTITION does not create any temporary tables (except when used with NDB tables); however, these operations can and do create temporary partition files.

...

REORGANIZE operations copy only changed partitions and do not touch unchanged ones.