我一直在使用 SQL Server，现在正在将 MySQL 用于一个项目。使用 SQL Server，如果我们的开发人员知道主机、用户名和密码，他们就可以连接到本地计算机上的远程数据库。但是，对于 MySQL，为了让开发人员能够从他们的本地计算机访问，我不得不登录 MySQL 并执行:

GRANT ALL ON *.* to user@address IDENTIFIED BY 'password'; 
flush privileges;

其中address是开发者机器的IP地址。当然，如果他们改变网络，我必须再次执行它。有没有办法像我在 SQL Server 上体验过的那样允许所有远程连接，或者出于某种原因这是一个坏主意？我们还有用户名和密码。我显然有点困惑。

另外:这是一个开发数据库，​​只能从我们的内部网络访问。我理解为什么让每个人都可以访问生产数据库是个坏主意。

最佳答案

正如上面 Ryan 指出的，你需要的命令是

GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password'; 

但是，请注意，文档指出，为了使其正常工作，必须为同一用户创建来自 localhost 的另一个用户帐户；否则，由 mysql_install_db 自动创建的匿名帐户优先，因为它具有更具体的主机列。

换句话说；为了让用户 user 能够从任何服务器连接； 2个账号需要创建如下:

GRANT ALL ON *.* to user@localhost IDENTIFIED BY 'password'; 
GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password'; 

阅读完整文档 here.

这是相关的引用文章:

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

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;
mysql> CREATE USER 'admin'@'localhost';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

The accounts created by these statements have the following properties:

Two of the accounts have a user name of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. The 'monty'@'localhost' account can be used only when connecting from the local host. The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

It is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 6.2.4, “Access Control, Stage 1: Connection Verification”.)

https://stackoverflow.com/questions/10236000/