Home / ubuntu / Questions / 14418
Accepted
Colin
Asked: 2010-11-23 08:16:56 +0800 CST

Different locations for MySQL databases?

  • 772

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?

mysql database

2 Answers

  1. 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!

    • 27

  2. 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.

    • 3