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

• https://dba.stackexchange.com/a/4425/877

• http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/

StackExchange Database Administrators Q#14875, answer score: 7