It is common practice for developers to work on several servers or tiers in order to test their work and make sure code is ready to deploy without introducing errors on a live production environment. WP Engine features multiple environments per WordPress installation and provides a multi-tiered workflow. It is possible for developers to work on a separate development site until code is ready to be shared with a client. The finished code can then be pushed to staging for customer approval. Once the customer signs off, it is possible to push the code live in the WP Engine User Portal or for the command line using Git.
This setup provides a great deal of flexibility and there is a benefit in having all three tiers running in the exact environment in order to eliminate any issues from version compatibility or configuration differences. As code is tested on the exact same stack, there is no reason why it will work on one tier and not the other.
There is one problem though. Developers will often work in a local environment as part of their daily workflow which happens outside of the WP Engine environment. The problem lies in the fact that in order to sync data between the two environments a database export/import is required along with some other minor transformations.
In my last post, I looked at how WP-CLI Aliases can be configured in order to use SSH to access local and remote servers. In this post, I’m going to crack the tough nut of how to sync a local database to a remote site using the command line and WP-CLI. In this case, the remote site is my production installation of WordPress running on WP Engine and my local install is a copy of the site that I use for development running VVV on my MacBook Pro. OK, Let’s dive into the details.
WP-CLI Database Commands
One of the functions of WP-CLI called wp db enables database management and administration. It is possible to create a new database, drop tables, repair a database and even run queries which have been stored as a file. There are many other operations available as you can see in the WP-CLI documentation
The function wp db takes additional commands in order to specify which operations to perform. A typical command looks like this: wp db optimize. In this case, we’re asking WP-CLI to optimize the database. WP-CLI commands are somewhat easy to remember because they always start with wp then there is a function like db and then commands with arguments will follow.
For our example, I’ll use wp db export and wp db import in order to grab the database from my production server and then import it to my local VVV vagrant box. In order to target a specific remote instance of WordPress I’ve set up Aliases in VVV that will allow me to simply target a remote or local WordPress instance.
Exporting the Database
The full command that I use to export the database from my production server is below.
wp @prod db export - > prod.sql
wp @prod specifies that I want to run this WP-CLI command on my Production server which is configured as an Alias. The actual command being run is wp @prod db export which will export the database to a local file on the production server and name it in the format of {dbname}-{Y-m-d}-{random-hash}.sql as a default option. My script will require the file be named specifically and downloaded to my local machine which is why there are additional options. The - option allows the file to be output to standard out in my terminal. This results in the SQL dump being printed to the screen and results in a long trail of SQL statements. The final option > allows the output to be stored in a file instead of printed to the screen, which I’ve named prod.sql.
Getting the live Site URL
In order for the database to be imported properly, we’ll need to change the option for the site URL stored in the wp_options table. This change allows our links to point to the dev site in our development environment rather than our production site. We can do this quickly with WP-CLI by running the wp option get command. As we have an alias set up for production we can use this to remote into our production site and get the value for ‘siteurl’. The full command looks like this: wp @ prod option get siteurl(). In the spirit of automation, it makes sense to save the value so that we can use this to update our development siteurl. We can do this by storing the siteurl value in a variable. The full command looks like this: prod_siteurl=$(wp @prod option get siteurl);. We can then get the value for our development siteurl in order to change the siteurl in the database export to work on the development site. This all comes together to look like the following example.
Get the siteurl from production and development
prod_siteurl=$(wp @prod option get siteurl); dev_siteurl=$(wp @dev option get siteurl);
Search and Replace
We’ve stored the values for the production and development site URLs in order to run a search and replace operation in our exported database to update the siteurl value. This can be done by running the command wp search-replace prod_siteurl dev_siteurl. Since we’ve saved the siteurl values as variable, we can use those in our command and execute it on our development server.
Search and replace siteurl value on development
wp @dev search-replace $prod_siteurl $dev_siteurl
We’ve got a little bit of housecleaning to do with regards to the exported sql file. As I’ve updated the database there’s no need to save and it can be removed.
Remove .sql file
rm prod.sql
The Final Script
The text below can be saved as getdb.sh on your local machine and executed once you change permissions to allow the script to be executed. You can add execute permissions to the script using the chmod command on the command line.
Execute Permission with chmod command
chmod +x getdb.sh
All Together Now
The script can now be run by typing the following in the directory where the script has been saved.
Execute the Script
./getdb.sh
The Complete bash script
#!/bin/bash
This script will get a production database and import into a local WordPress installation. Aliases @prod and @dev are used to simplify the commands.
Get siteurl from prod and dev
prod_siteurl=$(wp @prod option get siteurl); dev_siteurl=$(wp @dev option get siteurl);
Export database from prod and import to dev
wp @prod db export - > prod.sql wp @dev db import /srv/www/edmund/prod.sql
Update siteurl
wp @dev search-replace $prod_siteurl $dev_siteurl #Remove .sql file rm prod.sql
You can also view this code as a View the code on Gist.(‘http://add-github-url’) on github.
Conclusion
OK, so that is a lot to take in, but in reality, we’ve completely automated a remote connection to our production machine and downloaded a mysql dump of our WordPress database to our local machine. We’ve then run a search and replace operation in order to update the siteurl value and allow for links to work properly within the development site. That’s pretty powerful and can be executed from a few lines of code whenever needed by saving the commands to a script and allowing it to be executable. The end result is a reusable database export/import that can be run with a simple command.
That’s all for now, and I hope this is helpful. Keep an eye out for more of my posts in the near future.
Edmund Turbin
Edmund Turbin is Senior Sales Engineer at WP Engine, London. Over the past ten years he has built websites with content management systems and has held technical roles in media, publishing and ad tech in New York City. Edmund is passionate about front end coding, development workflow and architecting scalable WordPress solutions for enterprise. Edmund has recently is a certified AWS Solutions Architect – Associate. In his free time, Edmund enjoys electronic music production, snowboarding, cycling and discovering new adventures across the globe with his family & two kids. Follow him on Twitter @spicecadet.
The post Using the Command Line for Automation – Part II appeared first on Torque.
