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:
grunt-sshwill 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.
- Basic familiarity with Grunt.
- 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:
grunt-sshto execute commands over SSH
grunt-execto execute local commands; and
grunt-peachto 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:
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.
- Dump our remote database into a file
- Download the remote database dump file
- Change the dump file’s URLs from our remote URL to our local URL (taking serialization into account)
- Import the newly migrated dump file into our local dev database
- 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:
save_pathmust use your server path (use
$ pwdto get this if you’re unsure) and it needs to be a publicly accessible directory
save_urlis the public URL of your
save_pathfor use with
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.
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 keys1 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!
By request, here is a complete
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!