来自所有主机的 MySQL 根访问 [英] MySQL root access from all hosts

问题描述

我已经在远程 Ubuntu 机器上安装了 MySQL 服务器.root 用户在 mysql.user 表中定义如下:

I've installed MySQL server on a remote Ubuntu machine. The root user is defined in the mysql.user table this way:

mysql> SELECT host, user, password FROM user WHERE user = 'root';
+------------------+------+-------------------------------------------+
| host             | user | password                                  |
+------------------+------+-------------------------------------------+
| localhost        | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| ip-10-48-110-188 | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| 127.0.0.1        | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| ::1              | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+------------------+------+-------------------------------------------+

我可以使用标准 mysql 客户端从同一个远程机器命令行界面使用用户 root 进行访问.现在我想允许来自互联网上每个主机的 root 访问，所以我尝试添加以下行(它是上一个转储中第一行的完全重复，除了 host 列):

I can access with user root from the same remote machine command-line interface using the standard mysql client. Now I want to allow root access from every host on the internet, so I tried adding following row (it's an exact duplicate of the first row from previous dump, except for the host column):

mysql> SELECT host, user, password FROM user WHERE host = '%';
+------------------+------+-------------------------------------------+
| host             | user | password                                  |
+------------------+------+-------------------------------------------+
| %                | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+------------------+------+-------------------------------------------+

但我个人电脑上的客户端继续告诉我(我隐藏了服务器 IP):

But my client on my personal PC continues to tell me (I obscured the server IP):

SQL 错误 (2003):无法连接到46.x.x.x"上的 MySQL 服务器 (10061)

SQL Error (2003): Can't connect to MySQL server on '46.x.x.x' (10061)

我不知道这是身份验证错误还是网络错误.在服务器防火墙上，我为 0.0.0.0/0 启用了端口 3306/TCP，这对我来说没问题...

I can't tell if it's a authentication error or a network error. On the server firewall I enabled port 3306/TCP for 0.0.0.0/0, and that's ok for me...

推荐答案

更新:

正如评论中提到的，从 MySql 8 开始你需要首先显式地创建用户，所以命令看起来像:

As mentioned in the comments, since MySql 8 you need to first explicitly create the user, so the command will look like:

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

原答案:

这个过程有两个步骤:

a) 授予权限.作为 root 用户执行此替换 'password' 与您当前的 root 密码:

a) Grant privileges. As root user execute with this substituting 'password' with your current root password :

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

b) 绑定到所有地址:

b) bind to all addresses:

最简单的方法是注释my.cnf文件中的行:

The easiest way is to comment out the line in your my.cnf file:

#bind-address = 127.0.0.1 

并重启mysql

service mysql restart

默认情况下，它只绑定到本地主机，但如果您注释该行，它将绑定到它找到的所有接口.注释掉该行相当于 bind-address=*.

By default it binds only to localhost, but if you comment the line it binds to all interfaces it finds. Commenting out the line is equivalent to bind-address=*.

要检查 mysql 服务绑定的位置，请以 root 身份执行:

To check where mysql service has binded execute as root:

netstat -tupan | grep mysql

Ubuntu 16 更新:

配置文件是(现在)

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

(至少在标准 Ubuntu 16 上)

(at least on standard Ubuntu 16)

这篇关于来自所有主机的 MySQL 根访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！