About changing data path by database

Question

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.

Answer 1

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 > database1.sql
mysqldump ${MYSQLDUMP_OPTIONS} database2 > 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 < database1.sql
mysql ${MYSQL_CONN} -Ddatabase2 < 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)?

Answer 2

Sure you can, in your my.cnf you can specify multiple mysqld sections as follow:

[mysqld1]
datadir                  = /database/data1/mysql
port                     = 3306
socket                   = /database/tmp/mysql1.sock
pid-file                 = /database/tmp/mysql1.pid
...

[mysqld2]
datadir                  = /database/data2/mysql
port                     = 3307
socket                   = /database/tmp/mysql2.sock
pid-file                 = /database/tmp/mysql2.pid
...

Then use the mysqld_multi program to start and stop the mysqld you want to:

mysqld_multi start 2

Max.

Answer 3

Know that if you want to use the same MySQL instance, you "can't chose" file location. In MySQL we don't have this flexibility because we don't have the "notion" of "Tablespaces" in oracle or "File Groups" in SQL Server.

You just choose a location for all your datafiles in the my.cnf file, you can use the parameter file_per_table when you want to split your data in different files (one per table) but all these files will in datadir.

One solution is to use symlinks in UNIX if you want to "load balanced" your disk usages (The solution that Rolando explain)