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?

antivirtel
  • 3,635
  • 4
  • 30
  • 46
Colin
  • 708
  • 5
  • 10
  • 20
  • 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 '18 at 10:00

2 Answers2

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!

Isaiah
  • 58,486
  • 28
  • 133
  • 145
antivirtel
  • 3,635
  • 4
  • 30
  • 46
  • 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
  • 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 '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... – Tobias J Aug 19 '15 at 18:47
  • Thanks so much, some details of editing the apparmor [here](https://stackoverflow.com/questions/4182468/moving-mysql-innodb-database-to-separate-drive/11751680#11751680) 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](https://askubuntu.com/questions/154180/how-to-mount-a-new-drive-on-startup) 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.

Andrea Moro
  • 481
  • 1
  • 5
  • 16