Ubuntu – Different locations for MySQL databases

databaseMySQL

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?

Best Answer

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!

Related Question