Someone at work pointed out to me this blog post about what to do when disk partitions housing MySQL databases fill up, with the caption, “An idea for [the MySQL 5.1 Partitioning] Docs?”
My reply was, “Nope”.
Well, I was wrong. In a way. This guy’s talking about disk partititions, not partitioned tables. And using symlinks to point to data or database directories is not particularly new or exciting. However, partitioning in MySQL 5.1 provides a kind of cool new way to move table data and index files pretty easily.
The DATA DIRECTORY and INDEX DIRECTORY options have been around for CREATE TABLE for quite some time, but they’re not supported for ALTER TABLE - if you try, this is what happens:
mysql> ALTER TABLE t1
-> DATA DIRECTORY = '/home/jon/data'
-> INDEX DIRECTORY = '/home/jon/data';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW WARNINGS;
+---------+------+------------------------------–+
| Level | Code | Message |
+---------+------+------------------------------–+
| Warning | 0 | DATA DIRECTORY option ignored |
| Warning | 0 | INDEX DIRECTORY option ignored |
+---------+------+------------------------------–+
2 rows in set (0.00 sec)
Well, that’s not very happy, is it? However, in MySQL 5.1, you can do this:
ALTER TABLE t1
PARTITION BY HASH(col) (
PARTITION p0
DATA DIRECTORY = '/path/to/dir'
INDEX DIRECTORY = '/path/to/dir'
);
In this case, MySQL dutifully creates copies of the table’s .MYD and .MYI files in the new location, then replaces the originals with symlinks. Hey, presto! You’ve just moved all the bulky bits of the table. And if the path points to a location on a different disk, then you’ve just freed up a chunk of space on the original drive.
And yes, it is perfectly legal to create a partitioned table with only one partition.
If you don’t want the table to remained partitioned, here’s all you have to do:
ALTER TABLE t1
REMOVE PARTITIONING;
Now the partitioning is gone, but the symlinks remain. And the data and index files remain where you’ve moved them by means of the ALTER TABLE ... PARTITION BY. Too easy!
The fine print: The table schema doesn’t matter, except that if the table has a primary key (or a unique key in the event there’s no primary key), then any column used for partitioning that table must be part of that key.
Comments(1)