MySQL 拒绝主机为 % 的用户连接

Question

我有 MySQL 5，定义如下:

CREATE DATABASE foo DEFAULT CHARACTER SET = 'utf8';
GRANT ALL ON foo.* TO foo@'%' IDENTIFIED BY 'foo';
FLUSH PRIVILEGES;

但是，当使用用户foo访问时，传递foo，连接被拒绝。当我将其更改为

GRANT ALL ON foo.* TO foo@localhost IDENTIFIED BY 'foo';

MySQL 让我进入。为什么第一个不起作用？据我所知，% 应该匹配任何内容。

更新:发现这个，可能与http://forums.mysql.com/read.php?30,158830,158830有关

手册引用:(没有找到答案)

• http://dev.mysql.com/doc/refman/5.6/en/account-names.html
• http://dev.mysql.com/doc/refman/5.6/en/adding-users.html

Answer 1

来自 mysql documentation :

A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this enables you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 6.2.4, “Access Control, Stage 1: Connection Verification”.

执行第一个命令后检查 mysql.user 表

CREATE DATABASE foo DEFAULT CHARACTER SET = 'utf8';
GRANT ALL ON foo.* TO foo@'%' IDENTIFIED BY 'foo';
FLUSH PRIVILEGES;
select * from mysql.user where User='foo';