MySQL 如何为用户授予对mysql服务器的远程访问权限？

How to grant remote access permissions to mysql server for user?

提问by Aufwind

If I do SHOW GRANTSin my mysql database I get

如果我SHOW GRANTS在我的 mysql 数据库中做我得到

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 
    IDENTIFIED BY PASSWORD 'some_characters' 
    WITH GRANT OPTION

If I am not mistaken, root@localhostmeans that user rootcan access the server only from localhost. How do I tell MySQL to grant rootthe permission to access this mysql server from every other machine (in the same network), too?

如果我没记错的话，这root@localhost意味着用户root只能从localhost. 我如何告诉 MySQL 授予root从其他所有机器（在同一网络中）访问此 mysql 服务器的权限？

回答by Michael Berkowski

This grants root access with the same password from any machine in *.example.com:

这将授予 root 访问权限，使用相同的密码从 中的任何机器*.example.com：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%.example.com' 
    IDENTIFIED BY 'some_characters' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

If name resolution is not going to work, you may also grant access by IP or subnet:

如果名称解析不起作用，您还可以通过 IP 或子网授予访问权限：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%'
? ? IDENTIFIED BY 'some_characters'? 
? ? WITH GRANT OPTION;
FLUSH PRIVILEGES;

MySQL GRANTsyntax docs.

MySQLGRANT语法文档。

回答by moshe beeri

Try:

尝试：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Pa55w0rd' WITH GRANT OPTION;

回答by Khashayar

You need to take some steps to make sure first mysql and then root user is accessible from outside:

您需要采取一些步骤来确保首先可以从外部访问 mysql 然后 root 用户：

1. Disable skip-networkingin my.cnf(i.e: /etc/mysql/my.cnf)

2. Check value of bind-addressin my.cnf, if it's set to 127.0.0.1, you can change it to 0.0.0.0to allow access from all IPs or whatever ip that you want to connect from.

3. Grant remote access the root user from any ip (or specify your ip instead of %)

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
       IDENTIFIED BY 'your_root_password'
       WITH GRANT OPTION;
   FLUSH PRIVILEGES;`
   

4. Restart mysql service:

   sudo service mysql restart
   

1. 禁止skip-networking在my.cnf（即：/etc/mysql/my.cnf）

2. 检查bind-addressin 的值my.cnf，如果它设置为127.0.0.1，您可以将其更改0.0.0.0为允许从所有 IP 或您想要连接的任何 IP 访问。

3. 从任何 ip 授予 root 用户远程访问权限（或指定您的 ip 而不是%）

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
       IDENTIFIED BY 'your_root_password'
       WITH GRANT OPTION;
   FLUSH PRIVILEGES;`
   

4. 重启mysql服务：

   sudo service mysql restart
   

回答by Wasim A.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' 
    IDENTIFIED BY 'YOUR_PASS' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;  

*.* = DB.TABLEyou can restrict user to specific database and specific table.

*.* = DB.TABLE您可以将用户限制为特定的数据库和特定的表。

'root'@'%'you can change root with any user you created and % is to allow all IP. You can restrict it by changing %.168.1.1 etc too.

'root'@'%'您可以使用您创建的任何用户更改 root，% 是允许所有 IP。您也可以通过更改 %.168.1.1 等来限制它。

---

---

If that doesn't resolve, then also modify my.cnf or my.ini and comment these lines

如果这不能解决，那么还要修改 my.cnf 或 my.ini 并注释这些行

bind-address = 127.0.0.1to #bind-address = 127.0.0.1
and
skip-networkingto #skip-networking

bind-address = 127.0.0.1到#bind-address = 127.0.0.1
和
skip-networking到#skip-networking

• Restart MySQL and repeat above steps again.

• 重新启动 MySQL 并再次重复上述步骤。

Raspberry Pi, I found bind-address configuration under \etc\mysql\mariadb.conf.d\50-server.cnf

树莓派，我在下面找到了绑定地址配置 \etc\mysql\mariadb.conf.d\50-server.cnf

回答by user171212

Those SQL grants the others are sharing do work. If you're still unable to access the database, it's possible that you just have a firewall restriction for the port. It depends on your server type (and any routers in between) as to how to open up the connection. Open TCP port 3306 inbound, and give it a similar access rule for external machines (all/subnet/single IP/etc.).

