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.

In my app, I use MySQL for the database backend (the app uses multiple MySQL databases, all use MyISAM tables). For performance reasons, I want to move one of the databases to a solid state drive (DRAM based), but only that database, the other databases should stay on the SATA drive I use. As you know, in MySQL you can only specify the data directory in MySQL settings and you don't have control over where every database is saved. Is there any workaround for this? I'm hoping that there might some trick I'm not aware of that can get the job done.

Any suggestions would be really appreciated, thanks

share|improve this question

3 Answers 3

up vote 3 down vote accepted

Assuming your SSD has the drive letter G:\ you move your specific database subdirectory to the new drive and create a symlink in the 'datadir' directory.

mklink /D database_subdir G:\path\to\database

Best wishes, Fabian

share|improve this answer
    
Any performance hit with a symlink? –  gap Oct 19 '11 at 18:36

Assuming you are running on a Linux server and are set up for seperate files for each database, you could try moving the database subdirectory and symlinking the database file subdirectory.

share|improve this answer
    
Thanks for you answer. Actually I have MySQL running on a Windows machine (so far at least). I see MySQL create a separate directory for each database but all under the data directory of MySQL. Are things different on Linux? –  Mee Dec 2 '09 at 15:17
    
We have 'innodb_file_per_table' set in the MySQL configuration which saves the data in seperate files under the data directory in a subdirectory with the database name. With Linux (or Unix) you can create a symbolic link to a file or directory that acts like an alias to the actual directory or file. –  Craig Dec 2 '09 at 15:34
1  
you can create symlinks in windows server 2008 and vista. Use the mklink command. –  Jure1873 Dec 2 '09 at 15:53
    
symlinking with innodb_file_per_table is fraught with gotchas, particularly any alter-table statement will break it. –  cagenut Dec 2 '09 at 18:47

Suppose that the MySQL data directory is C:\mysql\data and you want to have database foo located at D:\data\foo. Set up a symlink using this procedure:

  1. Make sure that the D:\data\foo directory exists by creating it if necessary. If you already have a database directory named foo in the data directory, you should move it to D:\data. Otherwise, the symbolic link will be ineffective. To avoid problems, make sure that the server is not running when you move the database directory.
  2. Create a text file C:\mysql\data\foo.sym that contains the path name D:\data\foo.

For more information, http://dev.mysql.com/doc/refman/5.0/en/windows-symbolic-links.html

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.