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.
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:
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.
There is now a built-in MySQL feature for this:
https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html
CREATE TABLE t2 (c1 INT PRIMARY KEY)
TABLESPACE = innodb_file_per_table
DATA DIRECTORY = '/alternative/directory';
It is part of the CREATE TABLE syntax. The manual explicitly recommends this as a supported alternative to symbolic links.
Also, there may be a list of bad reasons to do this, and even a list of reasons that look good but bear gotchas, but there's also a perfectly legitimate list of good reasons for suitable cases. There are cases where this can and should be leveraged for performance when your storage hardware is tiered, and faster devices are limited in capacity.
