Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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/<user>/mysql directory.

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

How would you do this?

share|improve this question
    
@RolandoMySQLDBA they are InnoDB, post edited to reflect. –  Highway of Life Mar 12 '12 at 22:35
    
Did you try looking at dev.mysql.com/doc/refman/5.1/en/multiple-data-directories.html or dev.mysql.com/doc/refman/5.1/en/multiple-servers.html by chance? Im curious what, if anything, about those made this question less than ideal? –  jcolebrand Mar 13 '12 at 5:32

1 Answer 1

up vote 2 down vote accepted

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> select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');

Let's start by creating temp databases

mysql> create database tmpdb1;
mysql> create database tmpdb2;
mysql> 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')" > /root/MoveTables1.sql
mysql -uroot < /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> use db1
mysql> show tables;
mysql> use db2
mysql> show tables;
mysql> use db3
mysql> show tables;
mysql> use tmpdb1
mysql> show tables;
mysql> use tmpdb2
mysql> show tables;
mysql> use tmpdb3
mysql> show tables;

Next, drop the original databases

mysql> drop database db1;
mysql> drop database db2;
mysql> 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> 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')" > /root/MoveTables2.sql
mysql -uroot < /root/MoveTables2.sql

Finally, run the first query you started with

mysql> 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.

share|improve this answer
1  
I'd also note that putting .ibd files on separate disk will not help much with performance, only storage management because of how the various internal threads work and the contention on the ibdata file. –  Aaron Brown Mar 13 '12 at 1:10
1  
@AaronBrown you are so right. I wrote something back in August based on a linked from mysqlperformanceblog.com. (picardfacepalm.com) I will. update my answer. +1 on your comment. –  RolandoMySQLDBA Mar 13 '12 at 1:40

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.