Innodb; multiple data directories

12,070

Solution 1

I just wrote a blog post on this today: http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/

You don't want to do the symlinking thing - and InnoDB does not support DATA DIRECTORY/INDEX DIRECTORY.

You actually need stability of the data system wide for InnoDB. Let's say -

  • You have critical tables A,B,C.
  • Non-critical tables are D,E,F.
  • If you relocated D,E,F to /mnt/RAID0, and you lost this volume, InnoDB will actually not allow you to access tables A,B,C any more either.
  • Unless it can access all your data, InnoDB will refuse to start.

If you have two very different data requirements, you need two installs of MySQL ;)

Solution 2

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

Share:
12,070
mikewaters
Author by

mikewaters

Updated on June 04, 2022

Comments

  • mikewaters
    mikewaters over 1 year

    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?