MYSql config bind-address set to 0.0.0.0 but netstat shows different on Ubuntu

Question

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?

Answer 1

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.

Answer 2

I had the same issue: mysql configured correctly, sudo systemctl restart mysql seemed to work fine, but sudo lsof -i -P -n | grep 3306 still gave me TCP 127.0.0.1:3306 (LISTEN) instead of the expected TCP *:3306 (LISTEN), and I noticed from ps aux | grep mysql that /usr/bin/mysqld hadn't actually been restarted.

A full reboot was necessary for my new settings to take effect.