MySQL – How to Set a Specific Directory Location for Individual Database

configurationinnodblinuxMySQLUbuntu

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?

Best Answer

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.

Related Question