How do I InnoDB multiple tablespaces, on multiple disks?

[Tore]

Hi.

I want to use multiple tablespaces with InnoDB tables.

And I also want to spread my InnoDB tables on two separate disks.

I also want to control which disk each of the InnoDB tables are placed.

Is this possible?

How do I do it?

Example config please

 

[ericbrunson]

You might try the innodb_file_per_table option and symlink the files to were you want them.

 

[Tore]

Can you show me an example on how to use this symlink stuff.

And, when will the symlinks be broken?

I need it to work on both windows and linux.


One place in the manual MySql says that it works as for MyIsam tables except that InnoDB uses one file for each table for both indexes and data.

The 'CREATE TABLE' has a table option
"DATA DIRECTORY = 'absolute path to directory'".

Is this supposed to work for InnoDB with multiple tablespaces?
From their manual it says only for MyISAM tables....
but one could hope ......

 

[ericbrunson]

To create a symbolic link use "ln -s"

I don't do windows, so I can't help there.

 

[Tore]

Could you be more specific:

How does it look like in the mysql config file?

Could you show an example using two tables and placing those two tables on different disks.


It has to work with InnoDB tables!!!

 

[ericbrunson]

Create your innodb table after setting the innodb_file_per_table setting (read the mysql manual if you don't know how).

After the table is created, shut the database down, then move the file to the disk you want it to live on. Then use "ls -s /new/path/to/file /path/where/mysql/expects/to/find/it" to create the symlink. Then restart the database.

Try it first to make sure it works.

 

[Tore]

Thanks for replies.

 

[Tore]

Is there any pitfalls when using symlink and InnoDB tables with "multiple tablespaces"? (ref. previous thread).

What does not work when using symlinks and "multiple tablespaces" with InnoDB?

What will eventually cause the symlink to be broken?