2

I'm trying to connect to mysql using the command

mysql -h 127.0.0.1

It comes up with the error

ERROR 1045 (28000): Access denied for user 'root'@'mydomain.com' (using password: NO)

Why is 127.0.0.1 being converted into my domain name, and how can I fix it?

However, it does work if I don't specify a host (and by extension, if 'localhost' is specified).

Edit: It seems it resolves to the domain name when using TCP, so it also fails when using 

mysql -h localhost --protocol=TCP

Edit2: When I use skip-name-resolve I get a similar output except mydomain.com is replaced with x.x.x.x which is the public IP of mydomain.com.

Ben
  • 51
  • Assuming you're on a Unix/Linux machine - have you got 127.0.0.1 mydomain.com in your /etc/hosts file? – garethTheRed Jul 04 '14 at 08:51
  • I had tried that, but it didn't yield any difference – Ben Jul 04 '14 at 11:03
  • No difference as in it still said Access denied or it still has mydomain.com in the user name? If it's the latter and you definitely have removed your hostname from /etc/hosts then DNS must be misconfigured for mysql to be able to reverse lookup 127.0.0.1 to your hostname. – garethTheRed Jul 04 '14 at 11:11
  • No difference as in the error output was exactly the same with or without that in /etc/hosts – Ben Jul 04 '14 at 11:32
  • This answer contains hints as to finding a solution. – Daniel B Jul 04 '14 at 12:12
  • @DanielB I mentioned in the first edit that the issue seemed to be for TCP connections. I can however telnet 127.0.0.1 3306 which shows that mysqld is listening and accessible. I can also explicitly add a 'root'@'mydomain.com' user and login to mysql. – Ben Jul 04 '14 at 12:23
  • @user3603149 : Did you checked whether you have provided user root with proper privilege and password – Renju Chandran chingath Jul 04 '14 at 12:48
  • @RenjuChandranchingath logging in is not the direct issue. The issue is that any attempts to connect using mysql -h 127.0.0.1 are not seen as coming from localhost or 127.0.0.1 but something else (in this case the hostname of (or) the public IP) – Ben Jul 04 '14 at 12:53
  • @garethTheRed I added Edit2 which suggests that rDNS is not the cause. It seems more like an iptables issue but I've tried flushing unnecessary rules etc to no avail. – Ben Jul 04 '14 at 13:29
  • Are you using a password? mysql -h 127.0.0.1 -uroot -pMyRootPassword

    actually works as intended.

     –  Jul 04 '14 at 06:35
  • The password is irrelevant. If I added 'root'@'mydomain.com' as a user it would work, but I want to connect using 'root'@'127.0.0.1'. The problem is that any mysql TCP connection made to either 127.0.0.1 or localhost gets changed into mydomain.com – Ben Jul 04 '14 at 06:41
  • You forgot to state your operating system and MySQL version. – Michael Hampton Jul 04 '14 at 14:31

5 Answers5

3

I had masquerading for all external packets (including on lo). Removing/editing the iptable -t nat POSTROUTING rules fixed the issue.

Ben
  • 51
0

Most likely this is caused by a hosts file entry.

0

Maybe mysql is listening only on your public ip and not on 0.0.0.0?

Kazimieras Aliulis
  • 354
0

I'm not sure to understand your question but 127.0.0.1 is the standard loopback address. SO if your host have this IP : 192.168.0.1 Then 192.168.0.1 = 127.0.0.1 = localhost

NooJ
  • 11
  • 1
    In mysql, these things are not the same. User accounts (and permissions) are maintained individually for each host, so 'root'@'localhost' can have a different password to 'root'@'127.0.0.1' – Ben Jul 04 '14 at 06:32
  • OK, good to know. – NooJ Jul 04 '14 at 06:38
0

1) Check /etc/hosts and make sure that 127.0.0.1 points only to localhost and not mydomain.com. mydomain.com should be associated with the actual IP address for the server and not with localhost.localdomain.

2) It also looks like you've set the password for root on your mysql instance (actually this is a good thing). Recommend adding the "-p" switch to your login so that it looks like:

mysql -h localhost -p

You will then be asked to enter the password for root. Hope this helps.

joatd
  • 513