Home / server / Questions / 227597
Accepted
Andrei Serdeliuc
Asked: 2011-01-28 05:47:00 +0800 CST

Store each database in a different directory

  • 772

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

mysql

4 Answers

  1. Best Answer

    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.

    • 2

  2. 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

  3. 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

    • 1

  4. 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
    • 0