It’s common apply for builders to paintings on a number of servers or tiers with the intention to take a look at their paintings and ensure code is able to deploy with out introducing mistakes on a reside manufacturing atmosphere. WP Engine options a couple of environments in line with WordPress set up and gives a multi-tiered workflow. It’s conceivable for builders to paintings on a separate building website online till code is able to be shared with a shopper. The completed code can then be driven to staging for buyer approval. As soon as the buyer indicators off, it’s conceivable to push the code reside within the WP Engine Consumer Portal or for the command line the use of Git.

This setup supplies quite a lot of flexibility and there’s a get advantages in having all 3 tiers operating within the actual atmosphere with the intention to do away with any problems from model compatibility or configuration variations. As code is examined on the very same stack, there is not any reason it’s going to paintings on one tier and no longer the opposite.

There may be one drawback even though. Builders will ceaselessly paintings in an area atmosphere as a part of their day-to-day workflow which occurs out of doors of the WP Engine atmosphere. The issue lies in the truth that with the intention to sync knowledge between the 2 environments a database export/import is needed together with another minor transformations.

In my last post, I checked out how WP-CLI Aliases will also be configured with the intention to use SSH to get admission to native and far flung servers. On this put up, I’m going to crack the harsh nut of learn how to sync an area database to a far flung website online the use of the command line and WP-CLI. On this case, the far flung website online is my manufacturing set up of WordPress operating on WP Engine and my native set up is a duplicate of the website online that I exploit for building operating  VVV on my MacBook Professional. OK, Let’s dive into the main points.

WP-CLI Database Instructions

Probably the most purposes of WP-CLI referred to as wp db permits database control and management. It’s conceivable to create a brand new database, drop tables, restore a database or even run queries which were saved as a report. There are lots of different operations to be had as you’ll be able to see within the WP-CLI documentation

The serve as wp db takes further instructions with the intention to specify which operations to accomplish. An ordinary command looks as if this: wp db optimize. On this case, we’re asking WP-CLI to optimize the database. WP-CLI instructions are relatively simple to bear in mind as a result of they all the time get started with wp then there’s a serve as like db after which instructions with arguments will practice.

For our instance, I’ll use wp db export and wp db import with the intention to seize the database from my manufacturing server after which import it to my native VVV vagrant field. With a purpose to goal a particular far flung example of WordPress I’ve arrange Aliases in VVV that can permit me to easily goal a far flung or native WordPress example.

Exporting the Database

The whole command that I exploit to export the database from my manufacturing server is underneath.

wp @prod db export - > prod.sql

wp @prod specifies that I need to run this WP-CLI command on my Manufacturing server which is configured as an Alias. The real command being run is wp @prod db export which can export the database to an area report at the manufacturing server and title it within the layout of {dbname}-{Y-m-d}-{random-hash}.sql as a default possibility. My script would require the report be named particularly and downloaded to my native device which is why there are further choices. The - possibility permits the report to be output to straightforward out in my terminal. This ends up in the SQL sell off being published to the display screen and ends up in a protracted path of SQL statements. The overall possibility > permits the output to be saved in a report as an alternative of published to the display screen, which I’ve named prod.sql.

Getting the reside Web page URL

To ensure that the database to be imported correctly, we’ll want to exchange the choice for the website online URL saved within the wp_options desk. This modification permits our hyperlinks to indicate to the dev website online in our building atmosphere somewhat than our manufacturing website online. We will do that briefly with WP-CLI by way of operating the wp possibility get command. As now we have an alias arrange for manufacturing we will be able to use this to far flung into our manufacturing website online and get the price for ‘siteurl’. The whole command looks as if this: wp @ prod possibility get siteurl(). Within the spirit of automation, it is smart to avoid wasting the price in order that we will be able to use this to replace our building siteurl. We will do that by way of storing the siteurl price in a variable. The whole command looks as if this: prod_siteurl=$(wp @prod possibility get siteurl);. We will then get the price for our building siteurl with the intention to exchange the siteurl within the database export to paintings at the building website online. This all comes in combination to appear to be the next instance.

Get the siteurl from manufacturing and building

prod_siteurl=$(wp @prod possibility get siteurl);

dev_siteurl=$(wp @dev possibility get siteurl);

Seek and Substitute

We’ve saved the values for the manufacturing and building website online URLs with the intention to run a seek and update operation in our exported database to replace the siteurl price. This will also be accomplished by way of operating the command wp search-replace prod_siteurl dev_siteurl. Since we’ve stored the siteurl values as variable, we will be able to use the ones in our command and execute it on our building server.

Seek and update siteurl price on building

wp @dev search-replace $prod_siteurl $dev_siteurl

We’ve were given somewhat little bit of housecleaning to do on the subject of the exported sql report. As I’ve up to date the database there’s no want to save and it may be got rid of.

Take away .sql report

rm prod.sql

The Ultimate Script

The textual content underneath will also be stored as in your native device and achieved if you exchange permissions to permit the script to be achieved. You’ll be able to upload execute permissions to the script the use of the chmod command at the command line.

Execute Permission with chmod command

chmod +x

All In combination Now

The script can now be run by way of typing the next within the listing the place the script has been stored.

Execute the Script


The Whole bash script


This script gets a manufacturing database and import into an area WordPress set up. Aliases @prod and @dev are used to simplify the instructions.

Get siteurl from prod and dev

prod_siteurl=$(wp @prod possibility get siteurl);

dev_siteurl=$(wp @dev possibility 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

Replace siteurl

wp @dev search-replace $prod_siteurl $dev_siteurl

#Take away .sql report

rm prod.sql

You’ll be able to additionally view this code as a View the code on Gist.(‘http://add-github-url’) on github.


OK, so that could be a lot to soak up, however actually, we’ve utterly computerized a far flung connection to our manufacturing device and downloaded a mysql sell off of our WordPress database to our native device. We’ve then run a seek and update operation with the intention to replace the siteurl price and make allowance for hyperlinks to paintings correctly throughout the building website online. That’s beautiful tough and will also be achieved from a couple of strains of code on every occasion wanted by way of saving the instructions to a script and permitting it to be executable. The outcome is a reusable database export/import that may be run with a easy command.

That’s curious about now, and I’m hoping that is useful. Stay a watch out for extra of my posts within the close to long run.

Edmund Turbin

Edmund Turbin

Edmund Turbin is Senior Gross sales Engineer at WP Engine, London. Over the last ten years he has constructed internet sites with content material control methods and has held technical roles in media, publishing and advert tech in New York Town. Edmund is entrance finish coding, building workflow and architecting scalable WordPress answers for undertaking. Edmund has lately is an authorized AWS Answers Architect – Affiliate. In his unfastened time, Edmund enjoys digital song manufacturing, skiing, biking and finding new adventures around the globe together with his circle of relatives & two youngsters. Practice him on Twitter @spicecadet.

The put up Using the Command Line for Automation – Part II gave the impression first on Torque.

WordPress Agency

[ continue ]