How to sql-sync database between two remotes which share the same host?

Question

I've two remotes: @source and @destination.

I'm trying to transfer the database from dev to test environment by using sql-sync:

$ drush -vy sql-sync @source @destination

But at the end drush complains that:

The source and destination cannot both be remote.

rsync error: syntax or usage error (code 1) at main.c(1166) [Receiver=3.0.9]

Both remotes are on the same machine, but they're using different path.

I'm using latest drush 7.0-dev.

Is there any workaround?

Answer 1

Here is how drush sql-sync works between two remotes:

1. Logs into remote @destination via SSH and creates destination database if needed via sql-create.
2. Backs up database on @sourceby performing sql-dump on source unless told otherwise and saves it into drush-backups folder.

3. Logs into @destination remote via SSH and executes rsync to transfer database from Source to Destination.

   Example: rsync @source:drush-backups/db.sql.gz @destination:/tmp/db.sql.gz

To make this work, you have to make sure that:

• both local and remote drush have the same version (either 6.x or 7.x),
• on Destination the @source alias should be accessible for drush,
• rsync tool doesn't support two remotes, so only one may be remote.

Based on above it seems you can't use sql-sync drush command to transfer the database between two remotes in drush 7.x (see: #161).

As for workaround, first dump the database from your source onto your local and transfer back to the destination, in example:

drush @source sql-dump | drush @destination sql-cli

This should transfer the database between two remotes without sql-sync limitation.

Answer 2

Another option I use is to use drush ssh command to execute drush on the source :

drush @source ssh "drush sql-sync @self @destination"