Keep database synced between computers in Trellis/Bedrock/Sage setup

Steve_Daly · June 6, 2016, 9:16pm

Thanks for the comment. It lead me to what I think is the problem.

I do not have an env set in my wordpress_sites.yml file. I was counting on the default settings in the all/main.yml file.

The error is gone. Now to get the triggers working.

jlkesler · August 5, 2016, 6:03pm

I have the same problem you mentioned in your previous post. Can you show me an example of your dev wordpress_sites and vault setup? Sorry a newbie so I might have configured it wrong. The triggers are added to the vagrant file at the end correct?

Ivan_Svaljek · August 20, 2016, 5:47am

There is this plugin: https://github.com/wp-sync-db/wp-sync-db

Twansparant · October 31, 2016, 10:08am

I made a public repo for this: https://github.com/Twansparant/trellis-db-sync

Bruno · December 16, 2016, 8:57am

wp-sync-db is just a rip off of migrate db pro. While legal, is not nice…

plarcher · December 24, 2016, 10:30am

If it’s licensed as an open source and working into an open source ecosystem I’m not sure the term “rip off” makes sense ?

ben · December 24, 2016, 4:01pm

How about “unethical”? Do the right thing — if you find it useful, pay for the license through Delicious Brains.

aitor · May 31, 2017, 9:47am

Delicious Brains choosed their bussiness model GPL based. It was their own choice. Has no sense to get upset if somebody fork it. Has no sense to wield “unethical” flag against GPL freedom terms. That is really “unethical”.

ben · June 3, 2017, 5:02pm

I’m sorry, but just because they’re using the GPL license doesn’t mean it’s suddenly fine to be using rip-offs of someone’s software.

How do you think we would react to people taking our paid plugins and then re-distributing them for free?

Do you think it’s okay to sign up for one of the many ‘GPL Clubs’ around, that give you access to a wide variety of WordPress plugins by paying a small fee? That fee doesn’t go to any of the people that wrote those plugins.

aitor · June 5, 2017, 10:54am

To use, modify or redistribute GPL software is not a rip-off. Rip-off implies something ilegal. If you are impeding the freedoms guaranteed by the license, that is a rip-off.

You were less wrong using “unethical” term since ethic is a grayscale zone and can be discussed.

You should keep in mind that you can write plugins for a lot of people just because the GPL ecosystem built a very big community of users. All this people adopt this ecosystem because they can feel it as their collective property. If you do not respect the terms of the GPL you are damaging that ecosystem. Wordpress is clear about it: “If you disagree, you might want to consider a non-GPL platform such as Serendipity (BSD license) or Habari (Apache license) instead.”

So, although I am not a friend of opportunism and I don’t like those GPL clubs, the dilemma is between two choices: To preserve the ecosystem or to preserve your personal profits. I understand that personal profits of software development are very important to software developers, but it can’t be defended with ethical arguments against GPL terms.

A metaphor: think in a lumberjack in the forest. He can persevere their profits against forest environment. If you, as a developer, make GPL software, you are seeding more trees in that forest. Otherwise, you are the lumberjack.

So, how can I survive as a developer in a GPL environment? This is a complex discussion and I’m afraid it exceed the topic of this forum. Anyway, it includes concepts like “real time economy”, “attention economy” and “economy of services”. I will be happy to discuss this further if you deem it appropriate.

Just to say that, in wp-sync-db case, I prefer clearly to buy a complete functional WP Migrate DB Pro plugin, and avoid to hack trellis to adapt it to wp-sync-db needs, but my personal situation is very hard, actually, without resources to cover my basic needs. So, to find free resources is mandatory until I get afloat.

So, unlist my previous thread was a bad decision:
https://discourse.roots.io/t/can-i-downgrade-composer/9703
Because this decision was completely based on a arguable opinion and, in strictly sense, it can be considered censorship. This kind of decision hurts me since internally I’m involved with the roots community (despite my low level as a front-end developer). I learned a lot and I got huge satisfaction here.

groseler · November 16, 2017, 11:54am

Hi! How can I apply this to my project? Do I have to overwrite the vagrantfile, the group_vars folder and create database in the root only or do I need to do something else?

Thanks

Twansparant · November 16, 2017, 3:08pm

I made a fresh Trellis fork for this: https://github.com/Twansparant/trellis-db-sync
Basically, all you need to do is add a database/current folder in your project root and modify these lines:

