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

I want to share in this Note 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 note should be helpful for anyone that uses a Linux-like web server with SSH access. As usual, my Notes 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 Note, 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.2.0 Darwin Kernel Version 18.2.0: Thu Dec 20 20:46:53 PST 2018; root:xnu-4903.241.1~1/RELEASE_X86_64 x86_64
Shell: /bin/bash
PHP binary: /usr/local/Cellar/php@7.2/7.2.14/bin/php
PHP version: 7.2.14
php.ini used: /usr/local/etc/php/7.2/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.1.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 my config.yml file looks like:

george@imac1: /Users/George
==> cat ~/.wp-cli/config.yml
@apcom:
  ssh: nimbus1
  path: /usr/home/nimbus1/public_html/altoplace.com

@aptst:
  path: /Users/George/Documents/Sites/altoplace.tst

“apcom” and “apstst” are aliases that point to my remote and local WordPress installations. The Alias includes the path to the WordPress install. Note that my remote site requires SSH to access it. “nimbus1” is shortcut name for my pair Networks host that I set up in my SSH config file at ~/.ssh/config:

Host nimbus1.pairserver.com nimbus1
 HostName nimbus1.pairserver.com
 User ...
 IdentityFile ~/.ssh/...

To log into my server, all I have to do is:

george@imac1: /Users/George
==> ssh nimbus1
Last login: Fri Feb 1 11:00:30 2019 from 72.34.9.149
Welcome to pair Networks, Inc. ...

This needs to be working before the above “apcom” 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 @apcom --info
OS: Linux 4.15.0-39-generic #42-Ubuntu SMP Tue Oct 23 15:48:01 UTC 2018 x86_64
Shell: /bin/bash
PHP binary: /usr/local/bin/php72
PHP version: 7.2.13
php.ini used: /usr/local/etc/php72/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: 
WP-CLI global config: /usr/home/nimbus1/.wp-cli/config.yml
WP-CLI project config: 
WP-CLI version: 2.1.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. Mine looks like:

nimbus1@www2: /usr/home/nimbus1
==> cat ~/.wp-cli/config.yml
@apcom:
 path: /usr/home/nimbus1/public_html/altoplace.com

It simply defines an Alias that points to my 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 @apcom db export /usr/home/nimbus1/wp_db.sql
Success: Exported to '/usr/home/nimbus1/wp_db.sql'.

george@imac1: /Users/George
==> scp nimbus1:~/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.

Using wp-cli and rsync, I can make a copy of my remote WordPress site on my Mac. I can also copy my local WordPress site to my remote server. Or in other words, I can keep my local WordPress site synced with my remote site on the Internet. This allows me to work on my local site, such as adding content, and then update my remote site with the changes that I have made and tested. All of this is subject for a future post that I hope to write.

Leave a Comment

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