Preserve your MySQL Databases Between Destroy and Up with Vagrant + PuPHPet on Windows

Have you ever run vagrant destroy on your box before you realized you wanted to keep the MySQL databases? Well I have, last night actually. Thankfully I use the wonderful and free Veeam Endpoint Backup to backup daily and was able to restore the old VMDK, connect it to a VirtualBox VM, use Ubuntu recovery mode to gain access, and then backup the databases to a shared folder. So that was an exciting learning experience.

Problem

And so it got me thinking – how can I make the databases persist between vagrant destroy and vagrant up so that I don’t lose them just because my memory was lacking. Separate VM? No, that’s more resources and adds complexity. Map MySQL data directory to host? No, been there done that, DB access is noticeably slower. Forget Vagrant and use Docker? No, I’ve tried it and though many love Docker it’s not for me; perhaps when Docker for Windows is as good as Docker for Mac/Linux.

Solution

UPDATE 2/9/2020: Unfortunately the PuPHPet project has been abandoned and is no longer viable as an easy-to-use Vagrant/Puppet configuration generator. Also, the vagrant-triggers plugin has been superseded by the built-in triggers in Vagrant 2.1.0 and later, which is a good thing. Due to these two issues this howto guide can no longer be followed to get the expected result. When I have found and implemented an alternative solution I will post and link from here. However, the scripts included below to backup and restore your databases are still sound and can be used with the built-in triggers in Vagrant 2.1.0 and later.

Use the vagrant-triggers plugin and a couple bash scripts to obtain developer happiness.

Prerequisites

• VirtualBox 5.0.26 or later
• Vagrant 1.8.6 or later
• Vagrant Plugins
  • vagrant-winnfsd: vagrant plugin install vagrant-winnfsd
  • vagrant-triggers: vagrant plugin install vagrant-triggers
  • vagrant-hostsupdater: vagrant plugin install vagrant-hostsupdater
• Customized Vagrant + PuPHPet configuration

Getting Started

Important: Use an elevated Command Prompt (Start button, type “cmd”, hold Ctrl+Shift and press Enter) for all commands.

Create a new folder where you would like to store the content and config for your new Vagrant box. In my case I have a Vagrant folder on a secondary hard drive that I use for Vagrant boxes. The directory name you choose isn’t important as long as you remember what box is in it. The Vagrant box’s actual name will be taken from PuPHPet’s config.yaml regardless.

C:\WINDOWS\system32>d:
D:\>cd Vagrant
D:\Vagrant>mkdir Developer
D:\Vagrant>cd Developer
D:\Vagrant\Developer>

Extract the customized Vagrant + PuPHPet config (see Prerequisites above) there and you should have the following files present:

D:\Vagrant\Developer>dir
 Volume in drive D is Storage
 Volume Serial Number is 4051-68B4
 
 Directory of D:\Vagrant\Developer
 
02/10/2017  04:54 PM

Issue a vagrant up and take a break while the box is provisioned.

D:\Vagrant\Developer>vagrant up
Bringing machine 'developer' up with 'virtualbox' provider...
==> developer: Importing base box 'puphpet/ubuntu1604-x64'...
==> developer: Matching MAC address for NAT networking...
==> developer: Checking if box 'puphpet/ubuntu1604-x64' is up to date...
==> developer: Setting the name of the VM: Developer_developer_1486767926284_24830
==> developer: Clearing any previously set network interfaces...
==> developer: Preparing network interfaces based on configuration...
    developer: Adapter 1: nat
    developer: Adapter 2: hostonly
==> developer: Forwarding ports...
    developer: 22 (guest) => 6241 (host) (adapter 1)
    developer: 22 (guest) => 2222 (host) (adapter 1)
==> developer: Running 'pre-boot' VM customizations...
==> developer: Booting VM...
==> developer: Waiting for machine to boot. This may take a few minutes...
 
...
 