github.com

Twansparant/trellis-db-sync/blob/master/Vagrantfile#L86




  config.vm.synced_folder ANSIBLE_PATH, ANSIBLE_PATH_ON_VM, mount_options: ['dmode=755', 'fmode=644']
  config.vm.synced_folder File.join(ANSIBLE_PATH, 'bin'), bin_path, mount_options: ['dmode=755', 'fmode=755']
else
  if !Vagrant.has_plugin? 'vagrant-bindfs'
    fail_with_message "vagrant-bindfs missing, please install the plugin with this command:\nvagrant plugin install vagrant-bindfs"
  else
    trellis_config.wordpress_sites.each_pair do |name, site|
      config.vm.synced_folder local_site_path(site), nfs_path(name), type: 'nfs'
      config.bindfs.bind_folder nfs_path(name), remote_site_path(name, site), u: 'vagrant', g: 'www-data', o: 'nonempty'
      config.vm.synced_folder '../database/', '/srv/database'
    end


    config.vm.synced_folder ANSIBLE_PATH, '/ansible-nfs', type: 'nfs'
    config.bindfs.bind_folder '/ansible-nfs', ANSIBLE_PATH_ON_VM, o: 'nonempty', p: '0644,a+D'
    config.bindfs.bind_folder bin_path, bin_path, perms: '0755'
  end
end


