Capistrano SQL backup and sync

[roots]

Capistrano SQL backup and sync

[swalkinshaw]

Glad to hear you're liking Bedrock :)

1. I doubt we'd ever have this feature included by default. I understand the use case but at first thought it seems like it would be best in an extension.
2. This is something that would probably also be an extension (separate Gem) but maybe included by default.

I think this would actually be a fairly simple wrapper using WP-CLI. No point in duplicating a tool that already does it well. It has import and export (http://wp-cli.org/commands/db/) and search-replace too (http://wp-cli.org/commands/search-replace/).

[heyfletch]

In addition to wp-cli for sql tasks, I've come across syncdb. Looks promising.

[treb0r]

IMHO WP Migrate Pro is the only way to fly. It handles find and replace, remembers profiles and also syncs media files. I have been using it in tandem with bedrock and now I realise that I want to handle the database sync separately from Capistrano just to have flexibility.

https://deliciousbrains.com/wp-migrate-db-pro/

[leotulipan]

Wouldn't using a plugin like Migrate Pro violate one of the 12 factor rules (namely "4 backing services" http://12factor.net/backing-services) ?

IMO using Capistrano + WP-CLI (and mabye syncdb) for snapshoting, updating and synching is the cleaner way.

[treb0r]

It may possibly be 'cleaner' in some sense but WP Migrate Pro works well, is here now, requires zero configuration and is a joy to use.

I find that separating the db sync from the rest of the deploy brings benefits in added flexibility. And having the media files synced with the DB saves a lot of time.

[lavmeiker]

Hi guys! Here's one approach to the problem solution. This rubygem that will add db pull and push using WP-CLI. https://github.com/lavmeiker/capistrano-wpcli

This is my first public release so I'd appreciate some feedback on this one! 😃

Hope you find it useful.

[swalkinshaw]

Just closing this as it's discussion. Can be carried on here http://discourse.roots.io/.

[tommymarshall]

In case anyone else was looking at this, I added db backups when pushing/puling a DB. Added to the end of config/deploy.rb:

time             = Time.new
backup_timestamp = time.strftime("%Y%m%d%H%M")

set :db_backup_file_name,        -> {"wpcli_db_backup.#{backup_timestamp}.sql.gz"}
set :local_db_folder_path,       -> {"./db_backups"}
set :local_db_folder_file_path,  -> {"./db_backups/#{fetch(:db_backup_file_name)}"}
set :remote_db_folder_path,      -> {"#{fetch(:deploy_to)}/db_backups"}
set :remote_db_folder_file_path, -> {"#{fetch(:deploy_to)}/db_backups/#{fetch(:db_backup_file_name)}"}

before 'wpcli:db:pull', :backup_local_db do
  run_locally do
    execute :mkdir, "-p", fetch(:local_db_folder_path)
    execute :wp, :db, :export, "- |", :gzip, ">", fetch(:local_db_folder_file_path)
  end
end

before 'wpcli:db:push', :backup_remote_db do
  on roles(:web) do
    within release_path do
      execute :mkdir, "-p", fetch(:remote_db_folder_path)
      execute :wp, :db, :export, "- |", :gzip, ">", fetch(:remote_db_folder_file_path)
    end
  end
end

And then just .gitignore the db_backups directory.