How to set a specific directory location for individual MySQL database? Is it possible to set each database in MySQL to use a separate datadir?

I'm running a userdir development (sandbox) server and would like to put the MySQL data files for the database(s) for that user in their /home/&lt;user&gt;/mysql directory.


Linux Ubuntu Server 10.4
MySQL Server version: 5.1.41
Storage Engine type: InnoDB


How would you do this?
You would have play games with symlinks.

WARNING : The following only works with innodb_file_per_table enabled

For example, suppose your datadir was /opt/mysql/data. Each database woudl reside under that folder. If you have three databases (db1, db2, db3), then the folders are:


/opt/mysql/data/db1
/opt/mysql/data/db2
/opt/mysql/data/db3


Before doing anything run this query:

mysql&gt; select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');


Let's start by creating temp databases

mysql&gt; create database tmpdb1;
mysql&gt; create database tmpdb2;
mysql&gt; create database tmpdb3;


Next, let's move every table you have db1 to tmpdb1, db2 to tmpdb2, db3 to tmpdb3

mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' RENAME tmp',db,'.',tb,';') FROM information_schema.tables WHERE table_schema in ('db1','db2','db3')" &gt; /root/MoveTables1.sql
mysql -uroot &lt; /root/MoveTables1.sql


OK, we moved every .ibd file sideways to tmp databases

Go into mysql and make sure all the tables have been moved. Databases db1-db3 should be empty

mysql&gt; use db1
mysql&gt; show tables;
mysql&gt; use db2
mysql&gt; show tables;
mysql&gt; use db3
mysql&gt; show tables;
mysql&gt; use tmpdb1
mysql&gt; show tables;
mysql&gt; use tmpdb2
mysql&gt; show tables;
mysql&gt; use tmpdb3
mysql&gt; show tables;


Next, drop the original databases

mysql&gt; drop database db1;
mysql&gt; drop database db2;
mysql&gt; drop database db3;


Go into the OS and create symlinks

ln -s /home/user2/mysql /opt/mysql/data/db1
ln -s /home/user2/mysql /opt/mysql/data/db2
ln -s /home/user3/mysql /opt/mysql/data/db3
chown -R mysql:mysql /opt/mysql/data/db1
chown -R mysql:mysql /opt/mysql/data/db2
chown -R mysql:mysql /opt/mysql/data/db3


Go into mysql and make sure the databases are visible

mysql&gt; show databases;


Next, move the tables into the symlinked databases

mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' RENAME ',SUBSTR(db,4),'.',tb,';') FROM information_schema.tables WHERE table_schema in ('tmpdb1','tmpdb2','tmpdb3')" &gt; /root/MoveTables2.sql
mysql -uroot &lt; /root/MoveTables2.sql


Finally, run the first query you started with

mysql&gt; select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');


If the same display comes up, you did it successfully.

UPDATE

@AaronBrown was kind enough to remind me that spreading .ibd files does not buy you anything except for spread-out files. No performance gains whatsoever. Thanks Aaron.


https://dba.stackexchange.com/a/4425/877
http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/