CODEWITHSUNDEEP
mysqlmysql-error-1064
java_enthu
java_enthu

Reputation: 2327

Strange behaviour when connecting MySQL with hostname and password

I've MySQL Server 5.1.73 running on the CentOS 6.x server. I've set the password for root user. When I specify the following command it logs me to the mysql shell.

[root@sandbox ~]#  mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60384

I am checking the hostname 

mysql> SELECT @@hostname;
+-------------------------+
| @@hostname              |
+-------------------------+
| sandbox.hortonworks.com |
+-------------------------+

Now I loggoff and try to login with specifying host name explicitly and it asks me to provide the password it throws access denied error.

[root@sandbox ~]# hostname -f
sandbox.hortonworks.com
[root@sandbox ~]#  mysql -u root -p -h sandbox.hortonworks.com
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'sandbox.hortonworks.com' (using password: YES)

Even stranger part : I use the same command but remove -p parameter (i.e. using password = false), it logs in successfully without asking password.

[root@sandbox ~]#  mysql -u root -h sandbox.hortonworks.com
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61736

Could any one tell if I am missing any configuration?

Regards, -JE

Upvotes: 0

Views: 562

Answers (1)

mrcheshire
mrcheshire

Reputation: 535

Two things:

I expect the first login attempt works but the second doesn't because your root user is only defined as root@localhost - when you try to connect via an external interface (instead of localhost or 127.0.0.1) you can't authenticate as that user. If that's the issue, you'll need to create a [email protected] user account or alter the existing root user.

Secondly, the successful login without password was probably not as successful as you thought. MySQL will "helpfully" log you in as a different user than the one you specified in some situations, see this question: https://superuser.com/questions/266758/how-do-i-see-which-user-i-am-logged-in-as-in-mysql

The way mysql handles users can definitely be confusing (I'm no longer an expert, I've moved on to using PostgreSQL primarily, so I may be missing something in my explanation) but that's my best guess for the behavior that you're seeing.

Upvotes: 1

Related Questions