MySQL ERROR 1045 (28000): Access denied for user ‘my_user‘@‘localhost‘ (using password: YES)

问题：

1. 如果创建的用户时不指定可以登录的主机地址（包括主机地址缺省'my_user'和显示定义任意主机地址可连接'my_user'@'%'）
2. 在创建新用户后使用本地客户端连接本地MySql服务器。

满足以上两个条件，可以复现[28000][1045] Access denied for user 'my_user'@'localhost' (using password: YES)的问题。

原因：

1. 因为MySql的用户验证时优先匹配主机字段。
2. 当启动部署在本地的MySql，使用本地客户端连接本地MySq服务器的主机地址为localhost（即使使用新创建的’my_user’登录，登录字段的全称也为'my_user'@'localhost'）。
3. 所以根据主机优先匹配机制，优先匹配host字段为'localhost'的第一条数据。
4. 若此时MySql服务端的user表中存在匿名用户''@'localhost'（因为在降序排序下，user字段为空排列在user字段为my_user之前），则会提示1025错误。

解决方案：

删除匿名用户''@'localhost' or ''@'127.0.0.1'。

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'127.0.0.1';

MySQL ERROR 1045 (28000): Access denied for user ‘bill’@‘localhost’ (using password: YES)

You probably have an anonymous user ''@'localhost' or ''@'127.0.0.1'.

As per the manual:

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows: (…)

• When a client attempts to connect, the server looks through the rows [of table mysql.user] in sorted order.
• The server uses the first row that matches the client host name and user name.

(…) The server uses sorting rules that order rows with the most-specific Host values first. Literal host names [such as ‘localhost’] and IP addresses are the most specific.

Therefore such an anonymous user would “mask” any other user like '[any_username]'@'%' when connecting from localhost.

'bill'@'localhost' does match 'bill'@'%', but would match (e.g.) ''@'localhost' beforehands.

The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).