How can I fix it: Mysql remote access not working

Question

I did this in mysql

USE mysql;

CREATE user 'test'@'%' IDENTIFIED BY 'test';

GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

and in terminal

sudo apt-get install ufw

sudo ufw allow mysql

sudo ufw allow 3306

service mysql restart

and edited mysqld.cnf in /etc/mysql/mysql.conf.d

#bind-address       = 127.0.0.1

but when I try to access mysql server in terminal with this code

mysql -utest -ptest --port 3306 --host [myip]

And this is 3306 port status

Versions of Ubuntu, ufw, mysql? Can you connect without the firewall in place? — Rick James, Mar 06 '19 at 21:33
@RickJames ubuntu: 18.10 / ufw 0.35 / mysql: 5.7.25 and it's same result when I disabled ufw — Busted, Mar 06 '19 at 21:35
@RickJames I think this problem related with my another question. Please check this question. https://stackoverflow.com/questions/55032018/my-port-has-filtered-how-can-i-open-this — Busted, Mar 06 '19 at 21:43

Marc Alff · Answer 1 · 2019-03-07T00:44:14.090

You have at least local access to MySQL, since you could issue a CREATE USER statement.

Using this access, inspect the performance_schema.host_cache table: it lists all the possible root causes for denied connections.

https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.7/en/host-cache-table.html

With this, with status variables, etc, your should be able to figure out whether:

the connection is blocked in the network, not even reaching the server,
the connection is blocked by the server itself, due to various failures affecting authentication.

Try also a simple telnet to port 3306, and see if a connection and some reply data comes back, indicating the server can be reached at least thought the network.

EDIT:

Commenting a bind-address in the cnf file is one thing, but it is better to verify which bind address is actually used by the server:

mysql> show variables like "%bind%";
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| bind_address        | *     |
| mysqlx_bind_address | *     |
+---------------------+-------+
2 rows in set (0.02 sec)

This can affect connections.

Thank you for your answer. But my host_cache table is empty. And I tried telnet command but it's not repond for long time. — Busted, Mar 06 '19 at 23:35
@Busted in the error you got, there is "(110)" at the end, which points to the underlying socket error. 110 is ETIMEDOUT (connection timeout). To confirm, please try shutting down the server to verify, and you should get (111) ECONNREFUSED (connection refused) instead. — Marc Alff, Mar 07 '19 at 00:08
is this your complete my.cnf in the question above - could you post the rest? Is there maybe a "skip-networking" directive set? — macbert, Mar 13 '19 at 09:49

score 0 · Answer 2 · answered Mar 07 '19 at 10:09

0

give apache remote access

Check this answer https://stackoverflow.com/a/55037055/7510232

answered Mar 07 '19 at 10:09

snfrox

124
7

Thank you but I'm not using apache. I'm using just mysql. – Busted Mar 07 '19 at 12:29
are you running db on a VM? – snfrox Mar 08 '19 at 11:41

score 0 · Answer 3 · answered Mar 08 '19 at 15:30

0

Seeing this line indicates MySQL is listening on ipv6:

:::3306

I think MySQL is currently only listening on the ipv6 address (and localhost), so try to set the bind-address to the ipv4 address that you are using.

answered Mar 08 '19 at 15:30

Art van Scheppingen

301
1
5

LIU YUE · Answer 4 · 2019-03-10T03:52:35.953

0

have you tried ping the MySQL server and telnet the port?

:::3306 indicates your MySQL server is listening on both ipv4 and ipv6, if you want to access it through ipv6, then you need to add

bind-address = ::

to support your ipv6 access

edited Mar 10 '19 at 03:52

answered Mar 10 '19 at 03:45

LIU YUE

1,593
11
19

How can I fix it: Mysql remote access not working

4 Answers4