vconfig.fetch('vagrant_synced_folders', []).each do |folder|
  options = {

github.com

Twansparant/trellis-db-sync/blob/master/Vagrantfile#L168-L197


# Vagrant Triggers
trellis_config.wordpress_sites.each do |(name, site)|
  #
  # Get database credentials
  #
  site['env'].merge!(trellis_config.vault_sites[name]['env'])
  db_name  = site['env']['db_name']
  db_user  = site['env']['db_user']
  db_pass  = site['env']['db_password']
  #
  # Importing database
  #
  config.trigger.after [:up, :resume], :force => true do
    database_file = "../database/current/#{db_name}.sql"
    if File.exists?(database_file)
      info "Importing database #{db_name}"
      run_remote "mysql -u #{db_user} -p#{db_pass} #{db_name} < /srv/database/current/#{db_name}.sql"
    else
      fail_with_message "#{database_file} was not found."
    end

This file has been truncated. show original

github.com

Twansparant/trellis-db-sync/blob/master/vagrant.default.yml#L14


vagrant_memory: 1024 # in MB
vagrant_box: 'bento/ubuntu-16.04'
vagrant_box_version: '<= 201710.25.0'
vagrant_ansible_version: '2.4.1.0'
vagrant_skip_galaxy: false


vagrant_install_plugins: true
vagrant_plugins:
- name: vagrant-bindfs
- name: vagrant-hostmanager
- name: vagrant-triggers


# Array of synced folders:
# - local_path: .
#   destination: /path/on/vm
#   create: false
#   type: nfs
#   bindfs: true
#   mount_options: []
#   bindfs_options: {}
# See https://www.vagrantup.com/docs/synced-folders/basic_usage.html#mount_options

github.com

Twansparant/trellis-db-sync/blob/master/lib/trellis/config.rb#L45-L51


def vault_sites
  @vault_sites ||= begin
    vault_content['vault_wordpress_sites'].tap do |sites|
      fail_with message: "No sites found in #{vault_path}." if sites.to_h.empty?
    end
  end
end

github.com

Twansparant/trellis-db-sync/blob/master/lib/trellis/config.rb#L60-L65


def vault_content
  @vault_content ||= begin
    fail_with message: "#{vault_path} was not found. Please check `root_path`." unless exist?
    YAML.load_file(vault_path)
  end
end

github.com

Twansparant/trellis-db-sync/blob/master/lib/trellis/config.rb#L83-L85


def vault_path
  File.join(@root_path, 'group_vars', 'development', 'vault.yml')
end

groseler · March 10, 2018, 4:14pm

Hi! I’m getting an error regarding the vagrant file.

Path: /Users/.../trellis/Vagrantfile
Line number: 174
Message: undefined method `merge!'

philipp · April 24, 2018, 5:29pm

And I’m getting:

There was an error loading a Vagrantfile. The file being loaded
and the error message are shown below. This is usually caused by
an invalid or undefined variable.

Path: /.../trellis/Vagrantfile
Line number: 174
Message: undefined method `vault_sites'

Commented out that line for the moment but not sure how this can get solved. Just merged the latest trellis commits before.

Thank you very much in advance.

Twansparant · April 24, 2018, 8:06pm

My fork is probably out-of-date… Sorry about that! When I find some time, I’ll try and fix this error!

pacotole · August 6, 2018, 9:04am

FYI. We have created trellis-simple-sync, a very simple library to synchronize database and the uploads folder between development and other environments. It don’t need any configuration.

With just one command, e.g.:

./bin/sync.sh staging example.com db push
./bin/sync.sh production example.com uploads pull

blu · August 7, 2018, 8:03am

As of version 2.1.0, Vagrant no longer needs the vagrant-triggers plugin - to get the trellis-db-sync working after updating - remove the plugin from vagrant.default.yml and replace the trigger in the Vagrantfile to:

# Vagrant Triggers
  trellis_config.wordpress_sites.each do |(name, site)|
    #
    # Get database credentials
    #
    site['env'].merge!(trellis_config.vault_sites[name]['env'])
    db_name  = site['env']['db_name']
    db_user  = site['env']['db_user']
    db_pass  = site['env']['db_password']
    #
    # Importing database
    #
    config.trigger.after [:up, :resume] do |trigger|
      database_file = "../database/current/#{db_name}.sql"
      if File.exists?(database_file)
        trigger.info = "Importing database #{db_name}"
        trigger.run_remote = {inline: "mysql -u #{db_user} -p#{db_pass} #{db_name} < /srv/database/current/#{db_name}.sql"}
      else
        fail_with_message "#{database_file} was not found."
      end
    end
    #
    # Exporting database
    #
    config.trigger.before [:halt, :suspend] do |trigger|
      trigger.info = "Dumping database #{db_name}"
      trigger.run_remote = {inline: "mysqldump -u #{db_user} -p#{db_pass} #{db_name} > /srv/database/current/#{db_name}.sql"}
    end
  end

The rest of the setup remains as before.

I had to remove the ~/.vagrant.d directory to delete vagrant-triggers plugin completely - you may want to backup before removing this directory but the user data is automatically regenerated on running vagrant.

The trigger seems to run a little slower than before but at least it works again.

philipp · August 14, 2018, 9:19am

Thank you @blu! I can confirm that works as expected.

EDIT: Updating Vagrant to version >2.0.4 can lead to some unexpected behavior such as Trellis not recognizing the installed plugins like “vagrant-hostmanager”. For reference:

henscu · August 16, 2018, 4:19pm

@pacotole, I just tested trellis-simple-sync on my site, and it works, thanks! Running your sync was quite an effective check on my Staging site setup as I had somehow managed to set it up incorrectly, but yet it was working. All good now.

I have a question:

You only run one search-replace command of the format:

"wp", "search-replace", "https://example.local", "https://staging.example.com", "--all-tables"

This works, but does not cover all of my development (example.local) URLs, as many instances exist where the URL is not “https://example.com”. It can be one of:

I have searched for info on this scenario, but all references to running search=replace only describe URL examples that contain the text “http://” or “https://”.

Do you know if this is a problem with my WordPress site, or if I just need to modify your Ansible scripts to add 2 extra calls to search-replace to cater for http://example.local and example.local?

Also, how do you recommend handling guid columns?

pacotole · August 20, 2018, 2:55pm

Thanks @henscu

In my case, I use http in development and https in staging/production so I need to change the url with protocol included.

trellis-simple-sync automatically detects if SSL is active in each environment and replace the correct url.

I’ll add an extra search-replace task protocol-relative "//example.com" to ensure all urls are replaced.

Generally, I don’t recommend to change domain without protocol "example.com" because it can be part of the web content, like legal texts or if the domain is the name of the site (e.g.: “Welcome to example.com …”) but in particular cases may be necessary. You can add your extra tasks if needed.

About GUIDs, as far as I know, they are used as a unique identifier in the XML feeds.
If you are syncing a new site between dev <-> staging/production there is no problem.
If you are migrating from an old domain to a new domain and have many subscribers, you should keep the same GUID (wp search-replace ... --skip-columns=guid) to prevent that all old posts appear as new posts on feed readers.