Pushing local database to the remote with wp_cli

bedrock
1

Hi there!

I’ve only started with bedrock, but I already love it!

I just wanted to ask for your advice regarding database synchronization with wp_cli and bedrock.

I’ve decided to use standard wp_cli commands to do the job. My workflow looks something like this:

  1. ‘wp db export’ and gzip the local database,
  2. ‘scp’ the gzipped sql file into my remote server,
  3. ‘ssh’ into the remote,
  4. uncompress the gzipped sql file and ‘wp db import’ it to the wordpress install on the remote.

I would like to get rid of step no. 2 - (scp’ing the sql file to the remote).

In what directory on my Vagrant VM could I put the gzipped local .sql file so that it gets transferred to the remote with bundle exec cap production deploy ?

Also, I don’t want the dumped .sql file to end up in a publicly accessible folder /web/ Is this possible to achieve with bedrock?

Thank you!

2

I use GitHub - lavmeiker/capistrano-wpcli: Provides command line tools to facilitate Wordpress deploy. with https://github.com/roots/bedrock and it works great. You can do cap staging wpcli:db:push and cap staging wpcli:uploads:rsync:push easily enough.

To answer your original question:

By default you would have to check it into your git repository if you want it to be deployed with capistrano. I do this all the time. I create a “db” directory in the project root and dumb dbs in there from time to time. One of the advantages of bedrock is that the “web” directory is “hot” and everything outside of that is not.

You could also have cap upload the entire db directory on each deploy. But that might get slow with large uncompressed dbs.

3 Likes
3

I’m currently experimenting with capistrano-wpcli. It looks to be a very convenient tool to manage db and media syncing. Also, thanks for the suggestion to check database files into the repository. This will probably be a good way to always have a backup at hand.

4

Yep, it handles rewriting urls too. So if you dev at “example.dev” it will rewrite all references to “example.dev” to “example.com” in the production db.

6

Dumb question, but how do I integrate the cap wpcli:db:push into the bundle exec cap production deploy process? I installed the capistrano-wpcli and then required it in the Capfile, but not sure what to do after that?

7

I dont have read the whole thread but do you know the Wordmove script?

Maybe it can help you.

1 Like
8

@jasonhjohnson typing

cap production wpcli:db:push # push local db to production
# or
cap production wpcli:db:pull # pull db from production to local

in your command line in the Bedrock project folder is good enough.

Also instead of using cap production you can use stage as well (you need to have stage configured).

9

@marlonlamancio wordmove seems to me to not have any additional functionality over capistrano-wpcli, which supports Bedrock environment configurations out of the box.

10

Is this still the best way to go? I’m looking for a good way to promote up database changes using the latest code on the roots-example-project repo. It looks like this doesn’t use capistrano anymore for publishing so I’m not sure if I should use capistrano-wpcli for the DB pushes?

Thanks,
J

11

I’ll also bump this thread to ask if https://github.com/welaika/wordmove might be a viable solution for database syncing, now that Ansible is the default seutp? I’ll give it a try myself, but if anyone else has experience, let us know.

12

Did you have any luck with this, @evanfuture?

13

I use WP Migrate DB pro and it works great.

Has the added benefit of migrating media files too.

14

I haven’t tried yet @poggen, but I’ll see about it in the morning. Finger’s crossed as I prefer an open source solution (or at least a free one!)

15

I understand that people want ‘free as in beer’ where possible but given that I make my living from building websites I have no qualms about paying the support license. WP Migrate DB pro works beautifully and is worth far more than the price tag to me personally.

I also like to support projects that I use where I can. Including this one!

3 Likes
16

Capistrano has some plugins built for it that use WP-CLI, there have been links to them on these forums. I would say that Capistrano should be the way to go if you want database integration to your deploys.

However, I also use WP Migrate DB Pro like treb0r, I also don’t mind paying for it, as it simplifies this process pretty well. Not just the database, but all media uploads as well.

I also like to keep deploying the database separate from deploying code. Often times someone else will be adding content on the staging site, and that becomes like the “content source”, which I will sometimes pull down or else I’ll just work on code locally and only deploy the code.

2 Likes
17

@austin uses a modern developer’s workflow. Everything should be able to be scripted so that it is reproducible and environments are disposable.

@ben - is this, or has this, type of scripted-functionality for managing content (db/media) between environments been implemented into bedrock yet? If not, is this something that has been planned?

I have been working with Magento using Docker (much like Wordpress/Bedrock using Vagrant/Trellis) and my entire workflow has changed - it leaves me envious of a workflow for my Wordpress installs and I now do as little as possible with Wordpress. I feel like I felt about Wordpress before I found Bedrock and Trellis. Any plans on “Bedrock for Docker” ?

Cheers!

18

Not going to have something like this in Bedrock out-of-the-box.

There’s lots of different ways to handle this right now. Another option is wp-cli-ssh

19

Hi, someone could provide me the configuration of capistrano-wpcli with the bedrock/trellis structure?

@austin If you are still using capistrano-wpcli (or have used it with bedrock-ansible), could you provide me any guideline to integrate it into a bedrock-ansible project?

I’m experiencing some troubles with the backup function and I don’t know if the problem is in my configuration.

20

Are you guys still using this?

I’m looking for a solution to use for trellis/bedrock setup to handle database.

It’s it hard to setup?

Thanks

21

Hi Austin,

Replying to a pretty old post, but searching your history yielded no results to my eye.

Any chance you can provide detailed instructions or a place to start if I want to implement your idea to:

I use https://github.com/lavmeiker/capistrano-wpcli168 with https://github.com/roots/bedrock32 and it works great. You can do cap staging wpcli:db:push and cap staging wpcli:uploads:rsync:push easily enough.

To answer your original question:

luqo33:
In what directory on my Vagrant VM could I put the gzipped local .sql file so that it gets transferred to the remote with bundle exec cap production deploy ?
By default you would have to check it into your git repository if you want it to be deployed with capistrano. I do this all the time. I create a “db” directory in the project root and dumb dbs in there from time to time. One of the advantages of bedrock is that the “web” directory is “hot” and everything outside of that is not.

You could also have cap upload the entire db directory on each deploy. But that might get slow with large uncompressed dbs.

I have just gotten started with trellis and bedrock and I’m committed to versioning the DB. and it seems your method is one of the few examples on this site of someone actually wanting to do that. Any guidance you can provide would be amazing. I’ve looked into capistrano-wp-cli, but it doesn’t make it clear how I’d get started at integrating it in the way that you outline.

Thank You.