When you run mysql -u bill -p
, localhost
is resolved to your ip, since it is 127.0.0.1 and in your /etc/hosts
file, as default 127.0.0.1 localhost
exists. So, mysql interprets you as bill@localhost
which is not granted with bill@'%'
. This is why there are 2 different records for root
user in result of select host, user from mysql.user;
query.
There are two ways to handle this issue.
One is specifying an ip which is not reversely resolved by /etc/hosts
file when you try to login. For example, the ip of server is 10.0.0.2
. When you run the command mysql -u bill -p -h 10.0.0.2
, you will be able to login. If you type select user();
, you will get [email protected]
. Of course, any domain name should not be resolved to this ip in your /etc/hosts
file.
Secondly, you need grant access for this specific domain name. For bill@localhost
, you should call command grant all privileges on *.* to bill@localhost identified by 'billpass';
. In this case, you will be able to login with command mysql -u bill -p
. Once logined, select user();
command returns bill@localhost
.
But this is only for that you try to login a mysql server in the same host. From remote hosts, mysql behaves expectedly, '%' will grant you to login.