Using Aliases to Sync Databases Across Development Environments

One of the common tasks that web developers run on a regular basis is syncing development sites with a live site to ensure that the data is the same. Using WordPress aliases it is possible to reference a site from the command line that is on a remote server without changing directories. I combined aliases with a few quick WP-CLI commands to create a simple and repeatable way to pull a production database to a local or staging site. These commands can be run one by one, but the simplest method I’ve found is to create a shell script, make it executable and then run it whenever content needs to be refreshed from production. Here’s how to get this running:

Setup Your Aliases
Aliases require an SSH connection in order to interact with a remote server. On WP Engine this can be found in your site Overview tab in the User Portal once you have added an SSH Key. When you have your SSH connection string ready there are just a few steps to make aliases work. The script requires two aliases, one called @prod and one called @dev in order to work. You may have to tweak things to get this to work, but it will get you 99% of the way there.

  1. Create a file called wp-cli.yml in your WordPress project’s directory.
  2. Define @prod and @dev remotes to connect to your site environments in the wp-cli.yml file
  3. @prod:
    ssh: user_name@production_install_name@ssh.wpengine.net
    @dev:
    ssh: user_name@dev_install_name@ssh.wpengine.net

  4. Optionally, you can run wp cli alias add @prod --set-ssh=user_name@production_install_name@ssh.wpengine.net and make sure that you replace the user and install name to the ones you are using.
  5. Check that your aliases are defined by running wp cli alias list

Run the Script
The script will run a few commands to complete the database pull.
It gets the site url from dev and production via the WordPress options table. It copies the production database to a local file and then imports it to the @dev site. It then runs a search and replace to update the site url and finally deletes the exported local sql file.

When you’ve created your aliases you can do the following to run the script:

  1. Copy the code below to a file called get_prod_db.sh
  2. Change the file permissions so the file is executable – chmod +x get_prod_db.sh
  3. Run the script by typing ./get_prod_db.sh

As with anything database related there is some risk in getting this wrong, so please make sure you test this out and work out any issues on a non production site just in case. There’s a couple of reference links that will go further in to depth on aliases and config files below. Hope this works for you and happy coding! Please let me know if you have any questions.

More info on configuration files

More info on aliases