2

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.

2
  • Every time I've ever seen this issue, it had to do with the dump-dir not being writeable by the current user context on one end or the other. Oct 14, 2013 at 14:30
  • Check if there is a wildcard localhost user defined in your user table, could be causing the problem.
    – Cameron
    Nov 3, 2017 at 4:02

3 Answers 3

0

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.

0
$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' => '',
          ),
      ),   ), );
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.