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*