0

I can connect to my mysql database through command line when specifying the exact IP:

 mysql -u root -p -h 172.31.110.55

I am on aws, but when I try to connect using the DNS, I get this error:

  $ mysql -u root -p -h ip-172-31-110-55.us-west-2.compute.internal
  ERROR 1045 (28000): Access denied for user 'root'@'ip-172-31-110-55.us-west-2.compute.internal' (using password: YES)

I double checked that I am typing the password correctly

mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@ip-172-31-110-55.us-west-2.compute.internal                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip-172-31-110-55.us-west-2.compute.internal' IDENTIFIED BY PASSWORD '****HASH_OF_PASSWORD****' |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+

what can be the issue? I believe that this is the reason I can also get error when trying to access the database remotely when using phpmyadmin

  MySQL said: 
 #1045 - Access denied for user 'root'@'ip-172-31-110-55.us-west-2.compute.internal' (using password: YES)

my phpmyadmin config file:

/* Remote Server */
$i++;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['host'] = 'myinstance.123456789012.us-east-1.rds.amazonaws.com';
$cfg['Servers'][$i]['verbose'] = 'Remote Server Name';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '**********'; 
$cfg['Servers'][$i]['hide_db'] = '^(mysql|performance_schema|innodb|information_schema)$';
justadev
  • 393
  • 2
  • 4
  • 20
  • Can you post what other users you have on the MySQL server? If you have another user named root or ''@'ip-172-31-110-55.us-west-2.compute.internal' MySQL could be trying to authenticate to them first. `select user, host from mysql.user;` – Bert Sep 20 '16 at 20:25
  • I solved my phpmyadmin issue which was the important factor for me. the connection issue still exists, but I will leave it for now. thanks for trying to help – justadev Sep 20 '16 at 20:34

1 Answers1

0

I solved my phpmyadmin issue (which was the main problem I was facing). Posting the fix here for future generations because it was too long to put in a comment.

/* Remote Server */
$i++;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '172.31.110.55';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['verbose'] = 'Remote Server Name';
$cfg['Servers'][$i]['hide_db'] = '^(mysql|performance_schema|innodb|information_schema)$';

I also did "mysqladmin -u root password 'newpassword'" don't know if it has anything to do with it.

The command line issue still exists. If anybody has a clue I will be happy to check further.

justadev
  • 393
  • 2
  • 4
  • 20