Roots Discourse

How I sync wordpress databases between development, staging, and production

This is a topic I’ve seen discussed a few times, but nothing quite ever fit my needs or I simply couldn’t get it to work (I’m new to this!). A lot of people seem to be using capistrano-wpcli or WP Migrate DB Pro. However, I couldn’t get Capistrano-WPCLI to work and I didn’t feel like paying for WP Migrate.

Enter this topic. The discussion here gave me the idea and the necessary commands to write a simple shell script that I could use to automate syncing databases between environments as needed.

For example, the script below syncs the development database to the staging website. *this assumes you’re using Trellis and Bedrock

cd ~/path/to/site/example.com/trellis
vagrant ssh <<EOF
 	cd /srv/www/example.com/current
 	wp db export
 	exit
 EOF
 cd ~/path/to/site/example.com/site
 scp example_com_development.sql web@staging.example.com:/srv/www/example.com/current
 rsync -azP web/app/uploads/ web@staging.example.com:/srv/www/example.com/shared/uploads/
 ssh -t -t web@staging.example.com <<EOF
 	cd /srv/www/example.com/current
 	wp db import example_com_development.sql
 	wp search-replace example.dev staging.example.com
 	rm example_com_development.sql
 	exit
 EOF

Now I’m sure there are definitely ways that this can be improved and extended! I’m pretty new to this and even this simpler script took me a while and many different articles to piece together.

One drawback is that this will rewrite the wordpress users for the staging/production websites, which means you need to make sure your development vault.yml is encrypted if you’re on a public git repo. I’m sure it’d also be wise to extend the the script so that it creates a backup of the database of whichever environment you’re syncing to.

Eventually I’d like to combine it with @Twansparant’s Trellis DB Sync so that I could also keep databases synced between computers.

I hope this may be of use to someone! Slash I’d definitely welcome any feedback anyone has. This is a simple approach but it does the trick :slight_smile:

2 Likes

For those who end up here in the future, I’ve been using this tool to sync uploads and databases between development, staging, and production! It works great, is actively maintained, and is easy to drop in to any trellis installation.

https://github.com/valentinocossar/trellis-database-uploads-migration

2 Likes