About changing data path by database Is there any way to change physical path by database?

For example :

I have 2 databases in server.

database1 will be located in /data/database1/

database2 will be located in /data/database2/

Is this possible ?

thanks in advance.
If you are only running one mysqld instance and want to physically store databases on different disks, here is an approach:

Let's say you


datadir is /data
Two databases


database1
database2

you have a Disk Volume mounted on /data1
you have a Disk Volume mounted on /data2


All MYISAM

cp /data/database1/* /data1
cp /data/database2/* /data2
chown -R mysql:mysql /data1
chown -R mysql:mysql /data2
mv /data/database1 /data/database1_old
mv /data/database2 /data/database2_old
ln -s /data1 /data/database1
ln -s /data2 /data/database2
chown -R mysql:mysql /data/database1
chown -R mysql:mysql /data/database2


After this, when you run SHOW DATABASES; make sure the tables in database1 and database2 are fully accessible. If they are, you could then do

DROP TABLE database1_old;
DROP TABLE database2_old;


All InnoDB or Mixture of InnoDB/MyISAM

MYSQL_CONN="-uroot -ppassword"
MYSQLDUMP_OPTIONS="${MYSQL_CONN} --single-transaction --routines -triggers"
mysqldump ${MYSQLDUMP_OPTIONS} database1 &gt; database1.sql
mysqldump ${MYSQLDUMP_OPTIONS} database2 &gt; database2.sql
mysql ${MYSQL_CONN} -ANe"DROP DATABASE database1; DROP DATABASE database2;"
chown -R mysql:mysql /data1
chown -R mysql:mysql /data2
ln -s /data1 /data/database1
ln -s /data2 /data/database2
chown -R mysql:mysql /data/database1
chown -R mysql:mysql /data/database2
mysql ${MYSQL_CONN} -Ddatabase1 &lt; database1.sql
mysql ${MYSQL_CONN} -Ddatabase2 &lt; database2.sql


CAVEATS


Please test this on a staging server
Please make backups before doing this
Please test full accessibility (SELECT queries)
Heads up on InnoDB: This may not produce better performance for .ibd files sitting on a disk volume apart from ibdata1. I wrote about this before


Feb 06, 2012 : MySQL tables/databases into different disks
Aug 07, 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?