Moving MySQL Data Files to a VirtualBox Shared Folder

Out of virtual space?

Running out of MySQL storage space in VirtualBox? I was. And not once. I did some heavy Googling and excruciatingly long tests with results oscillating between disaster and epiphany (how glad was I to be able to restore the previous snapshot after each of the disasters!)

OK, so I run Ubuntu 12.04 LTS in latest VirtualBox on a Windows 8 machine. Recently I've been working with pretty heavy databases, and soon there was no more room in /var/lib/mysql ("datadir") for the database files.

Putting the data files to another partition was not an option because the total size of the virtual machine represented a limit as well. And expanding VirtualBox virtual machine seemed too daunting.

Thinking out of the box

My solution was to move the datadir to what VirtualBox calls a Shared Folder. Basically it's a folder on your host machine, in my case Windows, which is accessible from within the virtual Linux. Once the data folder is out of the virtual machine, it can grow much more freely on the host operating system.

All of the steps in this tutorial are from pieces strewn around the Internet, but all of them had funny hitches or imprecisions or missing stuff, so I've decided to write my own recipe.

So in practice...

First of all, make sure you take a snapshot of your virtual machine. Go to the Virtual Box main screen and click the Snapshots button. It only takes a second.

It is assumed you already use a Shared Folder and its Linux path is e.g. /media/sf_myfolder

It's easier if you are root.
sudo su -

It's crucial to stop MySQL before going any further. Moving database files while MySQL is running damages the internal data structure — the errors you'd see would make you scramble to revert to a previous snapshot in no time :-)
service mysql stop

Now let's move the default MySQL data folder from /var/lib/mysql to the shared folder and call it mysql_ext.
mv /var/lib/mysql /media/sf_myfolder cd /media/sf_myfolder mv mysql mysql_ext

Edit your my.cnf file. If it's in the default location, do the following.
nano /etc/mysql/conf.d/vacilando.cnf
and change the datadir path to:
datadir = /media/sf_myfolder/mysql_ext

Now we need to tell apparmor security system to tolerate the new location.
nano /etc/apparmor.d/usr.sbin.mysqld
There are 2 lines like:
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
Do not remove them, just add the same but with the new datadir:
/media/sf_myfolder/mysql_ext/ r,
/media/sf_myfolder/mysql_ext/** rwk,

Restart apparmor.
/etc/init.d/apparmor restart

The final catch

At this point I tried to start MySQL again but the process was failing. The error log contained things like: Can't find file: './mysql/plugin.frm' ... The error means mysqld does not have the access rights to the directory.

And that was not easy to figure out. Articles I found recommended chmod -R mysql:mysql /datadir but that would not work because it's not possible for Linux to set the permissions of the external folder!

At last I found the solution: let's add the mysql user to the vboxsf group.
sudo gpasswd -a mysql vboxsf

Thereafter, you can easily start mysql
service mysql start
... and go to do some more exciting things :-)

Tomáš Fülöpp
Sint-Agatha-Berchem, Belgium
May 30, 2014, May 31, 2014
Tomáš Fülöpp (2012)

Social media linksSocials

Related linksRelated links

LanguageENGLISH Content typeARTICLELast updateOCTOBER 20, 2018 AT 01:46:40 UTC