问题描述
我已经在远程 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 根访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！