Using WP-CLI for database synchronisation

There are a great many tools that duplicate sites and synchronise databases between sites. Some favourites are Duplicator, WP Migrate and WP Migrate Pro, as well as good old phpMyAdmin. However, sometimes all that is needed is to import the database and then do a search and replace. This is easier said than done however as WordPress uses serialised data, meaning the url isn’t a simple string, rather an array represented in the database like so:

a:1:{s:3:"url";s:18:"http://example.com";}

This is the sort of use case where WP-CLI shines. One command in particular, wp search-replace, can be a great timesaver. Here are some quick instructions for using wp-cli to perform this oft-required task.

Please note that some of these commands are destructive in nature so make sure you have backups or are at least comfortable with the idea of losing your local database before proceeding. I am assuming that you also have a basic knowledge of wp-cli and have it installed. If not, this article might be helpful. I also have a cheatsheet of useful wp-cli commands for setting up a WordPress site.

First export the database on the live site. I usually just use phpMyAdmin here but you can also use wp db export which will export the file as {dbname}.sql. There are also several arguments you can use to modify the way in which the file is output. If you want to go all-command-line it may be possible using wp db import scp your_username@remotehost.com:path/to/file/dbname.sql /local/dir but that will depend on your server configuration and I find it faster to download it using (s)ftp.

The new database then needs to replace the old. The easiest way to import the database is to add the downloaded database with the name {dbname}.sql to the site root. Delete the old database using wp db reset. Then use wp db import to import the new database.

Next we use our wp search-replace super-command to synchronise the database in the blink of an eye. For this use

wp search-replace --url=mysite.com mysite.com mysite.local

If the online site uses SSL (https), don’t forget to do

wp search-replace 'https://mysite.com' 'http://mysite.com'

You should then have a synchronised version of the live site on your local server. Please let me know in the comments if any clarifications can be made to make this article more understandable.