[mysql] MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

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.