来自所有主机的 MySQL 根访问

MySQL root access from all hosts

提问by lorenzo-s

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

我已经在远程 Ubuntu 机器上安装了 MySQL 服务器。该root用户在定义mysql.user表是这样的：

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 |
+------------------+------+-------------------------------------------+

I can access with user rootfrom the same remote machine command-line interface using the standard mysqlclient. 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 hostcolumn):

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

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

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

但是我个人电脑上的客户端继续告诉我（我隐藏了服务器 IP）：

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

SQL 错误 (2003)：无法连接到“46.xxx”上的 MySQL 服务器 (10061)

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

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

回答by hovanessyan

There's two steps in that process:

这个过程有两个步骤：

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

a) 授予特权。作为 root 用户执行此替换'password'为您当前的 root 密码：

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

b) bind to all addresses:

b) 绑定到所有地址：

The easiest way is to comment outthe line in your my.cnffile:

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

#bind-address = 127.0.0.1 

and restart mysql

并重新启动mysql

service mysql restart

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=*.

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

To check where mysql service has binded execute as root:

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

netstat -tupan | grep mysql

Update For Ubuntu 16:

Ubuntu 16 更新：

Config file is (now)

配置文件是（现在）

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

(at least on standard Ubuntu 16)

（至少在标准 Ubuntu 16 上）

回答by Babu James

Run the following query:

运行以下查询：

use mysql;

use mysql;

update user set host='%' where host='localhost'

update user set host='%' where host='localhost'

NOTE: Not recommended for production use.

注意：不推荐用于生产用途。

回答by user2839051

Sometimes

有时

bind-address = 127.0.0.1

绑定地址 = 127.0.0.1

should be

应该

bind-address = *

绑定地址 = *

回答by user10580057

MYSQL 8.0 - open mysql command line client
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
use mysql
UPDATE mysql.user SET host='%' WHERE user='root';
Restart mysql service

MYSQL 8.0 - 打开 mysql 命令行客户端
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
使用 mysql
UPDATE mysql.user SET host='%' WHERE user='root';
重启mysql服务

回答by The Big Zank

MariaDB running on Raspbian - the file containing bind-address is hard to pinpoint. MariaDB have some not-very-helpful-infoon the subject.

在 Raspbian 上运行的 MariaDB - 很难确定包含绑定地址的文件。MariaDB 有一些关于这个主题的不太有用的信息。

I used

我用了

# sudo grep -R bind-address /etc 

to locate where the damn thing is.

找到该死的东西在哪里。

I also had to set the privileges and hosts in the mysql like everyone above pointed out.

我还必须像上面每个人指出的那样在 mysql 中设置权限和主机。

And also had some fun time opening the 3306 port for remote connections to my Raspberry Pi - finally used iptables-persistent.

并且还有一些有趣的时间打开 3306 端口以远程连接到我的 Raspberry Pi - 最后使用iptables-persistent。

All works great now.

现在一切都很好。

回答by felipe cardenas

if you have many networks attached to you OS, yo must especify one of this network in the bind-addres from my.conf file. an example:

如果您的操作系统连接了许多网络，则必须在 my.conf 文件的绑定地址中指定此网络之一。一个例子：

[mysqld]
bind-address = 127.100.10.234

this ip is from a ethX configuration.

这个 ip 来自 ethX 配置。

回答by iHaveacomputer

In my case the "bind-address" setting was the problem. Commenting this setting in my.cnfdid nothelp, because in my case mysql set the default to 127.0.0.1 for some reason.

就我而言，“绑定地址”设置是问题所在。谈及此设置中my.cnf没有没有帮助，因为在我的情况下，MySQL的默认值设置为127.0.0.1出于某种原因。

To verify what setting MySql is currently using, open the command line on your local box:

要验证 MySql 当前使用的设置，请在本地框上打开命令行：

mysql -h localhost -u myname -pmypass mydb

Read out the current setting:

读出当前设置：

Show variables where variable_name like "bind%"

You should see 0.0.0.0 here if you want to allow access from all hosts. If this is not the case, edit your /etc/mysql/my.cnfand set bind-address under the [mysqld] section:

如果您想允许来自所有主机的访问，您应该在此处看到 0.0.0.0。如果不是这种情况，请/etc/mysql/my.cnf在 [mysqld] 部分下编辑并设置绑定地址：

bind-address=0.0.0.0

Finally restart your MySql server to pick up the new setting:

最后重新启动您的 MySql 服务器以获取新设置：

sudo service mysql restart

Try again and check if the new setting has been picked up.

再试一次并检查是否已选择新设置。

回答by Kieran Foot

mysql_updateis what you need.

mysql_update是你所需要的。

I don't know why anyone would follow the more complex ways of correcting this issue, when MySql graciously built a tool that already does this...

我不知道为什么有人会采用更复杂的方法来纠正这个问题，当 MySql 慷慨地构建了一个已经这样做的工具时......