Resolved Remote connection in MariaDB

[BrinsleyP]

Hello to every...

Today i deploy a new server running CentOS 8 and Plesk 18.0.31 Update #1 and i set to permit remote access in SQL ( MariaDB 10.3.17 )

firewall and permit ( allow ) remote connection
in server settings i set to => Allow remote connections from any host
and in user ( customers ) i set => Allow remote connections from any host

In /etc/my.cnf i set bind-address = 0.0.0.0

But i cant connect remotly in SQL.

So how to fix this?

 

[IgorG]

Have you tried to use instruction from this KB article How to enable remote access to MySQL server in Plesk? ?

 

[Bitpalast]

Frequent misconfigurations and error:
- Customer does not restart the database server after applying the change in /etc/my.cnf, so the new bind is not activated.
- Wrong user name, wrong password, wrong database name. Mostly minor glitches like a "_" instead of a "-" etc. Check your credentials closely.

 

[BrinsleyP]

Have you tried to use instruction from this KB article How to enable remote access to MySQL server in Plesk? ?

Hello, i try this too.

bind in 0.0.0.0 and ::
i try execute too the sh [ remotemysql.sh ] file remotemysql.sh to take some diferent action but same result.

i cant connect to server, i try too create a new db and new user but without effect :/

 

[BrinsleyP]

Frequent misconfigurations and error:
- Customer does not restart the database server after applying the change in /etc/my.cnf, so the new bind is not activated.
- Wrong user name, wrong password, wrong database name. Mostly minor glitches like a "_" instead of a "-" etc. Check your credentials closely.

All these elemetary steps are make
restart db server
confirm users and pass

 

[Bitpalast]

What is the exact error message when you try to connect?

 

[BrinsleyP]

What is the exact error message when you try to connect?

Cant connect to the host... Or host is unvaliable and etc.

I confim, the port 3306 its allow in firewall

i try heidisql and sql workbench and navicat too always cant connect

In attach files i send print about all itens checked..

in my.cnf i try comment, :: , 0.0.0.0

in db server allow all
in user config allow all

and in iptables 3306 its allow

but in some 3 apps same result i cant connect, and confirm, in anothers server i can connect and works fine.

 

[Bitpalast]

Does your server have a firewall before the server? Some data centers do that, e.g. OVH. In that case you'll need to allow port 3306 to pass in that firewall, too. Their default setting is to only allow some basic ports like web traffic and ftp. This is a firewall outside Plesk and outside your server. It can be configured in the data center's customer login panel.

The same needs to be check on the requestor, because if the port is blocked for some reason on the request side, it won't work either. Some routers block it, some "Internet Security" solutions block it.

In the screenshots that web address of the server that you try to connect too looks quite short. Are you sure that this is a valid address?

I also suggest to test the connection from the local console once again and from another server's console (if available):
# mysql -u <user name> -p -h <database server name or ip address>
to rule out issues with the software that you are using.

 

[BrinsleyP]

Does your server have a firewall before the server? Some data centers do that, e.g. OVH. In that case you'll need to allow port 3306 to pass in that firewall, too. Their default setting is to only allow some basic ports like web traffic and ftp. This is a firewall outside Plesk and outside your server. It can be configured in the data center's customer login panel.

The same needs to be check on the requestor, because if the port is blocked for some reason on the request side, it won't work either. Some routers block it, some "Internet Security" solutions block it.

In the screenshots that web address of the server that you try to connect too looks quite short. Are you sure that this is a valid address?

I also suggest to test the connection from the local console once again and from another server's console (if available):
# mysql -u <user name> -p -h <database server name or ip address>
to rule out issues with the software that you are using.

Hello

I try this from another server in the same DC, and i can connect in db on this another server, but from this another server to the plesk server i cant, the msg is

ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (113)

i check for firewall in network, plus check too in internal firewal and nothing, all ports are acessible, i think this is a issue about centos8+plesk

 

[Bitpalast]

So we know that it is not an issue with access credentials. The connection itself is failing. Let's investigate further into the ports.

a) What's the output of these on the machine on that the database server is running?
# lsof -i -P -n | grep LISTEN | grep 3306
# netstat -tulpn | grep LISTEN | grep 3306
# ss -tulw | grep mysql

b) From another system, can you telnet to port 3306, e.g. from your local Windows computer, can you
> telnet <your ip> 3306
?

 

[BrinsleyP]

hello, the tests is;

[root@server ~]# lsof -i -P -n | grep LISTEN | grep 3306
mysqld 103274 mysql 92u IPv6 2412271 0t0 TCP *:3306 (LISTEN)

[root@server ~]# netstat -tulpn | grep LISTEN | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 103274/mysqld

[root@server ~]# ss -tulw | grep mysql
tcp LISTEN 0 80 *:mysql *:*

and my telne test is;

C:\>telnet xxx.xxx.xxx.xxx 3306
Connecting To xxx.xxx.xxx.xxx...Could not open connection to the host, on port 3306: Connect failed
C:\>

 

[Bitpalast]

This must be a firewall issue of a firewall outside your server that is closed for port 3306. Have you contacted your data center on this issue?

 

[BrinsleyP]

This must be a firewall issue of a firewall outside your server that is closed for port 3306. Have you contacted your data center on this issue?

Yes, im not firewalled...
In the same DC i take anothers servers, in and in all cases i can connect in remote databases. but in these especific server not.
the only diference is centos8 in this new server. because in others server i use centos7 and debian 9 and works fine.

 

[Bitpalast]

Your screenshots show iptables, but are you maybe running firewalld on your system? Have you checked
firewall-cmd --list-ports
if this lists port 3306 as open, too?

 

[mow]

From what I see in your lsof & netstat, your mysqld binds to IPv6 only, no idea how you managed to do that. So no wonder you can't connect to xxx.xxx.xxx.xxx port 3306. You'd have to connect to xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx ...