Innodb; multiple data directories

Question

I have a requirement to store two Innodb database files on separate partitions. (one database on raid0 for speed, the other on raid1 for stability)

From what I've read, the only way to accomplish this is by using innodb_file_per_table and symlinking .ibd and .frm files wherever-they-may-live to the configured mysql storage directory (/var/lib/mysql on my system), where the ibdata1 file must live (for table metadata).

Is this the only way to accomplish the split?

Is there an innodb analog to myisam's TABLE/INDEX DIRECTORY?

Answer 1

Just to update this post if someone ever come across this, InnoDB now support data directory since version 5.6. No symlink (not recommended), and works for both *nix and Windows.

Requirements:

1. MySQL 5.6 and above
2. innodb_file_per_table is enabled

innodb_file_per_table = 1

---

Scenario 1 (create new tables):

It is as simple as specifying the DATA DIRECTORY options during creation of table.

1. CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/alternative/directory';

---

Scenario 2 (moving existing table to separate disk):

This involves few more steps (MySQL server restart required) but still pretty straight forward. And it doesn't requires the table to have DATA DIRECTORY option specified during table creation.

1. Stop MySQL server
2. Move the innodb table table_name.ibd file to separate disk/volume by simple file copy/file move
3. Create a text file with .isl extension e.g. table_name.isl
4. Edit the .isl file and type in the new path to the table_name.ibd file that you moved. /alternative/directory/table_name.ibd
5. Make sure the original .ibd file in old path was removed
6. Start MySQL server

Now subsequent changes to the moved table will be save into .ibd file in new path.

For reference please refer to MySQL official doc: http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html