其他人共享的那些 SQL 授权确实有效。如果您仍然无法访问数据库，则可能只是对端口设置了防火墙限制。如何打开连接取决于您的服务器类型（以及中间的任何路由器）。打开入站TCP端口3306，并给它一个类似的外部机器访问规则（所有/子网/单个IP/等）。

回答by Vik2696

Open the /etc/mysql/mysql.conf.d/mysqld.cnffile and comment the following line:

打开/etc/mysql/mysql.conf.d/mysqld.cnf文件并注释以下行：

#bind-address = 127.0.0.1

回答by studio1057

Two steps:

两个步骤：

1. set up user with wildcard:
   create user 'root'@'%' identified by 'some_characters'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'some_characters' WITH GRANT OPTION

2. vim /etc/my.cnf
   add the following:
   bind-address=0.0.0.0

1. 使用通配符设置用户：
   create user 'root'@'%' identified by 'some_characters'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'some_characters' WITH GRANT OPTION

2. vim /etc/my.cnf
   添加以下内容：
   bind-address=0.0.0.0

restart server, you should not have any problem connecting to it.

重新启动服务器，连接到它应该没有任何问题。

回答by Kushal

In my case I was trying to connect to a remote mysql server on cent OS. After going through a lot of solutions (granting all privileges, removing ip bindings,enabling networking) problem was still not getting solved.

就我而言，我试图连接到 Cent OS 上的远程 mysql 服务器。在经历了很多解决方案（授予所有权限、删除 ip 绑定、启用网络）之后，问题仍然没有得到解决。

As it turned out, while looking into various solutions,I came across iptables, which made me realize mysql port 3306 was not accepting connections.

事实证明，在研究各种解决方案时，我遇到了 iptables，这让我意识到 mysql 端口 3306 不接受连接。

Here is a small note on how I checked and resolved this issue.

这是关于我如何检查和解决此问题的小说明。

• Checking if port is accepting connections:

• 检查端口是否接受连接：

telnet (mysql server ip) [portNo]

-Adding ip table rule to allow connections on the port:

- 添加ip表规则以允许端口上的连接：

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

-Would not recommend this for production environment, but if your iptables are not configured properly, adding the rules might not still solve the issue. In that case following should be done:

- 不建议在生产环境中使用此方法，但如果您的 iptables 配置不正确，添加规则可能仍然无法解决问题。在这种情况下，应执行以下操作：

service iptables stop

Hope this helps.

希望这可以帮助。

回答by MhDG7

By mysql 8 and later version, you cannot add a user by granting privileges. it means with this query:

在 mysql 8 及更高版本中，您无法通过授予权限来添加用户。这意味着这个查询：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' 
    IDENTIFIED BY 'type-root-password-here' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql will return this error:

mysql 将返回此错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'written password' at line 1

ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“IDENTIFIED BY 'written password”附近使用的正确语法

this means you don't have a root user for % domain. so you need to first insert the user and then grant privileges like this:

这意味着您没有 % 域的根用户。所以你需要先插入用户，然后像这样授予权限：

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'your password';
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.15 sec)

mysql> FLUSH PRIVILEGES;

Dont forget to replace passwords with your specific passwords.

不要忘记用您的特定密码替换密码。

回答by Peter Prographo

Ubuntu 18.04

Ubuntu 18.04

Install and ensure mysqld us running..

安装并确保 mysqld 我们正在运行..

Go into database and setup root user:

进入数据库并设置root用户：

sudo mysql -u root
SELECT User,Host FROM mysql.user;
DROP USER 'root'@'localhost';
CREATE USER 'root'@'%' IDENTIFIED BY 'obamathelongleggedmacdaddy';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

Edit mysqld permissions and restart:

编辑 mysqld 权限并重新启动：

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

# edit the line to be this:
bind-address=0.0.0.0

sudo systemctl stop mysql
sudo systemctl start mysql

From another machine, test.. Obvs port (3306) on mysqld machine must allow connection from test machine.

在另一台机器上，测试.. mysqld 机器上的 Obvs 端口 (3306) 必须允许来自测试机器的连接。

mysql -u root -p -h 123.456.789.666

All the additional "security" of MySql doesn't help security at all, it just complicates and obfuscates, it is now actually easier to screw it up than in the old days, where you just used a really long password.

MySql 的所有额外“安全性”根本无助于安全性，它只是复杂化和混淆，现在实际上比过去更容易搞砸，过去你只使用了一个非常长的密码。