How to backup your remote WordPress database to your local computer using wp-cli

I want to share in this post about what I have learned about using wp-cli to remotely manage my pair Networks WordPress site from my Mac computer. As prerequisites, you need to first set up SSH Key Authentication and have wp-cli installed on your remote server. Please refer to how I installed WordPress on pair Networks using wp-cli for more information about how to set up wp-cli. This post should be helpful for anyone that uses a Linux-like web server with SSH access. As usual, my posts are macOS centric, but it is also possible to use wp-cli on Windows 10; you can find more info here.

There’s a lot that you can do with wp-cli. In this post, I want to show you how you can export your remote WordPress database and copy it to your local (Mac) computer using wp-cli and scp. There is a lot of wp-cli info to be found on the Net. Hopefully, I can give you enough information to help you get started. The first step you need to do is install the wp (wp-cli) command on your Mac. For me, the easiest way to do the install and keep it up to date is to use Homebrew. If you haven’t already, please install Homebrew. You will find it very useful for adding command-like software to your Mac. After Homebrew is installed, you can install wp-cli with the following command:

george@imac1: /Users/george
==> brew install wp-cli

You can then verify your wp-cli by doing:

george@imac1: /Users/George
==> which wp
/usr/local/bin/wp

george@imac1: /Users/george
==> wp --info
OS:	Darwin 18.7.0 Darwin Kernel Version 18.7.0: Thu Jun 20 18:42:21 PDT 2019; root:xnu-4903.270.47~4/RELEASE_X86_64 x86_64
Shell:	/bin/bash
PHP binary:	/usr/local/Cellar/php@7.1/7.1.30_1/bin/php
PHP version:	7.1.30
php.ini used:	/usr/local/etc/php/7.1/php.ini
WP-CLI root dir:	phar://wp-cli.phar/vendor/wp-cli/wp-cli
WP-CLI vendor dir:	phar://wp-cli.phar/vendor
WP_CLI phar path:	/Users/george
WP-CLI packages dir:	
WP-CLI global config:	/Users/george/.wp-cli/config.yml
WP-CLI project config:	
WP-CLI version:	2.2.0

That was easy! If you look closely at the output from the wp info command, your entry for “WP-CLI global config” is probably empty. You need to create the config.yml file. This file stores global parameters that are used by wp-cli. This is what an example config.yml file looks like:

george@imac1: /Users/George
==> cat ~/.wp-cli/config.yml
@expcom:
  ssh: user1
  path: /usr/home/user1/public_html/example.com

@exptst:
  path: /Users/George/Sites/example.tst

“expcom” and “exptst” are aliases that point to remote and local WordPress installations. Note that the remote site requires SSH to access it. “user1” is shortcut name for an example pair Networks host; the SSH config file looks like ~/.ssh/config:

george@imac1: /Users/george
==> cat ~/.ssh/config
Host *
  AddKeysToAgent yes
  UseKeychain yes
  AddressFamily inet

Host user1.pairserver.com user1
  HostName user1.pairserver.com
  User user1
  IdentityFile ~/.ssh/user1_ed25519

To log into the server, do:

george@imac1: /Users/george
==> ssh user1
Last login: Fri Jul 26 15:15:19 2019 from x.x.x.x
Welcome to pair Networks, Inc. ...
 o o o
user1@www2: /usr/home/user1
==>

This needs to be working before the above “expcom” wp-cli alias will work. Hope that’s all clear. To test that you can remotely execute wp on your remote server, try the following using the remote alias that you setup in your local wp config file:

george@imac1: /Users/george
==> wp @expcom --info
OS:	Linux 4.15.0-52-generic #56-Ubuntu SMP Tue Jun 4 22:49:08 UTC 2019 x86_64
Shell:	/bin/bash
PHP binary:	/usr/local/bin/php71
PHP version:	7.1.28
php.ini used:	/usr/local/etc/php71/php.ini
WP-CLI root dir:	phar://wp-cli.phar/vendor/wp-cli/wp-cli
WP-CLI vendor dir:	phar://wp-cli.phar/vendor
WP_CLI phar path:	/usr/home/nimbus1
WP-CLI packages dir:	/usr/home/nimbus1/.wp-cli/packages/
WP-CLI global config:	/usr/home/nimbus1/.wp-cli/config.yml
WP-CLI project config:	
WP-CLI version:	2.2.0

The info output should be just like what you would see if you had logged into your remote server and executed wp. Just like you did for the local version of wp, you need to create a global config.yml file on your remote host. It should look like:

user1@www2: /usr/home/user1
==> cat ~/.wp-cli/config.yml
@expcom:
 path: /usr/home/user1/public_html/example.com

It simply defines an Alias that points to an example WordPress installation.

Now you are ready to dump a copy of your WordPress database and copy it to your local Mac computer. Here’s an example of how to do this:

george@imac1: /Users/George
==> wp @expcom db export /usr/home/user1/wp_db.sql
Success: Exported to '/usr/home/user1/wp_db.sql'.

george@imac1: /Users/George
==> scp user1:~/wp_db.sql .
wp_db.sql 100% 967KB 6.8MB/s 00:00

Of course, you can decide where to export your database and where you want to copy it to on your local computer. You could add all of this to a shell script that you can put in your local bin directory to automate making a backup of your remote WordPress database.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.