创建 MySQL 用户时使用 % 作为主机

Using % for host when creating a MySQL user

提问by Ed Manet

My MySQL database needs two users: appuser and support.
One of the application developers insists that I create four accounts for these users:

我的 MySQL 数据库需要两个用户：appuser 和 support。
其中一位应用程序开发人员坚持要求我为这些用户创建四个帐户：

appuser@'%'
appuser@'localhost'
support@'%'
support@'localhost'

For the life of me I can't figure out why he thinks we need this. Wouldn't using the wildcard as the host take care of the 'localhost'?

对于我的生活，我无法弄清楚为什么他认为我们需要这个。主机不会使用通配符来处理“本地主机”吗？

Any ideas?

有任何想法吗？

(Using MySQL 5.5 here)

（此处使用 MySQL 5.5）

回答by aleroot

localhostis special in MySQL, it means a connection over a UNIX socket (or named pipes on Windows, I believe) as opposed to a TCP/IP socket. Using %as the host does not include localhost, hence the need to explicitly specify it.

localhost在 MySQL 中是特殊的，它意味着通过 UNIX 套接字（或 Windows 上的命名管道，我相信）而不是 TCP/IP 套接字的连接。使用%如主机不包含localhost，因此需要明确的指定。

回答by John Kary

As @nos pointed out in the comments of the currently accepted answer to this question, the accepted answer is incorrect.

正如@nos 在对该问题当前接受的答案的评论中指出的那样，接受的答案是不正确的。

Yes, there IS a difference between using %and localhostfor the user account host when connecting via a socket connect instead of a standard TCP/IP connect.

是的，通过套接字连接而不是标准 TCP/IP 连接进行连接时，使用%和localhost用于用户帐户主机之间存在差异。

A host value of %does not include localhostfor sockets and thus must be specified if you want to connect using that method.

主机值%不包括localhost套接字，因此如果要使用该方法进行连接，则必须指定。

回答by David Tonhofer

Let's just test.

让我们来测试一下。

Connect as superuser, and then:

以超级用户身份连接，然后：

SHOW VARIABLES LIKE "%version%"; 
+-------------------------+------------------------------+ 
| Variable_name ??????????| Value ???????????????????????| 
+-------------------------+------------------------------+ 
| version ????????????????| 10.0.23-MariaDB-0+deb8u1-log | 

and then

进而

USE mysql;

Setup

设置

Create a user foowith password barfor testing:

创建一个foo带密码的用户bar进行测试：

CREATE USER foo@'%' IDENTIFIED BY 'bar'; FLUSH PRIVILEGES;

Connect

连接

To connect to the Unix Domain Socket (i.e. the I/O pipe that is named by the filesystem entry /var/run/mysqld/mysqld.sockor some such), run this on the command line (use the --protocoloption to make doubly sure)

要连接到 Unix 域套接字（即由文件系统条目/var/run/mysqld/mysqld.sock或其他类似命名的 I/O 管道），请在命令行上运行它（使用该--protocol选项来加倍确定）

mysql -pbar -ufoo
mysql -pbar -ufoo --protocol=SOCKET

One expects that the above matches "user comes from localhost" but certainly not "user comes from 127.0.0.1".

人们期望上述匹配“用户来自本地主机”，但肯定不是“用户来自 127.0.0.1”。

To connect to the server from "127.0.0.1" instead, run this on the command line

要从“127.0.0.1”连接到服务器，请在命令行上运行

mysql -pbar -ufoo --bind-address=127.0.0.1 --protocol=TCP

If you leave out --protocol=TCP, the mysqlcommand will still try to use the Unix domain socket. You can also say:

如果您省略--protocol=TCP，该mysql命令仍将尝试使用 Unix 域套接字。你也可以说：

mysql -pbar -ufoo --bind-address=127.0.0.1 --host=127.0.0.1

The two connection attempts in one line:

一行中的两次连接尝试：

export MYSQL_PWD=bar; \
mysql -ufoo --protocol=SOCKET --execute="SELECT 1"; \
mysql -ufoo --bind-address=127.0.0.1 --host=127.0.0.1 --execute="SELECT 1"

(the password is set in the environment so that it is passed to the mysqlprocess)

（密码在环境中设置，以便它传递给mysql进程）

Verification In Case Of Doubt

有疑问时的验证

To really check whether the connection goes via a TCP/IP socket or a Unix Domain socket

真正检查连接是通过 TCP/IP 套接字还是 Unix 域套接字

1. get the PID of the mysql client process by examining the output of ps faux
2. run lsof -n -p<yourpid>.

1. 通过检查输出来获取 mysql 客户端进程的 PID ps faux
2. 跑lsof -n -p<yourpid>。

You will see something like:

你会看到类似的东西：

mysql [PID] quux 3u IPv4 [code] 0t0 TCP 127.0.0.1:[port]->127.0.0.1:mysql (ESTABLISHED)

or

或者

mysql [PID] quux 3u unix [code] 0t0 [code] socket

So:

所以：

Case 0: Host = '10.10.10.10' (null test)

