如何授予整个子网对 MySQL 的远程访问权限？

How to grant remote access to MySQL for a whole subnet?

提问by skystar7

I can easily grant access to one IP using this code:

我可以使用以下代码轻松授予对一个 IP 的访问权限：

$ mysql -u root -p
Enter password:    
mysql> use mysql    
mysql> GRANT ALL ON *.* to root@'192.168.1.4' IDENTIFIED BY 'your-root-password';     
mysql> FLUSH PRIVILEGES;

But i need to allow the whole subnet 192.168.1.* to access the database remotely.

但我需要允许整个子网 192.168.1.* 远程访问数据库。

How can i do that?

我怎样才能做到这一点？

回答by Malvineous

It looks like you can also use a netmask, e.g.

它看起来像你也可以使用一个网络掩码，如

GRANT ... TO 'user'@'192.168.0.0/255.255.255.0' IDENTIFIED BY ...

回答by p0lar_bear

EDIT: Consider looking at and upvoting Malvineous's answer on this page. Netmasks are a much more elegant solution.

编辑：考虑在此页面上查看并支持Malvineous的答案。网络掩码是一个更优雅的解决方案。

---

---

Simply use a percent sign as a wildcard in the IP address.

只需在 IP 地址中使用百分号作为通配符即可。

From http://dev.mysql.com/doc/refman/5.1/en/grant.html

来自http://dev.mysql.com/doc/refman/5.1/en/grant.html

You can specify wildcards in the host name. For example, user_name@'%.example.com'applies to user_namefor any host in the example.comdomain, and user_name@'192.168.1.%'applies to user_namefor any host in the 192.168.1class C subnet.

您可以在主机名中指定通配符。例如，user_name@'%.example.com'适用user_name于example.com域中的任意主机，user_name@'192.168.1.%'适用于C 类子网user_name中的任意主机192.168.1。

回答by Mike Brant

You would just use '%' as your wildcard like this:

您只需使用 '%' 作为您的通配符，如下所示：

GRANT ALL ON *.* to root@'192.168.1.%' IDENTIFIED BY 'your-root-password';

回答by David Allen

mysql> GRANT ALL ON *.* to root@'192.168.1.%' IDENTIFIED BY 'your-root-password';  

The wildcard character is a "%" instead of an "*"

通配符是“%”而不是“*”

回答by site80443

Just a note of a peculiarity I faced:
Consider:

只是我面临的一个特殊性的说明：
考虑：

db server:  192.168.0.101
web server: 192.168.0.102

If you have a user defined in mysql.user as 'user'@'192.168.0.102'with password1 and another 'user'@'192.168.0.%'with password2,

如果您在 mysql.user 中定义了一个用户为'user'@'192.168.0.102'password1，另一个用户'user'@'192.168.0.%'为 password2，

then,

然后，

if you try to connect to the db server from the web server as 'user' with password2,

如果您尝试使用密码 2 以“用户”身份从 Web 服务器连接到数据库服务器，

it will result in an 'Access denied' error because the single IP 'user'@'192.168.0.102'authentication is used over the wildcard 'user'@'192.168.0.%'authentication.

这将导致“拒绝访问”错误，因为'user'@'192.168.0.102'在通配符'user'@'192.168.0.%'身份验证上使用了单个 IP 身份验证。

回答by murat delen

after you connect server and you want to connect on your host, you should do the steps below:

连接服务器并希望在主机上连接后，您应该执行以下步骤：

1. write mysqlto open mysql
2. write GRANT ALL ON .to root@'write_your_ip_addres' IDENTIFIED BY 'write_password_to_connect';
3. press control and X to quit from mysql
4. write nano /etc/mysql/my.cnf
5. write # before bind-address = 127.0.0.1in my.cnf folder
6. #bind-address = 127.0.0.1
7. save my.cnf folder with control + X
8. write service mysql restart
9. you could connect via navicat on your host

1. 写mysql打开mysql
2. 写；GRANT ALL ON .to root@'write_your_ip_addres' IDENTIFIED BY 'write_password_to_connect'
3. 按 control 和 X 退出 mysql
4. 写 nano /etc/mysql/my.cnf
5. 在my.cnf文件夹中在bind-address = 127.0.0.1之前写入#
6. #绑定地址= 127.0.0.1
7. 用 control + X 保存 my.cnf 文件夹
8. 写 service mysql restart
9. 你可以通过主机上的 navicat 连接