Database syncing

trellis
1

What opinions are there for database syncing?

The obvious one is wp db migrate pro. Is there any others I would like to work in the command line since I’m already there.

Seems like the only thing missing.

Thanks

2

Trellis uses wp cli and you can export the database with wp db export command. Works same with import after you deploy.

3

So I would vagrant ssh in order to export the database? Is there a guide that you know of to show examples of steps involved?

Thank you!

4

https://wp-cli.org/commands/db/

You can also use the forum’s search feature to read threads that have already addressed this :thumbsup:

5

And Migrate DB pro also has WP Cli Addon in case you’d prefer that, here’s a nice video showing it - https://deliciousbrains.com/wp-migrate-db-pro/doc/cli-addon/

1 Like
6

I’ve written up how you can use WP-CLI and SCP/rsync to keep production and staging in sync here:

5 Likes
7

Thank you all for the information. Couple options to consider and best workflow I’ll have to consider.

8

WP Migrate DB is better but I’ve made this based on roots/bedrock-capistrano and lavmeiker/capistrano-wpcli. Take a look and tell me what you think. :smile:

1 Like
9
10

Related tool:

1 Like
11

I use the following, with WP-CLI aliases (see above):

# export production alias to local
wp db export \ # backup the local database
&& wp db reset \ # optional - create a blank slate
&& wp @production db export - | wp db import - \ # export the production database and pipe it into our local database
&& wp search-replace '//production-url.com' '//local-url.test' --skip-columns=guid --report-changed-only \
&& wp cache flush

# export local db to staging alias
wp @staging db export \ # backup the staging database
&& wp @staging db reset \ # optional - create a blank slate on staging
&& wp db export - | wp @staging db import - \ # export the local database and pipe it into our staging database
&& wp @staging search-replace '//local-url.test' '//staging-url.com' --skip-columns=guid --report-changed-only \
&& wp @staging cache flush

Use inside vagrant ssh if using trellis, or create a @dev alias for vagrant

I have these commands set up in composer scripts. I use rsync for the uploads directory.

I’d like to incorporate this into Trellis but ansible scares me.

7 Likes
12

Currently using this method to move local export of db tables to a staging site. How did you get around the permission issues with scp? I keep getting a permission denied error when attempting to scp into /srv/www/mydomain.com/current. From my research, it looks like this expected behavior and that I should drop it into Home and then – I guess – move to it current.

13

Since this topic has been revived I’m gonna go ahead and plug this:

3 Likes
14

Does this make it easy to back up a db during sync?

15

No, you will manually have to backup files/database. It’s just a tool to synchronize between production/staging/dev. Personally, I find it the best available strictly based on cost.

16

Any plans on rewriting the sync script as a WP-CLI or Acorn package?
would be easier to install and maintain that in composer :heart:

17

For anyone looking, trellis-github-deployment can sync assets and database between production and staging, and can with a little modification work the other way, too.

Support of GitHub “releases”, too, might meet some needs here. From the README (emphasis mine):

When a deploy to production is completed, the following occurs:

  • A new release is created with the current date and time including site’s database and uploads attached as artifacts (GitHub release size restrictions apply).
  • A GitHub Deployment is created with a link to the environment.

https://discourse.roots.io/t/trellis-deployment-with-github-actions/

1 Like