案例 0：主机 = '10.10.10.10'（空测试）

update user set host='10.10.10.10' where user='foo'; flush privileges;

• Connect using socket: FAILURE
• Connect from 127.0.0.1: FAILURE

• 使用套接字连接：FAILURE
• 从 127.0.0.1 连接：失败

Case 1: Host = '%'

情况 1：主机 = '%'

update user set host='%' where user='foo'; flush privileges;

• Connect using socket: OK
• Connect from 127.0.0.1: OK

• 使用socket连接：OK
• 从 127.0.0.1 连接：好的

Case 2: Host = 'localhost'

案例 2：主机 = 'localhost'

update user set host='localhost' where user='foo';flush privileges;

Behaviour variesand this apparently depends on skip-name-resolve. If set, causes lines with localhostto be ignored according to the log. The following can be seen in the error log: "'user' entry 'root@localhost' ignored in --skip-name-resolve mode.". This means no connecting through the Unix Domain Socket. But this is empirically not the case. localhostnow means ONLY the Unix Domain Socket, and no longer matched 127.0.0.1.

行为各不相同，这显然取决于skip-name-resolve. 如果设置，将导致localhost根据日志忽略行。在错误日志中可以看到以下内容：“'user' entry 'root@localhost' 在 --skip-name-resolve 模式下被忽略。” . 这意味着不通过 Unix 域套接字进行连接。但经验上并非如此。localhost现在仅表示 Unix 域套接字，不再匹配 127.0.0.1。

skip-name-resolveis off:

skip-name-resolve已关闭：

• Connect using socket: OK
• Connect from 127.0.0.1: OK

• 使用socket连接：OK
• 从 127.0.0.1 连接：好的

skip-name-resolveis on:

skip-name-resolve正在：

• Connect using socket: OK
• Connect from 127.0.0.1: FAILURE

• 使用socket连接：OK
• 从 127.0.0.1 连接：失败

Case 3: Host = '127.0.0.1'

情况 3：主机 = '127.0.0.1'

update user set host='127.0.0.1' where user='foo';flush privileges;

• Connect using socket: FAILURE
• Connect from 127.0.0.1: OK

• 使用套接字连接：FAILURE
• 从 127.0.0.1 连接：好的

Case 4: Host = ''

案例 4：主机 = ''

update user set host='' where user='foo';flush privileges;

• Connect using socket: OK
• Connect from 127.0.0.1: OK

• 使用socket连接：OK
• 从 127.0.0.1 连接：好的

(According to MySQL 5.7: 6.2.4 Access Control, Stage 1: Connection Verification, The empty string '' also means “any host” but sorts after '%'.)

（根据MySQL 5.7: 6.2.4 Access Control, Stage 1: Connection Verification，空字符串 '' 也表示“任何主机”，但在 '%' 之后排序。）

Case 5: Host = '192.168.0.1' (extra test)

案例 5：Host = '192.168.0.1'（额外测试）

