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 :-)
ENGLISH ARTICLEOCTOBER 20, 2018 AT 01:46:40 UTC