I have a large mysql database and two small disks on centos, how do I make the it utilize both disks?
解决方案
You can partition a table over multiple drives. Have a look at the official manual, which covers this subject in depth.
Here's an example to partition an existing table over multiple drives:
ALTER TABLE mytable
PARTITION BY RANGE (mycolumn)(
PARTITION p01 VALUES Less Than (10000)
DATA DIRECTORY = "/mnt/disk1"
INDEX DIRECTORY = "/mnt/disk1",
PARTITION p02 VALUES Less Than (20000)
DATA DIRECTORY = "/mnt/disk2"
INDEX DIRECTORY = "/mnt/disk2",
PARTITION p03 VALUES Less Than MAXVALUE
DATA DIRECTORY = "/mnt/disk3"
INDEX DIRECTORY = "/mnt/disk3"
);
Mind that this needs NO_DIR_IN_CREATE to be off. It doesn't seem to work in windows, and it doesn't seem to work with InnoDB.
If you run out of diskspace on your last partition, you can split it with following statement:
ALTER TABLE mytable REORGANIZE PARTITION p03 INTO
(
PARTITION p03 VALUES Less Than (30000)
DATA DIRECTORY = "/mnt/disk3"
INDEX DIRECTORY = "/mnt/disk3",
PARTITION p04 VALUES Less Than MAXVALUE
DATA DIRECTORY = "/mnt/disk4"
INDEX DIRECTORY = "/mnt/disk4"
);