mysql - 不允许主机 'xxx.xx.xxx.xxx' 连接到此 MySQL 服务器

Question

这应该非常简单，但我无法让它在我的生活中发挥作用。
我只是想远程连接到我的 MySQL 服务器。

• 连接为:

  mysql -u root -h localhost -p  
  

• 工作正常，但正在尝试:

  mysql -u root -h 'any ip address here' -p
  

• 因错误而失败:

  ERROR 1130 (00000): Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

在 mysql.user 表中，主机为“localhost”的用户“root”与主机为“%”的用户“root”的条目完全相同。

我束手无策，不知道如何继续。欢迎任何想法。

Answer 1

可能是一种安全预防措施。您可以尝试添加一个新的管理员帐户:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;

虽然正如 Pascal 和其他人所指出的那样，让具有这种访问权限的用户对任何 IP 开放都不是一个好主意。如果您需要管理用户，请使用 root，并将其保留在本地主机上。对于任何其他操作，请准确指定您需要的权限并限制用户的可访问性，如 Pascal 在下面建议的那样。

编辑:

来自 MySQL 常见问题解答:

If you cannot figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain '%' or '_' characters). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this allows 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 5.4.4, “Access Control, Stage 1: Connection Verification”.