WordPress Database Sync with Grunt

Updated 11/9/2014; last updated 1/8/2015

Export your WordPress database and migrating it manually is relatively trivial, or select from the myriad of plugins that accomplish this. But if you use Grunt to manage your development tasks then there’s a quick way to integrate WordPress database sync into your Grunt workflow and automate your sync with just one simple command.

I first attempted this with several Grunt modules that claimed to export and import MySQL databases but I found the tasks were unreliable. So let’s walk through the down and dirty WordPress database sync tasks I created. Improvements and suggestions are welcome.

Before We Begin

In this walkthrough I make the following assumptions:

1. Although grunt-ssh will allow you to use a password to access your remote environment, you should configure your remote environment to use authorized keys so that SSH does not prompt you for a password.
2. Basic familiarity with Grunt.
3. Basic familiarity with the command line.

Ok? Good, let’s begin!

Install Grunt Tasks

The three Grunt tasks I settled on to get this working are:

1. grunt-ssh to execute commands over SSH
2. grunt-exec to execute local commands; and
3. grunt-peach to migrate our database URLs and handle serialized database entries (which relies on Peach)

So let’s install these Grunt modules in one fell swoop with:

Task Overview

The next step is to configure our Grunt tasks, but let’s take a moment and think through what our workflow should look like to sync our local database with our remote.

1. Dump our remote database into a file
2. Download the remote database dump file
3. Change the dump file’s URLs from our remote URL to our local URL (taking serialization into account)
4. Import the newly migrated dump file into our local dev database
5. Cleanup our dump files

That’s pretty standard if you’ve done this manually and it should seem familiar. Automating this will save time so let’s get to it.

Setup a Configuration File

I know it seemed like I was going to jump right into configuring our tasks, but we need a config file to store a few URLs and our database credentials so they’re not included in our Gruntfile.js and therefore will be omitted from our project repo for security. My config file is called mysql.json and I placed it in the directory where I run Grunt from in my project. Here’s what it looks like:

Two quick notes on this file:

1. save_path must use your server path (use $ pwd to get this if you’re unsure) and it needs to be a publicly accessible directory
2. save_url is the public URL of your save_path for use with wget

The remainder should be relatively self explanatory. In accordance with the twelve factor app methodology we’re storing credentials separately and this file should be added to your .gitignore so it’s excluded from your project repo.

Read mysql.json and Add a Timestamp

We’re about to register our Grunt task for WordPress database sync and we need to tell our Gruntfile.js to read the mysql.json we just created. I also want to create a quick variable in case I later decide to remove/modify the cleanup subtasks and maintain a database backup. So I created a variable that will grab the current timestamp for use in filenames. These two lines look like this:

Now let’s translate our task overview from above into something Grunt understands…

Register Our Task

It’s helpful to register Grunt tasks before configuring them to visualize a roadmap of exactly which tasks need to be configured and the functions they need to complete. For my WordPress database sync I registered one task using the task overview from up above as a guide:

If you’ve followed along to this point then you’re ready to configure the Grunt tasks that will carry out your database sync.

Configure Grunt Tasks

If you’re familiar with Grunt then these should be self-explanatory. I’m sure there’s room for improvement—as I said, this is pretty down and dirty:

Quick note: In sshexec I use agent: process.env.SSH_AUTH_SOCK to tell SSH to use my private keys¹ but you can just as easily use a password here instead, just make sure you add it to your mysql.json to keep credentials out of your repo.

Sync Your Database!

Now you can run $ grunt sync_local_db in your project directory and your local database will be synced with your remote.

The configuration options are flexible enough to accommodate syncing your remote database with a bit of tweaking if that’s something you need. Note that if you uploaded files or images through the WordPress Media Library on your remote then those files will need to be transferred otherwise you may end up with broken links and images. This crash course could easily be adapted to also automate a task for transferring your uploads using tar and I may cover that in a future post.

Where to Go from Here

What’s nice about Grunt is that once you take the time to peer back the curtain it’s pretty easy to understand. You could easily take what I’ve done and tweak it to sync your remote db with your local, if that’s something you need. You can also take Grunt’s automation even further and add this task to your Capistrano deploy script or even your Git hooks so that your databases sync each time you commit, push, or deploy. There are nothing but possibilities!

*Update 11/9/2014

By request, here is a complete mysql.json and Gruntfile.js for both local and remote sync. Note that I use a directory within my theme structure called peach/ to store all temporary dumps in and this directory must also be present on your remote environment. The best way to do that is to create the directory first locally, then add an empty .gitkeep file to it and commit/push/pull your changes on your remote before you attempt a sync. These configs could definitely be improved on and tightened up in a few places so feel free to fork and revise them. Be careful!