Home / server / Questions / 131890
Accepted
asdmin
Asked: 2010-04-14 01:29:58 +0800 CST

mysql single database relocation

  • 772

I would like to know if it's possible to operate different databases on different filesystem locations.

Background: we are a hosting service, which hosts mysql, web, and smtp to it's customer, but all our services (sql, smtp, http) are located in a different place. We are going to assign a single logical volume to a customer, which will accommodate the customer's mailing, weppages and (hopefully) sql database. Web pages and mailing are already covered, but I am not able to find a configuration setting which would enable me to specify the location of a database (the directory where mysql stores the DB).

Let me please highlight, the target here is to relocate different databases to different locations in the filesystem, not moving them from a single place to an another (single) place.

Also please do not bother answering with soft and hard symbolic links. ;)

Thanks

mysql

3 Answers

  1. Best Answer

    You would like to place difference databases' data files under different directories and load them together in one MySQL instance, am i correct to say that ?

    There is a directive called 'datadir' under [mysqld] block which normally used to determine where to store for database files.

    As far as I aware, mysql cannot have multilple 'datadir' for one instance. You can have multiple instance using mysqld_multi and use each instance to point to different datadir. But, of cause, you'll have to use different ports for each instance.

    • 1

  2. In most cases, you will have a difficult time doing this without dedicating a separate instance to it. If you dedicate a separate instance to it, you can use the datadir directive with a different cnf as identified by divinity.

    My first thought would be to encourage or insist that the customer uses MyISAM. If using MyISAM, you could symlink the database to the new filesystem. In contrast, if InnoDB is used, the storage mechanism is shared between different databases and they cannot be logically separated. Be aware, that the customer could still create InnoDB tables unless you prevent it by running MySQL with skip-innodb. In a shared environment, that might not be realistic. Be aware that different engines also have different performance as well.

    For example:

    • datadir is /usr/local/mysql/data
    • New filesystem is /home/user1
    • Database is database1

    Solution:

    • Symlink /usr/local/mysql/data/database1 to /home/user1/database1
    • 1

  3. Short answer is, you can't do what you want with MySQL.

    Long answer is, typically, hosting providers either run MySQL on localhost on the machine doing the web serving. This eliminates network bandwidth for moving mysql data from one machine to another. It doesn't allow clustering without an additional machine.

    Or, a hosting provider will have a pool of MySQL servers, usually in a primary/secondary using replication/heartbeat or, primary/primary/primary using MySQL Cluster. After a particular MySQL 'cluster' is filled up, new databases get created on a new cluster. To maintain some sense of usability, clients are assigned mysql.domain.com when they create a database so that they don't have to remember that they are on mysql401.hostname.com. This allows you to also run MySQL4 and MySQL5 clusters if you really wanted to.

    • 1