I have a large table that I want to spread across multiple disks/LUNs. It will contain blobs and I want to have it separated out from the other data.

How do I go from new disks (lets call them sdb1 and sdc1, the OS, etc. is on sdba1) attached to a (virtual) Linux database server to putting the parts of the database, really one table spread across a couple dedicated devices.

Have looked at the Partition information but its unclear how I change the location of the partitions so everything just doesn't end up in the same data directory.

1 Answer 1


There is a CREATE TABLE syntax where you can declare the data folder and index folder under the partition_definition of a partition_option:

        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
    [(partition_definition [, partition_definition] ...)]

    PARTITION partition_name
            {LESS THAN {(expr | value_list) | MAXVALUE} 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir'] <<<<<---------
        [INDEX DIRECTORY [=] 'index_dir'] <<<<<---------
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]


  • I know it works for MyISAM because I wrote about it in September 2011 : Dynamically changeable database. For an already existing MyISAM table, it can done from the OS by moving the .MYD and .MYI to another disk and setting up two symlinks. I wrote about that in May 2012 : Can I have one table be an "alias", or "symlink", to another?. When done correctly, SHOW CREATE TABLE will display DATA DIRECTORY and INDEX DIRECTORY and their respective folders.

  • I personally implemented this in production for a client when I did the following

    • Converted mysql.general_log from CSV to MyISAM
    • Moved /var/lib/mysql/mysql/general_log.MYD to a large 3TB disk
    • Moved /var/lib/mysql/mysql/general_log.MYI to a large 3TB disk
    • Setup /var/lib/mysql/mysql/general_log.MYD as a symlink to the real .MYD
    • Setup /var/lib/mysql/mysql/general_log.MYI as a symlink to the real .MYI
    • enabled the general log
    • Watched the client's general log growth to 1.1TB
    • I wrote up how to set this up back in June 2011 : Mail alert from MySQL when databases are edited (under Option 3 : You Could Monitor the general log)
  • Using hard links, it can be done to MyISAM tables in Windows, although the effort to do so may not be worth it. I wrote about that back in January 2012 : Can I specify a different disk for a specific MySQL table?

  • I do not believe [DATA DIRECTORY [=] 'data_dir'] and [INDEX DIRECTORY [=] 'index_dir'] were implemented for InnoDB. Even if the it were implemented for InnoDB, I would adamently advised against it and I wrote about why you should not back in August 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?

Your Original Question

MySQL 5.5 Only : With regard to your question in the heading, Where are partitions defined in mysql, you can run this query to locate all tables that have partitions:

select count(1) partition_count,table_schema,table_name
from information_schema.partitions
group by table_schema,table_name
having count(1) > 1;

Here is a sample:

mysql> select count(1) partition_count,table_schema,table_name
    -> from information_schema.partitions
    -> group by table_schema,table_name
    -> having count(1) > 1;
| partition_count | table_schema | table_name              |
|              31 | clickit      | t_keyword_conversion_ga |
|               2 | part         | myoldpart               |
|               3 | test         | messages                |
|             256 | test         | ngram_key               |
|               2 | test         | rc1                     |
5 rows in set (1.60 sec)


You can get the complete definition by running SHOW CREATE TABLE db.tbl\G from any table from the result of that query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.