MYSql配置绑定地址设置为0.0.0.0，但netstat在Ubuntu上显示不同 [英] MYSql config bind-address set to 0.0.0.0 but netstat shows different on Ubuntu

问题描述

关注此线程.我已经成功编辑my.cnf文件以注释掉#bind-address值，并且还尝试指定0.0.0.0和我要允许连接的特定IP地址.

Following this thread. I've successfully edited my my.cnf file to comment out the #bind-address value and also tried to specify 0.0.0.0 and the specific IP address I want to allow connections.

我确实注意到在执行此操作时，my.cnf的链接方式如下:

I do notice that while doing this my.cnf is linked like:

lrwxrwxrwx   1 root root    24 Sep  9 06:21 my.cnf -> /etc/alternatives/my.cnf

然后此my.cnf也被链接:

and then this my.cnf is also linked:

lrwxrwxrwx 1 root root 20 Sep  9 06:23 my.cnf -> /etc/mysql/mysql.cnf

所以实际上，我正在编辑mysql.cnf文件.

So in reality, I'm editing the mysql.cnf file.

我停止/启动MYSQL服务器.

I stop/start the MYSQL server.

然后我运行netstat -nat | grep :3306，它给了我一个

I then run the netstat -nat | grep :3306 and it gives me a:

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN 

然后我转到客户端并在"server_name"上获取无法连接到MySQL服务器"

I then go over to the client and get the Can't connect to MySQL server on 'server_name'

我做错了什么?

推荐答案

与0.0.0.0的绑定地址只是允许其接受远程连接的步骤的一部分.这些步骤包括使用# skip-networking

the bind address to 0.0.0.0 is just part of the steps for allowing it to accept remote connections. Those steps include the rem'ing out explicitly with # skip-networking

[mysqld]
bind-address    = 0.0.0.0
# skip-networking

然后服务器重新启动.

然后，您需要一个用户，主机组合进行登录，并且理想情况下，该用户需要对数据库的GRANT才能具有足够的权限(而不是过多的权限)使用.

You then need a user,host combo for login and ideally a GRANT to a db to use with adequate (not excessive) rights.

您可以使用select user,host from mysql.user

请参见 GRANT语法上的MySQL手册页.

Please see the MySQL Manual page on GRANT Syntax.

我在这里写了一个有关通配符%主机和其他次要详细信息的答案.

I wrote a little answer Here about the wildcard % host and other minor details.

下面是测试的图示:

create schema testDB007;
use testDB007;

create table t1
(   id int not null
);

CREATE USER 'jeffrey123z'@'%' IDENTIFIED BY 'mypass123^';
-- note password is mypass123^

GRANT ALL ON testDB007.* TO 'jeffrey123z'@'%';
SHOW GRANTS FOR 'jeffrey123z'@'%';

现在，上方的蓝色行(USAGE)意味着用户只能登录而已，仅此而已.第二行显示GRANT cmd中数据库的PRIVILEGES.

Now, the blue row above (USAGE) means almost nothing other than the user can login and that is it. The 2nd row shows the PRIVILEGES for the db from the GRANT cmd.

在mysql.user中查看用户:

关于上图，

select user,host,password from mysql.user where user='jeffrey123z';

select user,host,authentication_string from mysql.user where user='jeffrey123z';

上面的第一个查询是针对MySQL 5.7之前的版本.第二个查询是针对5.7及更高版本.密码是散列的.主机是通配符%，表示可以从任何主机登录.

The first query above is for prior to MySQL 5.7. The second query is for 5.7 and after. The password is hashed. The host is the wildcard % meaning login from any host.

这篇关于MYSql配置绑定地址设置为0.0.0.0，但netstat在Ubuntu上显示不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！