16

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?

  • 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 '18 at 10:00
25

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!

  • 2
    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
  • 7
    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
  • 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... – Toby J Aug 19 '15 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 '17 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.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.