drush sql-sync with remote site

I found it hard to get right because the errors are pretty opaque, but with the help of Greg Anderson in this Drupal Excahnge question and a bit of reading I figured it out.

The key is running these 2 commands on the command line of the target server:

$ drush site-alias --with-db --show-passwords --with-optional @self
$ which drush

The first will give a complete run down of the details drush has gleaned. It'll look something like this:

  $aliases["self"] = array (
     'root' => '/home/USER/public_html',
     'uri' => 'http://SITE_URL',
     'path-aliases' =>
     array (
        '%drush' => '/home/USER/.composer/vendor/drush/drush',
        '%site' => 'sites/default/',
     ),
     'databases' =>
     array (
        'default' =>
        array (
           'default' =>
           array (
              'database' => 'DB_NAME',
              'username' => 'DB_USER',
              'password' => 'DB_PASS',
              'host' => 'localhost',
              'port' => '',
              'driver' => 'mysql',
              'prefix' => '',
           ),
        ),
     ),
  );

To this you'll need to add the output of the which drush line. That goes in with path-aliases as something like:

'%drush-script' => '/home/USER/.composer/vendor/bin/drush',

NOTE 1: you may need to expand out the home directory from ~/.composer/vendor... to the complete path.

NOTE 2: %drush is "A read-only property: points to the folder that the drush script is stored in." (see https://raw.githubusercontent.com/drush-ops/drush/master/examples/example.aliases.drushrc.php). So, not useful for specifying Drush's location if you installed it with composer like I did.

You will also need to specify your 'remote_user', I'm using my ssh user. The complete entry in .drush/aliases.drushrc.php looks like:

  $aliases['SITE_ALIAS'] = array(
     'uri' => 'example.com',
     'root' => '/home/USER/public_html',
     'remote-host' => 'IP_ADDRESS',
     'remote-user' => 'SHH_USER',
     'path-aliases' =>
     array (
        '#drush-script' => '~/.composer/vendor/bin/drush',
        '%drush' => '/home/USER/.composer/vendor/drush/drush',
        '%site' => 'sites/default/',
     ),
     'databases' =>
     array (
        'default' =>
        array (
           'default' =>
           array (
              'database' => 'DB_NAME',
              'username' => 'DB_USER',
              'password' => 'DB_PASS',
              'host' => 'localhost',
              'port' => '',
              'driver' => 'mysql',
              'prefix' => '',
           ),
        ),
     ),
  );

Tweet me if you get stuck @modelAgnostic.