Vote Charlie!

Moving MySQL data with Vagrant and Ubuntu 14.04

Posted at age 26.
Edited .

I recently started doing my local development on virtual machines using Vagrant, and PuPHPet. I really should have started this long ago given all the hoops I’ve had to jump through setting up Perl and friends with every new version of OSX. And even though the new OSXes lately barely qualify as a new version and come out more and more frequently, they still break nearly everything about my development environments. So, virtual machines are super convenient.

Note: This is more of a personal journal than a tech guide, for I don’t claim to have enough expertise in any of these areas to write a comprehensive guide. But perhaps you’ve also been troubleshooting this niche problem, in which case I would be interested in hearing if this worked for you as well.

But now I was trying to import a MySQL database that is around give gigabytes, and somehow that import process maxed out the 18GB filesystem of this Ubuntu 14.04 system. I decided to move the MySQL data directory to the part of the filesystem that is shared with the host OSX, so the size of the databases shouldn’t matter. It wasn’t as simple as I hoped. Or I’m an idiot.

First I moved /var/lib/mysql/ to /var/www/mysql and changed the datadir line in /etc/mysql/my.cnf, and restarted MySQL. Only it didn’t start. After some research, I found Ubuntu has some security system called AppArmor that needs to know about the new location. Various StackExchange answers mention needing to change a line in /etc/apparmor.d/usr.sbin.mysqld, but that didn’t exist on my system. I did a search for /var/lib/mysql within /etc/apparmor.d and found the file I apparently needed to change, alias. I was still having issues getting MySQL to start, which I thought had something to do with permissions. I tried setting MySQL to run as www-data, since that user owned the mounted folder /var/www, and I couldn’t seem to reassign permissions within it, apparently due to how Vagrant mounts it. So the following is what ended up working for me.

Moving MySQL data directory on Vagrant / PuPHPet / Ubuntu 14.04

Ideally this would be integrated into a nice startup script, but I found I couldn’t mount the MySQL folder with the mysql user on VM creation since I got a “user does not exist” error, so my steps are a bit clunky. The goal is to move the data folder from /var/lib/mysql/ to /var/mysql/, which will actually exist outside the VM’s main filesystem, and thus not be restricted by the VM’s size. This is also assuming the VM has never been initialized. If it already exists, just run the commands from the script in exec-once instead of making that file.

  1. Within the Vagrant folder, edit puphpet/config.yaml to include the following under synced_folder (first three lines don’t need to be added, but are here to show the hierarchy):
                    owner: www-data
                    group: www-data
                    source: ./mysql
                    target: /var/mysql
                    sync_type: default
                            - '--verbose'
                            - '--archive'
                            - '-z'
                        auto: 'false'
    1. Create mysql at the root of your Vagrant folder. You can put it somewhere else if you modify the source line accordingly.
    2. Create a startup script to set up the data directory. Make a file called “” in puhphet/files/exec-once with this:


      echo “alias /var/lib/mysql/ -> /var/mysql/,” | sudo tee -a /etc/apparmor.d/tunables/alias sudo /etc/init.d/apparmor restart sudo sed -i ‘s!datadir(\s=\s)/var/lib/mysql!datadir\1/var/mysql!g’ /etc/mysql/my.cnf sudo sed -i ‘s!max_allowed_packet(\s=\s).*!max_allowed_packet\1 512M!g’ /etc/mysql/my.cnf sudo sed -i ‘/datadir/a innodb_log_file_size = 1G’ /etc/mysql/my.cnf sudo mv /var/lib/mysql/mysql /var/mysql/ sudo /etc/init.d/mysql restart 1. Initialize the VM with vagrant up. 1. Turn it off with vagrant halt. 1. Edit puphpet/config.yaml again, replacing www-data with mysql for the owner and group of the synced folder section you added. 1. Start the VM again with vagrant up.

I could apparently fix this a bit doing something like the answers on Vagrant chicken-and-egg: Shared folder with uid = apache user.