19

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?

Improve this question
1
  • 1
    Incidentally, it's possible to do this the other way around. Leave the database as it was, and symlink to it from Dropbox. Dropbox doesn't really understand symlinks, and will just sync them as normal.
    – TRiG
    Jul 30, 2018 at 10:00

2 Answers 2

Reset to default
27

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!

Improve this answer
4
  • 2
    Thanks! I didn't even consider symlinks, I was too busy thinking about ways to do it inside mysql.
    – Colin
    Nov 22, 2010 at 19:21
  • 8
    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, 2010 at 23:22
  • 3
    @KeesCook thanks so much for the apparmor tip, that was driving me crazy!! Looks like it's better to put it in /etc/apparmor.d/local though (on my install there's already a usr.sbin.mysqld there), adding a single /full/path/to/new/database/dir/** rwk, did it for me...
    – Tobias J
    Aug 19, 2015 at 18:47
  • Thanks so much, some details of editing the apparmor here is also useful. I was slowed down trying to put the database on a drive which does did not mount automatically at boot and needed root user to access it. Refused to work. Edited fstab like this so that that drive was mounted in the file system after bootup, now everything works.
    – cardamom
    Sep 10, 2017 at 17:02
3

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .