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

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

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
mysqld_multi seems to be a good solution, but I am a bit afraid of the memory usage. Will it start a fully standalone database, with separate caches and memory usage? –  asdmin Apr 14 '10 at 6:58
    
As far as i aware, it runs separately in process list under separate user accounts . I'm not sure with cache, but it use separately in memory usages. –  user40424 Apr 15 '10 at 3:20

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
share|improve this answer

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.

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.