CODEWITHSUNDEEP
mysqlubuntu
Busted
Busted

Reputation: 147

How can I fix it: Mysql remote access not working

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]

enter image description here

And this is 3306 port status

enter image description here

Upvotes: 3

Views: 703

Answers (4)

LIU YUE
LIU YUE

Reputation: 1977

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

Upvotes: 0

Art van Scheppingen
Art van Scheppingen

Reputation: 301

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.

Upvotes: 0

snfrox
snfrox

Reputation: 124

give apache remote access

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

Upvotes: 0

Marc Alff
Marc Alff

Reputation: 8395

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.

Upvotes: 1

Related Questions