I’m attempting to use drush sql-sync to sync my prod database to my local dev copy, and everything seems to work until drush attempts to log into the local MySQL. When it does, it throws this error:

"ERROR 1044 (42000) at line 39: Access denied for user 'drupal_user'@'localhost' to database 'mydb'" 

I’m using a group alias file (e.g. mysite.aliases.drushrc.php), and I have my two aliases defined like so:

$local_sites = '/Users/steve/Sites/'; $aliases['local'] = array(   'uri' => 'default',   'root' => $local_sites . 'mysite/public_html',   'path-aliases' => array(     '%files' => $local_sites . 'mysite/public_html/sites/default/files',     '%dump-dir' => '/Users/steve/Sites/mysite',    ), );  $aliases['prod'] = array(   'uri' => 'default',   'os' => 'Linux',   'root' => '/cluster/www_system/www/www.mysite.org/current_build/public_html',   'remote-host' => 'control.cphwf01.local',   'remote-user' => 'sedwards',   'path-aliases' => array(     '%files' => 'sites/default/files',     '%dump-dir' => '/scratch',    ), ); 

I run sql-sync with –verbose, and I can see the file created on the remote server, and then copied to my local. However, where it fails is at this line:

Calling system(mysql --database=mydb --host=localhost --user=drupal_user --password=mypass --silent < /Users/steve/Sites/mysite/mysiteLeW4G5); 

If I paste this command to the command line like so:

mysql --database=mydb --host=localhost --user=drupal_user --password=mypass  

it connects just fine. Now, I do notice that the temp dump file is then deleted, but it’s there at the time sql-sync is attempting to connect. Any ideas why this would be failing?

On a side note, what flag do I need to pass to sql-sync to get the dumped file to persist on my local once it’s been created? That way I can test w/o having to dump from the server every time. It would also be way to just dump from the remote to my local w/o actually importing into the local db.

Thanks.

EDIT: I’ve run sql-conf for my local site (with –show-passwords) and it shows the correct information for the local copy.

EDIT 2: Following the advice here, I used the root user, and the sync worked. However, this still doesn’t make sense, since using the drush generated connection string with the db user/pw from settings.php at the command line works fine.