Syncing the database between environments with Bedrock-Ansible

bedrock
1

Hi,

I know there have been several topics about syncing database between environments. However, I’ve not come across a solution to use Ansible for this task.

I think it would be really convenient for somebody who deploys the code with bedrock-ansible to also have a task set up for pulling and pushing the databse between local and remote environments.

So my question is has anyone managed to use Ansible with success when it comes to syncing database with Ansible? Perhaps you can share the definition of your db-syncing task?

Thanks!

1 Like
2

What about using some of the existing places in deploys to run arbitrary commands?

There’s:

  • project_pre_build_commands_local
  • project_pre_build_commands
  • project_post_build_commands
  • project_post_finalize_commands

You could run any WP-CLI commands from these and potentially copy files.

3

A good point, indeed. These would be the hooks to use. I guess that WP Cli export, import tasks could be run there to synchronize databases.

4

But wordpress DB has a lot of URL-specific records. Simply replacing strings won’t work. Would wp db export properly handle that?

5

We ended up forking this repo https://github.com/lavmeiker/capistrano-wpcli to fit into our workflow. I can’t remember if out of the box it works with bedrock-ansible. It will use wp search-replace to make sure serialized strings and urls are changed according to the environment. We also added a wp export to our production deploy tasks. This adds a db dump to the current release so that file structure and db is frozen in the event we need to roll back. Haven’t had to use it but it’s a good piece of mind.

6

diggs - can you explain the steps and locations you used.

this is a huge part of the disconnect and breakdown in using bedrock-ansible.

normally, i’d just manually use sequel pro to import/export but i haven’t been able to successfully connect or find any instructions to do so.

7

scott - can you provide instructions to use sequel pro to connect to mariadb in the bedrock-ansible (trellis) setup?

8

See this from @ben: https://github.com/roots/bedrock-ansible/pull/231#issuecomment-107974860

1 Like
9

Scott - Problem solved! The fast-support and industry-best practices that you guys use is what draws me - keep up the excellent work! (And let me know when you introduce or start working on a docker version of bedrock :wink:

10

Scott - On to staging…I am trying to connect to the staging server’s mariadb using Sequel Pro so I can import the database (after using SearchReplaceDB to replace dev URL to staging URL in the database of course.)

I am trying using SSH, but can’t seem to connect:

  • MySQL Host: (Staging IP Address)
  • Username: (db_user)
  • Password: (db_password)
  • SSH Host: (staging.XXX.com)
  • SSH User: root
  • SSH Key: (my id_rsa private key)

I know I am missing something easy… Suggestions?

11

Well I’d verify that each of those work independently first:

  1. Make sure you can SSH into the server with those SSH credentials (I assume you can).
  2. Make sure you can connect to MySQL with those credentials when already manually SSHd into the server.
12

Scott - I am novice at command line. That’s the reason I prefer to use the GUI for this type of task. I understand your instructions, but I don’t understand how to implement them.

Can you verify:

  • SSH Host: Should I use hostname or IP for the staging server?
  • SSH User: Should this be blank or root or something else? We used vagrant on the dev, and digital ocean uses SSH keys so I don’t know what to use for this?
  • SSH Key: Public or Private?

Sorry for the hassle :frowning:

13

Host: either? shouldn’t matter.
User: well it can’t be blank. root is the default so probably that.
Key: you shouldn’t need to specify this. Sequel Pro will just use your default (~/.ssh/id_rsa)

1 Like
14

Worked! Thank you once again!