Access denied (remote-user and database user are different) on drush sql-sync

Question

With Drush v8.0.3 in both sides (local is a Mac and desarrollo is a Linux Server with CPanel), I'm executing:

sql-sync @project.local @project.desarrollo

and getting this error at the end of the process (everything else works):

ERROR 1045 (28000): Access denied for user 'myremotehostuser'@'localhost' (using password: YES)

My alias file includes this:

$aliases['desarrollo'] = array (
  'root' => '/home/myremotehostuser/subdomains/project/',
  'uri' => 'http://project.myremotehost.com',
  'remote-user' => 'myremotehostuser',
  'remote-host' => 'myremotehost.com',
  'path-aliases' => array(
    '%dump-dir' => '/tmp',
  ),
  'source-command-specific' => array (
    'sql-sync' => array (
      'no-cache' => TRUE,
      'structure-tables-key' => 'common',
    ),
  ),
  'command-specific' => array (
    'sql-sync' => array (
      'sanitize' => TRUE,
      'no-ordered-dump' => TRUE,
      'structure-tables' => array(
        'common' => array('cache', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'sessions', 'watchdog'),
      ),
    ),
  ),
);

And drush status on "desarrollo" returns:

Drupal version         :  7.41                                   
Site URI               :  http://project.myremotehost.com       
Database driver        :  mysql                                  
Database hostname      :  localhost                              
Database port          :                                         
Database username      :  databaseuser                         
Database name          :  databasename                      
PHP configuration      :  /usr/local/lib/php.ini                 
PHP OS                 :  Linux                                  
Drush script           :  /usr/local/bin/drush                   
Drush version          :  8.0.3                                  
Drush temp directory   :  /tmp                                   
Drush configuration    :                                         
Drush alias files      :                                         
Drupal root            :  /home/myremotehostuser/subdomains/project/ 
Drupal Settings File   :  sites/default/settings.php             
Site path              :  sites/default  

As you can see, the "database owner" (a MySQL user) is not the same as "remote-host" (a CPanel user).

Drush is supossed to scan the setting files in both sides to figure out the right config, if I'm not wrong, you don't even have to add a $databases array or a db-url string since v7. Then, why is it trying to access the db with 'myremotehostuser'@'localhost' instead of 'databaseuser'@'localhost'?

In this case, there is no database user called 'myremotehostuser' and I guess that I could solve the problem by creating it and granting permissions for 'databasename' but I'm almost sure I'm missing something really dumb here and there must be a simple solution.

edit:

Trying the same alias file in the same server with host user name and database user name still being different, but this time in a freshly created account, seems to work prefectly. So I guess the problem it's being caused by some CPanel/WHM configuration issue rather than any Drush related thing. I will keep trying this a couple of times before closing the question.

Answer 1

I encountered this issue too. It turns out drush is first looking for the file ~/.my.cnf and if it's found, it'll use it to connect. The file contains the username and password.

[client]
user=myremotehostuser

Once I temporarily remove this file, drush can dump the sql successfully using the site's settings.php.