==> developer: Did something go wrong? Don't worry! I can (maybe) help!
==> developer: Please go to our Github issues page at:
==> developer:
==> developer: https://github.com/puphpet/puphpet/issues
==> developer:
==> developer: and search for your problem. If you do not find your problem
==> developer: answered, open a new ticket!
==> developer:
==> developer: ------------------------------------------------------------
==> developer: PLEASE REMEMBER TO INCLUDE THE CONTENTS OF YOUR
==> developer: "puphpet/config.yaml" FILE.
==> developer: ------------------------------------------------------------
==> developer:
==> developer: Make sure to xxx out any potential API keys or passwords
==> developer: that you do not want others to see!
==> developer:
==> developer: Happy programming!
==> developer:                                              - Juan Treminio
==> developer:
==> developer: ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
==> developer: |  ____                _      _    _                    _  |
==> developer: | |  _ \ ___  __ _  __| |    / \  | |__   _____   _____| | |
==> developer: | | |_) / _ \/ _` |/ _` |   / _ \ | '_ \ / _ \ \ / / _ \ | |
==> developer: | |  _ <  __/ (_| | (_| |  / ___ \| |_) | (_) \ V /  __/_| | 
==> developer: | |_| \_\___|\__,_|\__,_| /_/   \_\_.__/ \___/ \_/ \___(_) |
==> developer: |                                                          |
==> developer: ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Alright, if everything went smoothly you should have a working vagrant box at http://developer.test that displays the PHP info page.

At this point we have no MySQL data but we do have an empty database, db1, so let’s install WordPress to generate some data.

After extracting the WordPress install zip file to D:\Vagrant\Developer\www\developer.test\public_html we can start the install at http://developer.test/wordpress.

D:\Vagrant\Developer>vagrant destroy
==> developer: Running triggers before destroy...
==> developer: Dumping the databases before destroying the VM...
==> developer: Executing remote command "cd /var/www/mysql && bash backupdbs.sh"...
==> developer: mysql: [Warning] Using a password on the command line interface can be insecure.
==> developer: Dumping database: db1
==> developer: mysqldump: [Warning] Using a password on the command line interface can be insecure.
==> developer: Remote command execution finished.
    developer: Are you sure you want to destroy the 'developer' VM? [y/N] y
==> developer: Forcing shutdown of VM...
==> developer: Destroying VM and associated drives...
==> developer: Pruning invalid NFS exports.
==> developer: [vagrant-hostsupdater] Removing hosts

D:\Vagrant\Developer\www\mysql>dir
 Volume in drive D is Storage
 Volume Serial Number is 4051-68B4
 
 Directory of D:\Vagrant\Developer\www\mysql
 
02/10/2017  07:19 PM

D:\Vagrant\Developer>vagrant up
...
 
==> developer: Running files in files/startup-once
==> developer: Created file /.puphpet-stuff/startup-once-ran
==> developer: Importing backupdbs.db1.sql ...
==> developer: mysql: [Warning] Using a password on the command line interface can be insecure.
==> developer: Finished running files in files/startup-once
 
...

Vagrant.configure('2') do |config|
  eval File.read("#{dir}/puphpet/vagrant/Vagrantfile-#{data['target']}")
end

Vagrant.configure('2') do |config|
  eval File.read("#{dir}/puphpet/vagrant/Vagrantfile-#{data['target']}")
 
  # BACKUP MYSQL DATABASES
  config.trigger.before :destroy do
    info "Dumping the databases before destroying the VM..."
    run_remote  "cd /var/www/mysql && bash backupdbs.sh"
  end
 
end

#!/bin/bash
 
USER="root"
PASSWORD="1234"
 
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
 
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "sys" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > backupdbs.$db.sql
    fi
done

cd /var/www/mysql
bash restoredbs.sh

#!/bin/bash
 
USER="root"
PASSWORD="1234"
 
databases=`ls -1 backupdbs.*.sql`
 
for db in $databases; do
        echo "Importing $db ..."
        mysql -u $USER -p$PASSWORD < $db
done