mysql 多个磁盘_跨多个磁盘传播mysql数据

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"

);