WordPress Database Sync with Grunt

- 14 comments

Development,Grunt,Snippets,WordPress

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 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!

*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!


Comments

  1. This sequence of syncing is from remote to local right. May I know how the procedures if the sequence is from remote to local

    1. Thanks for commenting, Aziz. I’ve added enough of the basic pieces that you should be able to reverse it rather easily. Which part are you stuck on specifically?

      1. Right now I try to edit sort of the syntax, like mysqldump from ‘>’ to ‘<' and I resort back the task so that the sync will go from local site to remote.. Yet It's not working.. Plus, how is it still prompting user to key in the local db password, I see the syntax is already include the password.. Thanks

        1. Ok Aziz, I added the configs I use for my remote sync above. Beware, there are some changes in the way I do local sync in the updated configs too. Enjoy and let me know if you get stuck!

          1. Michael, after running the tasks of sync-remote-db, I found the same problem that i had before. The task stuck at sshexec:import_migrated_local_dump.. The error says that ‘MySQL has gone away’. I found this error too when I was importing the db to the remote db.. Most said it’s about the max_execution_time, but I already increase it.. Hope u can help..

          2. So you experience the same error message when you use the Grunt task as when you attempt to import the dump file manually? If that is the case then it’s possible that you may need to specify your MySQL server’s host_name or port (see here). You may need to add it to Gruntfile.js yourself once you get it working manually. A good place to start is in your wp-config.php file, check for define('DB_HOST'… and if it’s not defined as 'localhost' then use the -h switch accordingly. It’s also possible that it’s running on a non-standard port so be sure and check with your host.

            1. Yes. Btw it’s not only happen when I used grunt task. Before this I traced what is the real issue, i noticed after running peach task to replace domain in database, ‘Mysql has gone away’ occurred either using grunt or directly import using phpmyadmin. But it’s ok before running the peach task.

  2. …thank you for this article. It’s very useful. However when executing the sync_local_db task I keep getting either an “Authentication failure. Available authentication methods: publickey,password” or “Time out while waiting for handshake”
    I have already enabled SSH on my server and vae both public and private keys in .ssh inside my user folder.

    1. Yahzee, are you able to successfully open an SSH connection to your web host without being prompted for a password? Have you tried manually performing each of the tasks which this task automates?

Leave a Comment