asked on

is it possible to have innodb_file_per_table on mutiple filesystem

We are on RH Linux MySQL 5.0.18

With innodb_file_per_table enabled, is there a way to isolate the data files to different filesystem? Right now all the .idb files goes into data directory and we end up having one big filesystem.

I take it symbolic linking doesn't work for innodb?

I can't think of anything better than having mutiple mysqld running to isolate the database and datadirectory. Any better options?

Thanks

Raynard7

Yes you can have it in separate files, in the my.cnf you could do

[mysqld]
innodb_file_per_table

http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

db2inst1

ASKER

I do have it in seperate files, but is there a way to have it in seperate locations not just in the data dir.

For example

all the files from database1 should go to directory /mysql/data/database1

Raynard7

Symlinks are only supported for myisam tables
http://dev.mysql.com/doc/refman/5.0/en/symbolic-links-to-tables.html

Raynard7

the separate locations - not with innodb tables, only myisam - also refer to the above article

db2inst1

ASKER

I am looking for workarounds....? mutiple instances?

Raynard7

not sure - normally if I am running out of room just add another two HDD's to the raid.

what exactly are you wanting to acheive?

db2inst1

ASKER

I didn't really like the idea of having one big filesystem.. Would like to isolate the tables atleast at database level for better I/O. Its a shared env, didn't want one busy db to impact others..

ASKER CERTIFIED SOLUTION

Raynard7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

db2inst1

ASKER

Thanks for all the information. I will leave the question open for few more days, and see if there is any other workarounds. :)