Preserving Databases When Destroying a Vagrant Box

I’ve been using Homestead for local development for quite some time now. I run all of the types of sites that I work on on it — Laravel, WordPress, even ExpressionEngine. I don’t remember the last time that I fired up MAMP.

I have my Homestead box set up so that one box runs multiple projects. I do this because sometimes I have to jump between projects. It’s more convenient to have all of my projects on one box than to have to fire up multiple boxes.

Almost every one of my projects requires a database. That means that I have quite a few databases set up on my Homestead box. So when it comes time to destroy the box — for example when it’s time to update to the latest version, I have to fire up Sequel Pro and export each of the databases individually. Then once I’ve spawned a new box, I need to import those databases individually — the structure and content — so I can continue working on my projects. That is quite time consuming and cumbersome when you have multiple databases.

I did a little research yesterday to see if I could find a way to automate (or partially automate) this process so that it isn’t such a pain in the ass to destroy and re-spawn a box. I found some code that will export databases and more code that will import databases. So I created a couple of bash scripts with the code so that I could test things out. They worked great. So I wanted to share in case these scripts — and my process — could help someone else out (and so I can come back to this post when I forget what I did but need to do it again LOL).

First up … the script to export the databases. I called it dumpdbs.sh. Don’t forget to modify the script to set your own database username and password.

#!/bin/bash
 
USER="dbusername"
PASSWORD="dbpassword"

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 > backup.$db.sql
    fi
done

Next up … the script to import the databases. I called it restoredbs.sh. Again, modify the database username and password!

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

And I just want to make a note … “-p$PASSWORD” in both scripts is not a typo. I had to look this up myself since I don’t interact with MySQL at the command line. When you use the -p flag for the password, there can be no space between the flag and the password itself.

Okay. That’s great. I have a couple scripts to export and import my databases. But how the hell do I use them? Well, let me tell you what I did. 😊

BEFORE you destroy your box, SSH into the box and run the script to export the databases.

vagrant ssh
cd /path/to/where/you/saved/the/files
bash dumpdbs.sh
exit

Then you can destroy your box and spawn a new one.

vagrant destroy
vagrant up

Once the new box is up and running, SSH into the new box and run the script to import the databases.

vagrant ssh
cd /path/to/where/you/saved/the/files
bash restoredbs.sh
exit

No more having to open up Sequel Pro to manually export and import a bunch of databases individually.

My next task is to figure out how to incorporate these scripts into the destroy / spawn process within my Homestead setup so that it is automatic. The article where I found the code for the bash scripts also includes some code that you can add to your Vagrantfile to trigger the running of these scripts. It looks like it requires some Vagrant plugins, so I still need to do some research to figure everything out and then figure out where to put the necessary code in my Homestead setup.

But for now, I’m okay with the process being manual. This way I know that the database exporting was successful before destroying the box. I just have to remember to run the script before I destroy the box. That’s what sticky notes are for. 😁

I found the code for the 2 bash scripts here.

Share this: