MYSql 配置绑定地址设置为 0.0.0.0 但 netstat 在 Ubuntu 上显示不同

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

在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 及更高版本.密码是散列的.主机是通配符 % 表示从任何主机登录.

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.

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

and then this my.cnf is also linked:

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

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

I stop/start the MYSQL server.

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 

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

What am I doing wrong?

解决方案

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

And a server restart.

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

You can see your current users with select user,host from mysql.user

Please see the MySQL Manual page on GRANT Syntax.

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

An illustration for a test is below:

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'@'%';

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.

View user in mysql.user:

Concerning the above picture,

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

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

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.