Good evening, it's @RolandoMySQLDBA, the author of that post you mentioned.
Look at the last three lines
Server version: 5.1.61-log Source distribution
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
The last line says
127.0.0.1 via TCP/IP so you are 100% using the TCP/IP protocol.
Did you run
SELECT USER(),CURRENT_USER(); ?
If you did, it should say
[email protected] | [email protected]
It would seem weird for the output to be
[email protected] | root@localhost
Please notice the following: Since both root users have identical grants and identical passwords (none in this case), mysqld decided to pick
root@localhost. At this point, you are probably wondering, "Why would
mysqld make such a choice ?"
In the DBA StackExchange, I have this 2-year-old post (MySQL error: Access denied for user 'a'@'localhost' (using password: YES)). In that past, I describe exactly how mysqld peforms user authentication. Note the paragraphs from pages 486,487 of MySQL 5.0 Certification Study Guide
There are two stages of client access control:
In the first stage, a client attempts to connect and the server either
accepts or rejects the connection. For the attempt to succeed, some
entry in the user table must match the host from which the clent
connects, the username, and the password.
In the second stage (which occurs only if a client has already
connected sucessfully), the server checks every query it receives from
the client to see whether the client has sufficient privileges to
The server matches a client against entries in the grant tables based
on the host from which the client connects and the user the client
provides. However, it's possible for more than one record to match:
As I mentioned from the Certification Book,
it's possible for more than one record to match. Therefore,
mysqld made the choice.
If you delete
mysql.user, that's one way to get
SELECT USER(),CURRENT_USER(); to match. Another way is to give
[email protected] a password.
Having 2 root users with identical means of being used for user authentication is the root cause as to why you cannot see
[email protected]. Notwithstanding, you are using
Please make sure
127.0.0.1 is defined in the OS. Run this:
cat /etc/hosts | grep -c "127\.0\.0\.1"
If you get
0, then the OS does not know about it. You would need to add
/etc/hosts and restart the network and then mysql.
UPDATE 2014-04-26 20:00 EDT
What I am about to say may sound silly, but the Documentation uses TCP instead of tcp
On Unix, MySQL programs treat the host name localhost specially, in a
way that is likely different from what you expect compared to other
network-based programs. For connections to localhost, MySQL programs
attempt to connect to the local server by using a Unix socket file.
This occurs even if a --port or -P option is given to specify a port
number. To ensure that the client makes a TCP/IP connection to the
local server, use --host or -h to specify a host name value of
127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by
using the --protocol=TCP option. For example:
shell> mysql --host=127.0.0.1
shell> mysql --protocol=TCP
The --protocol option enables you to establish a particular type of connection even when the other options would normally default to
some other protocol.
Try using protocol=TCP like this
mysql -u root -h 127.0.0.1 --protocol=TCP -ANe"SELECT USER(),CURRENT_USER()"
mysql -u root -h localhost --protocol=TCP -ANe"SELECT USER(),CURRENT_USER()"
to see if it makes a difference.
UPDATE 2014-05-09 16:19
You need to look over the
If you see this in
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
then logging in as
[email protected] should work properly
If you see this in
[email protected] stands no chance of working