Add database piping tool

[wmcmillian-coalmarch]

Right now we can download databases via the 'terminus site backup get' command and we can create the latest via 'terminus site backup create.'

The problem with this method is twofold
1.) Even when specifying just the database element, it creates backups for the database, code, and files (and waits until all are complete before succeeding).

2.) This also adds a backup to the backup queue in the pantheon dashboard which can clutter up the queue.

Maybe a command similar to sql-sync-pipe which pipes the database directly into a local database?

[wmcmillian-coalmarch]

Maybe, perhaps tying into the export functionality on the dashboard

[joshkoenig]

This has gotten a lot of +1's from the Power Users list.

[joshkoenig]

What we probably need is something like:

terminus site sql-dump --site=foo --env=live

You could then pipe that to a local file:

terminus site sql-dump --site=foo --env=live > dumpfile.sql

Or even directly to your locally running instance of mysql:

terminus site sql-dump --site=foo --env=live | mysql -h 127.0.0.1 -u root local_db_name

[bensheldon]

@joshkoenig I think we should have a discussion about to what extent terminus is a wrapper around the Platform (mostly REST) API, and to what extent Terminus is implementing new behaviors and plugging into local-machine resources (git, mysql). We have another issue asking for SQL connection info (#184) that I think would be a better starting place. Or, to address this issue, building fine-grained backups into the platform.

[alphex]

Just my +1 for either (OR BOTH)

terminus site sql-dump --site=foo --env=live > dumpfile.sql

terminus site sql-dump --site=foo --env=live | mysql -h 127.0.0.1 -u root local_db_name

[asentner]

+1 for me. This would be great enhancement! Hope to see this feature added

[eslynunez]

+1 pls pantheon pls.

[dalin-]

+1 for this.

Pantheon really needs a quick and easy way to pull down a copy of the database. None of the available options are really as slick as they could be:

• sql-sync-pipe
  • This would almost be awesome were it not for terminus sites aliases should also include organizations' sites #611 which means I can't just add a contractor to my organization and tell them to run sql-sync-pipe. I need to explicitly add them to every site, as if the organization feature didn't exist.
• terminus sites backup
  • Requires several steps (make the backup, download it, gunzip piped into mysql, sanitize)
  • Even if you script all that it's still not as fast as sql-sync-pipe (can't set some tables to structure only)

[westonruter]

For WordPress, this should be available via WP-CLI since you can export the DB to stdout. However, it seems db is not allowed:

$ terminus wp 'db export -' --site=example --env=dev
[2016-02-17 01:25:04] [error] db is not available via Terminus. Please run it via WP-CLI.

This error message doesn't exactly make sense either 😄

[defaria]

Plus 1 for allowing db wp-cli commands on terminus.

[mattking5000]

Any updates on this? would love to be able to use the WP_CLI db commands, if not all at least some.

[derimagia]

I have a plugin I made to do this for Drupal Sites: https://github.com/derimagia/terminus-pipe - my goal was/is to make it as fast as possible.

Sadly I haven't had too much time so I haven't "released" the plugin - right now it only supports Drupal (needs drupal aliases.. although it should work/be able to be configured to work with wordpress sites if an alias is set up...). Secondly it only works on Mac/Linux right now. I eventually want to have it do the same thing with files once I have more time to work on it.

[philipjohn]

Previously it was suggested that you could simply pipe to a local file:

$ terminus wp db export - --site=cals-blog > localfile.sql

However, that now doesn't appear to work (if it ever did):

terminus remote:wp foobar.live -- db export --tables=wp_options > options.sql
mysqldump: Can't create/write to file 'pantheon-2022-11-22-e8c93da.sql' (OS errno 13 - Permission denied)
[notice] Command: foobar.live -- wp db export [Exit: 1]
[error]

[bensheldon]

@philipjohn just to quickly unblock you, I think the pipe character in that script is executed on the remote rather than piping the output from the command to a local file. I think you want something like this to execute the command, and then pipe the output locally (sorry, my bash is terrible):

$(terminus wp db export -- --site=cals-blog) > localfile.sql

[philipjohn]

Thanks @bensheldon ! You put me on the right track, here's what worked:

terminus remote:wp foobar.live -- db export --tables=wp_options - > options.sql

That - is small but very significant!