How to set a specific directory location for individual MySQL database?

Question

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?

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

I'm not entirely sure you can. Not without running each one inside it's own chroot inside ~username. — Tom O'Connor, Mar 12 '12 at 21:39

score 2 · Accepted Answer · answered Mar 12 '12 at 21:49

2

If you are not using INNODB or the innodb_file_per_table, then just stop the database server, move the database folder to whatever location you like, then create a symbolic link to the new location from the mysql folder. Mysql will still need access to the files, so you can't simply change the permissions so that particular user owns the directory/files though. Please do test first with test data.

answered Mar 12 '12 at 21:49

Zoredache

128,755
40
271
413

Same process for myisam files - but you move the directory containing the database. Or use the DATA / INDEX DIRECTORY options on CREATE TABLE – symcbean Mar 13 '12 at 10:36

How to set a specific directory location for individual MySQL database?

1 Answers1