允许所有远程连接，MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10236000/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Allow all remote connections, MySQL
提问by stephen.hanson
I had been using SQL Server and am now using MySQL for a project. With SQL Server, our developers can connect to the remote database on their local machines if they know the host, username, password. With MySQL, though, to give a developer access from their local machines, I have been having to log in to MySQL and execute:
我一直在使用 SQL Server，现在在一个项目中使用 MySQL。使用 SQL Server，如果我们的开发人员知道主机、用户名和密码，他们就可以连接到本地计算机上的远程数据库。然而，对于 MySQL，为了让开发人员从他们的本地机器访问，我不得不登录到 MySQL 并执行：
GRANT ALL ON *.* to user@address IDENTIFIED BY 'password';
flush privileges;
Where addressis the IP address of the developer's machine. Of course, if they change networks, I have to execute it again. Is there a way to allow all remote connections like I have experienced with SQL Server, or is this a bad idea for some reason? We have username and password still.. I'm obviously a little confused.
address开发者机器的IP地址在哪里。当然，如果他们改变网络，我必须再次执行。有没有办法允许所有远程连接，就像我在 SQL Server 上遇到的那样，或者出于某种原因这是一个坏主意？我们还有用户名和密码。我显然有点困惑。
Also: this is a development database and is only accessible from our internal network. I understand why it is a bad idea to give everyone access to a production database.
另外：这是一个开发数据库，只能从我们的内部网络访问。我理解为什么让每个人都可以访问生产数据库是个坏主意。
回答by Icarus
As pointed out by Ryan above, the command you need is
正如上面 Ryan 所指出的，您需要的命令是
GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';
However, note that the documentation indicates that in order for this to work, another user account from localhostmust be created for the same user; otherwise, the anonymous account created automatically by mysql_install_dbtakes precedence because it has a more specific host column.
但是，请注意文档表明，为了使其工作，localhost必须为同一用户创建另一个用户帐户；否则，自动创建的匿名帐户mysql_install_db优先，因为它具有更具体的主机列。
In other words; in order for user userto be able to connect from any server; 2 accounts need to be created as follows:
换句话说; 为了让用户user能够从任何服务器连接；需要创建2个帐户，如下所示：
GRANT ALL ON *.* to user@localhost IDENTIFIED BY 'password';
GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';
Read the full documentation here.
And here's the relevant piece for reference:
这是相关的部分供参考：
After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:
以 root 身份连接到服务器后，您可以添加新帐户。以下语句使用 GRANT 设置四个新帐户：
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”.)
这些报表创建的帐户具有以下属性：
其中两个帐户的用户名是 monty，密码是 some_pass。这两个帐户都是具有执行任何操作的完全权限的超级用户帐户。'monty'@'localhost' 帐户只能在从本地主机连接时使用。'monty'@'%' 帐户使用 '%' 通配符作为主机部分，因此它可用于从任何主机连接。
必须同时拥有 monty 的两个帐户才能像 monty 一样从任何地方连接。如果没有 localhost 帐户，当 monty 从本地主机连接时，由 mysql_install_db 创建的 localhost 的匿名用户帐户将优先。因此，monty 将被视为匿名用户。其原因是匿名用户帐户具有比 'monty'@'%' 帐户更具体的主机列值，因此在用户表排序顺序中排在更早的位置。（用户表排序在第 6.2.4 节“访问控制，阶段 1：连接验证”中讨论。）
That seems silly to me unless I am misunderstanding this.
除非我误解了这一点，否则这对我来说似乎很愚蠢。
回答by David Tinker
You can disable all security by editing /etc/my.cnf:
您可以通过编辑 /etc/my.cnf 禁用所有安全性：
[mysqld]
skip-grant-tables
回答by Ryan
GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';
Will allow a specific user to log on from anywhere.
将允许特定用户从任何地方登录。
It's bad because it removes some security control, i.e. if an account is compromised.
这很糟糕，因为它删除了一些安全控制，即如果帐户被盗用。
回答by Pugazendhi Asaimuthu
Install and setup mysql to connect from anywhere remotely DOES NOT WORK WITH mysql_secure_installation !(https://dev.mysql.com/doc/refman/5.5/en/mysql-secure-installation.html)
安装和设置 mysql 以从任何地方远程连接不适用于 mysql_secure_installation！( https://dev.mysql.com/doc/refman/5.5/en/mysql-secure-installation.html)
On Ubuntu, Install mysql using:
在 Ubuntu 上，使用以下命令安装 mysql：
sudo apt-get install mysql-server
Have just the below in /etc/mysql/my.cnf
在 /etc/mysql/my.cnf 中有以下内容
[mysqld]
#### Unix socket settings (making localhost work)
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
#### TCP Socket settings (making all remote logins work)
port = 3306
bind-address = 0.0.0.0
Login into DB from server using
使用从服务器登录到数据库
mysql -u root -p
mysql -u 根 -p
Create DB user using the below statement
使用以下语句创建数据库用户
grant all privileges on *.* to ‘username'@‘%' identified by ‘password';
Open firewall:
打开防火墙：
sudo ufw allow 3306
Restart mysql
重启mysql
sudo service mysql restart
回答by An?l ?zselgin
Also you need to disable below line in configuration file: bind-address = 127.0.0.1
您还需要在配置文件中禁用以下行：bind-address = 127.0.0.1
回答by Mike M
mysql> CREATE USER 'monty'@'192.168.%.%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'192.168.%.%'
回答by Gregory Santana
Mabey you only need:
Mabey 你只需要：
Step one:
步骤1：
grant all privileges on *.* to 'user'@'IP' identified by 'password';
or
或者
grant all privileges on *.* to 'user'@'%' identified by 'password';
Step two:
第二步：
sudo ufw allow 3306
Step three:
第三步：
sudo service mysql restart
