Synchronise remote WordPress database to local Vagrant

When working on a local copy of an existing WordPress site it’s useful to synchronise the latest content from the live server.

One way is to download the database as an SQL file, convert it for the local URL then import it into the local server – Vagrant in this instance.

After having done this process manually for a particular long-running client a couple of times I’ve finally done the obvious thing & written a bash script to automate it.

Simplifying `ssh`

I already have an alias to the remote server set up in ~/.ssh/config.

# Add an alias "example" for example.com
Host example
    HostName example.com
    User myUserName
    Port 7777
    IdentityFile ~/.ssh/id_rsa.example.com

This allows me to connect to the server with ssh example which makes the script a bit less verbose.

The steps

Export the remote database
Download the SQL file
Find & replace the remote URL with the local one
Replace the old local database with the new one

1: Export the remote database

I made a server-side script (dump-db.sh), using mysqldump to save a snapshot of the database as ~/db-dump.sql:

mysqldump -u db_username -pPASSWORD wp_databasename > ~/db-dump.sql

Running this remotely via ssh means the local script doesn’t need to know the database password:

ssh example '~/dump-db.sh'

2: Download the SQL file

scp is a quick way to transfer files over ssh:

scp example:~/db-dump.sql ~/db-dump.sql

3: Find & replace the remote URL with the local one

In order for the remote example.com database to work on my local example.test server I have to find & replace all instances of example.com with example.test. I use sed for this, with the -i (in-place) option to overwrite the existing file with the updated version:

sed -i 's/example.com/example.test/g' ~/db-dump.sql

4: Replace the old local database with the new one

Vagrant provides an import script which imports any SQL files in the /backups directory into a database with the same name as the file. First I move the updated dump file and rename it:

mv ~/db-dump.sql ~/vagrant-local/database/sql/backups/wp-db.sql

The script won’t overwrite existing databases so next I need to drop the old local database. This can be done with vagrant ssh, passing a command with the -c (command) option.

The drop command itself uses mysql’s -e (execute) option to run the SQL command DROP database wp_db. The command will be running in Vagrant’s virtual machine so it doesn’t require a username & password:

pushd ~/vagrant-local/
vagrant ssh -c 'mysql -e "DROP database wp_databasename"'

Note: To run vagrant ssh successfully in the script I had to first switch to the /vagrant-local directory. I used pushd for this so that I can popd back to the previous location after, but pushd initially failed with the error message pushd: not found. I fixed this by adding the #!/bin/bash header to the top of the script.

With the old database dropped, Vagrant’s import-sql.sh script will import the SQL dump file into a datbase named wp-db. The script is run using vagrant ssh with -c again:

vagrant ssh -c '/srv/database/import-sql.sh'
popd

That’s it!

The full script

I like to echo reassuring messages in my scripts so that future me knows what’s actually going on!

#!/bin/bash

echo
echo ------------------------------
echo Dump database on remote server
echo ------------------------------
echo

# Run database dump script on remote server
ssh example '~/db-dump.sql'

echo Done ✔

echo
echo ------------------------------
echo Download remote dump file
echo ------------------------------
echo

# Download database
scp example:~/db-dump.sql ~/db-dump.sql

echo Done ✔

echo
echo ------------------------------
echo Localise downloaded dump file
echo ------------------------------
echo

# Find & replace `example.com` with `example.test`
sed -i 's/example.com/example.test/g' ~/db-dump.sql

# Move & rename the updated sql file
mv ~/db-dump.sql ~/vagrant-local/database/sql/backups/wp-db.sql

echo Done ✔

echo
echo ------------------------------
echo Run vagrant import script
echo ------------------------------
echo 

pushd ~/vagrant-local/

# Drop old vagrant database
vagrant ssh -c 'mysql -e "DROP database wp_db"'

# Run import script
vagrant ssh -c '/srv/database/import-sql.sh'

popd

echo Done ✔

echo
echo ------------------------------
echo Synchronisation complete!
echo ------------------------------

Post-script

After finishing writing this post I ran a final test of my script.

It appeared to work perfectly but, when I tried to view the local site on Vagrant all I saw was the dreaded White Screen of Death!

I obviously attributed this to the script I had just run, but after a day of anxious troubleshooting it turned out to be a badly timed coincidence caused by something else I had changed… *sigh*

Simplifying ssh