InnoDB file per tablespace

Question

I am using following syntax to create an InnoDB table:

create table InnoDB_Test(a INT) 
DATA DIRECTORY='/var/log/storage/MySQL' 
INDEX DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;`

I want to create table which can store data over mounted partition(/var/log/storage i.e. /dev/sda6), but it is storing the data in /var/lib/mysql.

Files related to table i.e. .idb and .frm files are created there. It is not creating .isl file and neither the directory which is to be created in the directory mentioned.

But if I create the table with MyISAM engine then it is working fine. All files are created and data is getting stored in mounted partition.

I have enabled innodb_file_per_table, have_symlink global variables.

I am using 5.5.44-MariaDB MariaDB Server, CentOS 7.1

My partitions are as Follows:

/dev/sda2       6.5G  3.6G  2.6G  59% /
/dev/sda1       395M   32M  338M   9% /boot
/dev/sda6       5.3G   22M  5.0G   1% /var/log/storage`

So please tell me how to store data of InnoDB table in mounted partition?

Answer 1

Your problem is very simple

INDEX DIRECTORY is not supported for InnoDB, only MyISAM

create table InnoDB_Test(a INT) 
DATA DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;

is all you need

UPDATE 2016-04-15 07:02 EDT

You just said in your comment

Thanks Ronaldo for your answer. I try with removing INDEX DIRECTORY option but it is not working. Even if i give other directory("/root/MySQL") other than mounted one, it is still not working. It gives a warning with code 1618 " option ignored"

According to the MariaDB Documentation on CREATE TABLE

These options are ignored if the NO_DIR_IN_CREATE SQL_MODE is enabled (useful for replication slaves).

If you set your sql_mode it not have NO_DIR_IN_CREATE, you should be good to go.

Answer 2

What you did applies to tables one at a time. Instead...

Plan A: Change datadir when installing. (See documentation for details and caveats.)

Plan B: Symlink .../mysql/data to some place in the other partition.

(And remove DATA_DIRECTORY, etc from CREATE TABLE.)