Mysql数据库分区，单个硬盘存储

scbai

Mysql数据 创建一个hash分区表，但设备只有一个硬盘
如果想每个分区表存储到不同文件中，该如何写建表语句呢。

如果按下述语句创建，数据文件都会在一个文件下，也就是/var/lig/mysql/ibdata1

CREATE TABLE `tbl_TEST` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `MD5` varchar(32) NOT NULL ,
   PRIMARY KEY  (`id`, `MD5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(md5) PARTITIONS 2;

怎么写sql语句，能够分开存储到不同的数据文件中呢

starzhestarzhe

mysql分区听说不大稳定

scbai

采用innodb引擎，为共享表空间，在加上分区表如何搞。

ruochen

? create table
datadir/indexdir

strong_sjhi

CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0
DATA DIRECTORY = '/disk0/data'
INDEX DIRECTORY = '/disk0/idx',
SUBPARTITION s1
DATA DIRECTORY = '/disk1/data'
INDEX DIRECTORY = '/disk1/idx'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2
DATA DIRECTORY = '/disk2/data'
INDEX DIRECTORY = '/disk2/idx',
SUBPARTITION s3
DATA DIRECTORY = '/disk3/data'
INDEX DIRECTORY = '/disk3/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s5
DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'
)
);

dgvri

推荐mysql使用RAID0+1，分区存放不安全。