Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Is there any way to store each mysql database in a separate directory? I could symlink the directories into /var/lib/mysql for each database, but I was wondering if there is a built-in way of doing it.

Thanks

share|improve this question

3 Answers 3

up vote 2 down vote accepted

No built-in way of doing that I'm afraid, the way mysql works is just defining the datadir and then all databases are supposed to be there in their directories.

If you want to do that just make sure that the user that mysql is running in has access to the final directories, soft links should be fine I think.

share|improve this answer
    
Thanks, didn't think so as I couldn't find anything, but I hoped it might be something obscure that's not documented. –  Andrei Serdeliuc Jan 27 '11 at 14:06

Surprisingly, the only built-in symlinking of individual MyISAM tables
is done by the CREATE TABLE command as follows:

CREATE TABLE
(
...
) ENGINE=MyISAM
DATA_DIRECTORY='absolute path of data folder'
INDEX_DIRECTORY='absolute path of index folder';

Goto http://dev.mysql.com/doc/refman/5.0/en/create-table.html and locate the DATA_DIRECTORY and INDEX_DIRECTORY options.

Only the .frm file stays in the original server.

BTW These options do not work in Windows version of MySQL. These options are not available for InnoDB.

share|improve this answer

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

With multiple tablespaces enabled, InnoDB stores each newly created table in its own tbl_name.ibd file in the appropriate database directory

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.