('192.168.0.1' is one of my machine's IP addresses, change appropriately in your case)

（'192.168.0.1' 是我机器的 IP 地址之一，根据您的情况进行适当更改）

update user set host='192.168.0.1' where user='foo';flush privileges;

• Connect using socket: FAILURE
• Connect from 127.0.0.1: FAILURE

• 使用套接字连接：FAILURE
• 从 127.0.0.1 连接：失败

but

但

• Connect using mysql -pbar -ufoo -h192.168.0.1: OK (!)

• 连接使用mysql -pbar -ufoo -h192.168.0.1：好的（！）

The latter because this is actually TCP connection coming from 192.168.0.1, as revealed by lsof:

后者因为这实际上是来自 的 TCP 连接192.168.0.1，如以下所示lsof：

TCP 192.168.0.1:37059->192.168.0.1:mysql (ESTABLISHED)

Edge Case A: Host = '0.0.0.0'

边缘情况 A：主机 = '0.0.0.0'

update user set host='0.0.0.0' where user='foo';flush privileges;

• Connect using socket: FAILURE
• Connect from 127.0.0.1: FAILURE

• 使用套接字连接：FAILURE
• 从 127.0.0.1 连接：失败

Edge Case B: Host = '255.255.255.255'

边缘情况 B：主机 = '255.255.255.255'

update user set host='255.255.255.255' where user='foo';flush privileges;

• Connect using socket: FAILURE
• Connect from 127.0.0.1: FAILURE

• 使用套接字连接：FAILURE
• 从 127.0.0.1 连接：失败

Edge Case C: Host = '127.0.0.2'

边缘情况 C：主机 = '127.0.0.2'

(127.0.0.2 is perfectly valid loopback address equivalent to 127.0.0.1 as defined in RFC6890)

（127.0.0.2 是完全有效的环回地址，相当于RFC6890 中定义的 127.0.0.1 ）

update user set host='127.0.0.2' where user='foo';flush privileges;

• Connect using socket: FAILURE
• Connect from 127.0.0.1: FAILURE

• 使用套接字连接：FAILURE
• 从 127.0.0.1 连接：失败

Interestingly:

有趣的是：

• mysql -pbar -ufoo -h127.0.0.2connects from 127.0.0.1and is FAILURE
• mysql -pbar -ufoo -h127.0.0.2 --bind-address=127.0.0.2is OK

• mysql -pbar -ufoo -h127.0.0.2连接自127.0.0.1并且是 FAILURE
• mysql -pbar -ufoo -h127.0.0.2 --bind-address=127.0.0.2没问题

Cleanup

清理

delete from user where user='foo';flush privileges;

Addendum

附录

To see what is actually in the mysql.usertable, which is one of the permission tables, use:

要查看mysql.user表中的实际内容（权限表之一），请使用：

SELECT SUBSTR(password,1,6) as password, user, host,
Super_priv AS su,
Grant_priv as gr,
CONCAT(Select_priv, Lock_tables_priv) AS selock,
CONCAT(Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) AS modif,
CONCAT(References_priv, Index_priv, Alter_priv) AS ria,
CONCAT(Create_tmp_table_priv, Create_view_priv, Show_view_priv) AS views,
CONCAT(Create_routine_priv, Alter_routine_priv, Execute_priv, Event_priv, Trigger_priv) AS funcs,
CONCAT(Repl_slave_priv, Repl_client_priv) AS replic,
CONCAT(Shutdown_priv, Process_priv, File_priv, Show_db_priv, Reload_priv, Create_user_priv) AS admin
FROM user ORDER BY user, host;

this gives:

这给出：

+----------+----------+-----------+----+----+--------+-------+-----+-------+-------+--------+--------+
    | password | user     | host      | su | gr | selock | modif | ria | views | funcs | replic | admin  |
    +----------+----------+-----------+----+----+--------+-------+-----+-------+-------+--------+--------+
    | *E8D46   | foo      |           | N  | N  | NN     | NNNNN | NNN | NNN   | NNNNN | NN     | NNNNNN |

Similarly for table mysql.db:

表类似mysql.db：

SELECT host,db,user, 
       Grant_priv as gr,
       CONCAT(Select_priv, Lock_tables_priv) AS selock, 
       CONCAT(Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) AS modif, 
       CONCAT(References_priv, Index_priv, Alter_priv) AS ria, 
       CONCAT(Create_tmp_table_priv, Create_view_priv, Show_view_priv) AS views, 
       CONCAT(Create_routine_priv, Alter_routine_priv, Execute_priv) AS funcs 
       FROM db ORDER BY user, db, host;

回答by alex

If you want connect to user@'%'from localhost use mysql -h192.168.0.1 -uuser -p.

如果你想user@'%'从 localhost连接到mysql -h192.168.0.1 -uuser -p.

回答by Joshua Walsh

Going to provide a slightly different answer to those provided so far.

将提供与迄今为止提供的答案略有不同的答案。

If you have a row for an anonymous user from localhost in your users table ''@'localhost'then this will be treated as more specific than your user with wildcard'd host 'user'@'%'. This is why it is necessary to also provide 'user'@'localhost'.

如果您的 users 表中有一行来自 localhost 的匿名用户，''@'localhost'那么这将被视为比您使用通配符主机的用户更具体'user'@'%'。这就是为什么还需要提供'user'@'localhost'.

You can see this explained in more detail at the bottom of this page.

您可以在本页底部看到更详细的解释。

回答by ling

The percent symbol means: any host, including remote and local connections.

百分比符号表示：任何主机，包括远程和本地连接。

The localhost allows only local connections.

本地主机只允许本地连接。

(so to start off, if you don't need remote connections to your database, you can get rid of the appuser@'%' user right away)

（所以首先，如果您不需要远程连接到您的数据库，您可以立即摆脱 appuser@'%' 用户）

So, yes, they are overlapping, but...

所以，是的，它们是重叠的，但是......

...there is a reason for setting both types of accounts, this is explained in the mysql docs: http://dev.mysql.com/doc/refman/5.7/en/adding-users.html.

...设置这两种类型的帐户是有原因的，这在 mysql 文档中进行了解释：http: //dev.mysql.com/doc/refman/5.7/en/adding-users.html。

If you have an have an anonymous user on your localhost, which you can spot with:

如果您的本地主机上有一个匿名用户，您可以通过以下方式发现：

select Host from mysql.user where User='' and Host='localhost';

and if you just create the user appuser@'%' (and you not the appuser@'localhost'), then when the appuser mysql user connects from the local host, the anonymous user account is used(it has precedence over your appuser@'%' user).

如果您只是创建用户 appuser@'%'（而不是 appuser@'localhost'），那么当 appuser mysql 用户从本地主机连接时，将使用匿名用户帐户（它优先于您的 appuser@ '%' 用户）。

And the fix for this is (as one can guess) to create the appuser@'localhost' (which is more specific that the local host anonymous user and will be used if your appuser connects from the localhost).

对此的解决方法是（正如人们所猜测的那样）创建 appuser@'localhost' （它更具体，本地主机匿名用户，如果您的 appuser 从本地主机连接，则将使用该用户）。