Local WordPress Development - How To Sync Uploads And Database From A Staging or Production Site

Given that WordPress is still growing1WP is about a third of the web now, as of April 2019, there’s no shortage of work for proper WordPress developers. In my case, work comes from several sources, all of it freelance. The vast majority of work I do is retainer. Agencies/companies need me to come into a sprint, usually for the stuff that’s a bit too much for juniors.

Since every project is different, the workflow is usually unique as well. Unless I’m involved with the engineering at project kick-off, I usually have to adapt to what’s been put in place by other developers. Typically that means writing custom shell scripts and aliases to help sync both code and content.

The Old Way

For ages, I’ve been a server geek. I have a whole rack of older, used Dell servers, as well as several other servers outside the rack. Back in the day, it was my normal process to spin-up a virtual machine, or VM, in Virtualbox on a local server, install Apache, PHP, MySQL, etc., and add some hosts records to point to alias the local environment with a practical, semantic URL.

The VM could be customized to fit any project in terms of resources, packages, and access. For client QA, I would add some port forwarding to allow outside access to my local server farm.

In the mid- to late-2000s, servers were expensive. Digital Ocean and Vultr didn’t exist yet. AWS was nascent and mostly an internal product for Amazon. Rackspace was king, and, yes, GoDaddy was already horrible.

If you didn’t have access to dedicated, server-grade hardware at home, another option was XAMPP, WAMP, etc. These were great programs because they ran on the machine you were already using, and they included a web server, database server, FTP server, and more right out of the box.

In my case, I ran XAMPP for some projects. I still do sometimes. In fact, I use it for the WordPress core repo. However, I use something else for most other projects because I don’t want to have databases on my main computer.

The New-ish Way

Going back to my old VM method for a minute, a lot of time was spent in configuration mode. Installing software, editing configuration files, setting up backups, and integrating code and content. You could clone an existing VM, but there was so much to update that it was almost easier to start from scratch, especially if a project had oddball requirements.

These days, containers are what we use. I won’t go deep into what they are, but they allow us to quickly and reliably spin-up an environment or a number of environments for development. Configuration is much easier and it’s very repeatable.

I’ll use the example of a basic WordPress environment using Docker. All you need is a Dockerfile from a common build and replicating an environment is trivial. Even in production, the same configuration can be cloned for horizontal scaling.

Packages like Apache2 (or nginx), PHP, MariaDB, etc. can all be installed and configured with just a few commands by referencing Dockerfiles. Even PHP extensions and Apache modules are setup and enabled without any direct editing of configuration files. Include the Dockerfile in the repo, and every member of the team can spin up the exact same environment for local development. No more worrying about mismatched PHP versions, caching issues, etc.

Of course, there are other ways to develop WordPress locally. Roots.io and Local by Flywheel are options. Local specifically requires a processor capable of virtualization, so if you can’t use Docker on your main workstation, it’s not an option either.

Syncing Content

Everything above is valid, but it’s nothing that hasn’t been covered a million times. All of the aforementioned tools help with getting a dedicated, local WordPress install up and running for development, but it does nothing to keep database and user uploads synced between staging and other members of the team.

You could manually SFTP uploads down periodically. You could also mysqldump to a file on the staging server, copy the file down, and import locally. However, these operations take time and you still end up with issues.

When a WordPress database is copied, the siteurl and home options are set to the source site’s main URL. Again, you could update these by hand in the database, but it’s another item in the long list of things to remember each time you sync content.

We’ve arrived at the point of this, until now, seemingly pointless blog entry. In a recent project, I decided to whip up a quick shell script to accomplish all of the content syncing from a common staging environment.

Prerequisites

If you’re on Windows, you’ll need to either install cygwin or some other bash-capable terminal interface. Also, you’ll need to have rsync or scp available, preferably the former. Finally, to update the options and URLs in the database, you’ll need to install WP-CLI.

Once you have all of that done, you can add a custom function to your bash profile to sync content:

