Unable to connect to local MySQL db using drush sql-sync

Question

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.

Answer 1

I have faced the same issue. My solution was to add more rights to mysql user with this comande line :

GRANT ALL PRIVILEGES ON `myDB`.* TO 'mySQLuser'@'localhost' WITH GRANT OPTION

Because previous rights :

GRANT USAGE ON *.* TO 'mySQLuser'@'localhost' IDENTIFIED BY PASSWORD 'pwd'

Do not seem to be enough.

Answer 2

$aliases['local'] = array(   'uri' => 'http://yoursite.local',   'root' => $local_sites . 'all/themes/yourtheme/',   'path-aliases' => array(
    '%dump-dir' => $local_sites . 'all/drush/dbdumps',
    '%files' => 'sites/default/files',   ),   'databases' => array(
    'default' =>
      array(
        'default' =>
          array(
            'database' => 'db_name',
            'username' => 'user',
            'password' => 'pass',
            'host' => '127.0.0.1',
            'port' => '',
            'driver' => 'mysql',
            'prefix' => '',
          ),
      ),   ), );

Answer 3

I think you are sync db from local to prod, that means you are accessing your local db from remote server. So you have to change configuration of sql db, so that it can be accessed by remotely.

Edit my.conf file and change the bind address = 127.0.0.1 to bind address = 0.0.0.0 , mean now you are letting access that db from all IP address.