Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

Can I configure MySQL to store a specific database in a separate place on disk?

I still want any new databases to go to the default location, but there's a specific database that I'd like to have stored in my Dropbox folder so that it can stay synced between a few computers.

How can I do this?

share|improve this question
add comment

2 Answers

up vote 13 down vote accepted

Of course you can! You have to grant root permisson. Read the ln manual pages.

MySQL stores the database data in separated dirs in /var/lib/mysql. I think, at first you had to make a dir, where you want to put the selected database's files. Grant root permisson, stop MySQL deamon:

# /etc/init.d/mysql stop

Copy your data to your new folder:

# cp /var/lib/mysql/DATABASENAME /path/to/new/database/dir

Remove your old dir from MySQL data files: (Warning! ALWAYS make SQL dump first!!!)

# rm -R /var/lib/mysql/DATABASENAME

Make a symlink back to MySQL data dir: (Pay attention! Read the man page of ln first!!!)

# ln -s /full/path/to/new/database/dir /var/lib/mysql/DATABASENAME

After it, you are ready, start your MySQL:

# /etc/init.d/mysql start

Warning! If your new dir isn't write- and readable by MySQL that wouldn't work! Example, the common problem: you place your new data dir to your home folder, your home folder is read protected by others(ex: drwxrwx--- you you yourdir).

Ask, if you want to know other things about that method!

share|improve this answer
1  
Thanks! I didn't even consider symlinks, I was too busy thinking about ways to do it inside mysql. –  Colin Nov 22 '10 at 19:21
3  
You'll want to edit /etc/apparmor.d/*mysqld to include the new directory too. Then sudo service apparmor reload before restarting MySQL. –  Kees Cook Nov 22 '10 at 23:22
add comment

Please bear in mind that if you are using Ubuntu, you need also to add an entry in the AppArmour configuration file /etc/apparmor.d/usr.sbin.mysqld otherwise you will keep getting writing permission errors even though you change files and folder permissions.

share|improve this answer
add comment

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.