function staging_content_sync() {
    LOCAL_SITE_URL="[LOCAL_SITE_URL]"
    LOCAL_PATH="[LOCAL_PATH]"
    STAGING_SITE_URL="[STAGING_SITE_URL]"
    STAGING_HOST="[STAGING_HOST]"
    STAGING_USER="[STAGING_USER]"
    STAGING_PRIVATE_KEY="[STAGING_PRIVATE_KEY]"
    STAGING_PATH="[STAGING_PATH]"
    LOCAL_DATABASE_NAME="[LOCAL_DATABSE_NAME]"
    LOCAL_DATABASE_USER="[LOCAL_DATABSE_USER]"
    LOCAL_DATABASE_PASSWORD="[LOCAL_DATABSE_PASSWORD]"
    USE_RSYNC=true

    cd "$LOCAL_PATH"
    echo "Pulling latest staging database export..."
    scp -Cpq -i $STAGING_PRIVATE_KEY $STAGING_USER@$STAGING_HOST:/tmp/staging.sql staging.sql
    echo "Done."
    echo "Importing production database locally..."
    mysql -u $LOCAL_DATABASE_USER -p"$LOCAL_DATABASE_PASSWORD" $LOCAL_DATABASE_NAME < staging.sql
    rm staging.sql
    echo "Done."
    if ! [ -x "$(command -v wp)" ]; then
        echo -e "\e[31mWarning: WP-CLI is not installed.\nYou need to update the siteurl and home options manually.\nYou also need to do your own search and replace for other URLs in the database..\e[0m" >&2
        return 0
    else 
        echo "Updating urls in wp_options..."
        wp option update siteurl $LOCAL_SITE_URL >/dev/null
        wp option update home $LOCAL_SITE_URL >/dev/null
        echo "Done."
        echo "Updating urls in other tables..."
        wp search-replace '$STAGING_SITE_URL' '$LOCAL_SITE_URL' >/dev/null
        echo "Done."
    fi
    echo "Production database sync complete."
    echo "Copying uploads..."
    if [ "$USE_RSYNC" = true ] ; then
        rsync -arthPz --progress --exclude '*.log' -e "ssh -i $STAGING_PRIVATE_KEY $STAGING_USER@$STAGING_HOST:$STAGING_PATH/wp-content/uploads/
     wp-content/
    else
        scp -rpqC -i $STAGING_PRIVATE_KEY $STAGING_USER@$STAGING_HOST:$STAGING_PATH/wp-content/uploads/ wp-content/
    fi
    echo "Done."
    echo "Staging content sync completed!"
}

The script is actually pretty tame. There’s no awk or cut. Once you’ve added the script to your user profile and customized the variables at the top, you can just run staging_content_sync whenever you need to grab the freshest content from a remote WP install. You’ll probably need to tweak it and hopefully extend it!2There’s so much that could be added. Pull from the repo, run automated testing, etc.

This isn’t a new concept. Pantheon – which I highly recommend – has been doing this for while. Click a button and content is pulled down from Staging or Live to Dev or any Multidev environment you choose. I wanted to replicate that process, so the function above is what I initially came up with.

If you do end up using or improving the code above, please leave a note in the comments!

About

Web Developer

References

References
 1 WP is about a third of the web now, as of April 2019
 2 There’s so much that could be added. Pull from the repo, run automated testing, etc.

3 Comments

  1. Benji

    Your new method happens to be my preferred method for some years! With all sorts of new tools developed as paid SaaS applications and premium-plugin solutions it made me really happy while scrolling down to see my hero `rsync` and sidekick `mysqldump` coming in to save the day!

    I have two additions that you may find useful. I’m using OpenSSH 7.9p1, though these options should work on most setups. It will require your SSH user to already have access to mysql via SSH (my.cnf file on remote server) and a local SSH config file would make these commands even shorter!

    Dump directly to local database:

    `ssh -TC name@remote-host “mysqldump database_name” | mysql local_database`

    Or with find / replace:

    `ssh -TC name@remote-host “mysqldump database_name” | sed ‘s,remote-site.com,local-site.test,g’ | mysql local_database`

    I developed these well before WP-CLI and still prefer it this way 🙂

  2. RZKY

    I’m still not clear on how did you get the “staging.sql” file in the first place. Also, to be clear, this is only a one-way sync right? From staging to local?

Add Your Thoughts

Your email address will not be published. Required fields are marked *