4

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

4 Answers 4

2

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.

1
  • Thanks, didn't think so as I couldn't find anything, but I hoped it might be something obscure that's not documented. Jan 27, 2011 at 14:06
1

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.

1

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

0

Looks It is possible by workaround.

All database situated under DATADIR directory by folder name respectively. So you can attach new disk and mount it to folder name according database name.

mysql> create database DatabaseName;


# ls /var/lib/mysql/DatabaseName


# mount /dev/sdb /var/lib/mysql/DatabaseName

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .