You can use ALTER TABLE with the DATA DIRECTORY and INDEX DIRECTORY options to accomplish this. For example, consider a table defined like this:
CREATE TABLE p.part1 (
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c2 INT NOT NULL
) ENGINE=MyISAM
PARTITION BY HASH (c1)
PARTITIONS 2;
By default, the two partitions are named
p0 and
p1, and the corresponding partition files will appear in the
p subdirectory of the MySQL data directory. Since my server is installed in
~/bin/mysql-5.1, this means they'll be placed in
~/bin/mysql-5.1/var/p:
jon@flundra:~/bin/mysql-5.1/var/p> ls part1* -l
-rw-rw---- 1 jon users 8582 2008-03-25 11:27 part1.frm
-rw-rw---- 1 jon users 28 2008-03-25 11:27 part1.par
lrwxrwxrwx 1 jon users 4500072 2008-03-25 11:27 part1#P#p0.MYD
lrwxrwxrwx 1 jon users 5788672 2008-03-25 11:27 part1#P#p0.MYI
-rw-rw---- 1 jon users 4500072 2008-03-25 11:27 part1#P#p1.MYD
-rw-rw---- 1 jon users 5788672 2008-03-25 11:27 part1#P#p1.MYI
I can then move the data and index files for partition
p0 to
/home/jon/data like this:
mysql> ALTER TABLE p.part1 PARTITION BY HASH (c1) PARTITIONS 2 (
> PARTITION p0 INDEX DIRECTORY = '/home/jon/data' DATA DIRECTORY = '/home/jon/data',
> PARTITION p1);
Query OK, 1000015 rows affected (34.44 sec)
Records: 1000015 Duplicates: 0 Warnings: 0
Note that I had to specify
every partition belonging to the table in the previous
ALTER TABLE statement even though I made changes in only one of them.
And now the files for partition
p0 have been moved to the new directory, with links to the new locations in the original directory:
jon@flundra:~/bin/mysql-5.1/var/p> ls part* -l
-rw-rw---- 1 jon users 8582 2008-03-25 11:29 part1.frm
-rw-rw---- 1 jon users 28 2008-03-25 11:29 part1.par
lrwxrwxrwx 1 jon users 29 2008-03-25 11:29 part1#P#p0.MYD -> /home/jon/data/part1#P#p0.MYD
lrwxrwxrwx 1 jon users 29 2008-03-25 11:29 part1#P#p0.MYI -> /home/jon/data/part1#P#p0.MYI
-rw-rw---- 1 jon users 4500072 2008-03-25 11:29 part1#P#p1.MYD
-rw-rw---- 1 jon users 5788672 2008-03-25 11:29 part1#P#p1.MYI
jon@flundra:~/bin/mysql-5.1/var/p> ls ~/data/part1* -l
-rw-rw---- 1 jon users 4500063 2008-03-25 11:29 /home/jon/data/part1#P#p0.MYD
-rw-rw---- 1 jon users 5786624 2008-03-25 11:29 /home/jon/data/part1#P#p0.MYI
(Note that you do
not have to place the data and index files in the same directory.)
For more information about changing partitioning schemes, see
http://dev.mysql.com/doc/refman/5.1/en/partitioning-management.html and
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html in the MySQL 5.1 Manual.
(This was done on a laptop having a 1.2 GHz Celeron CPU - you'll see much better performance for such operations on real server hardware. ;-) )
Jon Stephens
MySQL Documentation Team @ Oracle
MySQL Dev Zone
MySQL Server Documentation
Oracle
Edited 1 time(s). Last edit at 03/25/2008 04:51AM by Jon Stephens.