Connecting mysql via 127.0.0.1 instead of localhost

Question

I connected mysql on Mac OS X using this command:

mysql -u root -p -h 127.0.0.1

After I logged in, I fired up this command:

select user();

and it showed:

 root@localhost

and even wired, I already change the root password on 127.0.0.1 via SET PASSWORD command.

When I typed in

show grants for 'root'@'127.0.0.1';

the password did change in the result.

However, I still have to use the old password to log in with the following command:

mysql -u root -p -h 127.0.0.1

QUESTIONS

• Could you tell me why this happened?
• Is there anything wrong here?

Answer 1

Connecting to MySQL using 127.0.0.1 requires the TCP/IP protocol.

The problem is that the mysql client tries to outsmart you by saying

• If you specify 127.0.0.1 and do not tell me the protocol, I will use the socket and behave like a localhost connection.
• If you specify 127.0.0.1 and tell me to use TCP/IP, THEN I will use a TCP/IP connection

In light of this, you must connect as follows:

mysql -u root -p -h 127.0.0.1 --protocol=tcp

to verify this, please run this after connecting

SELECT USER(),CURRENT_USER();

Both should say root@127.0.0.1 (how do I see which user I am logged in as in MySQL?)

Give it a Try !!!