Where are partitions defined in mysql

Question

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.

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:

partition_options:
 PARTITION BY
 { [LINEAR] HASH(expr)
 | [LINEAR] KEY(column_list)
 | RANGE{(expr) | COLUMNS(column_list)}
 | LIST{(expr) | COLUMNS(column_list)} }
 [PARTITIONS num]
 [SUBPARTITION BY
 { [LINEAR] HASH(expr)
 | [LINEAR] KEY(column_list) }
 [SUBPARTITIONS num]
 ]
 [(partition_definition [, partition_definition] ...)]
partition_definition:
 PARTITION partition_name
 [VALUES 
 {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] ...)]

CAVEATS

• 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)
mysql>

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