failed to connect remote mysql server

Question

Failed to connect remote mysql server (CentOS), I have tried lots of methods but no help.

I can ssh the remote host successfully, and the port 3306 is listenning:

[root@fabulous ~]# netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      9979/mysqld

I have closed the iptables:

[root@fabulous ~]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]

Mysql client on remote server works fine, and access on every host is allowed:

mysql> select user,host from user;
+-------+--------------------+
| user  | host               |
+-------+--------------------+
| root  | %                  |
| root  | 127.0.0.1          |
| root  | fabulous.ma2oo.com |
| root  | localhost          |
+-------+--------------------+
4 rows in set (0.00 sec)

Global configuration for mysql:

[root@fabulous ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
default-character-set=utf8

However, when I access the mysql server with Navicat on my windows laptop the error message is :

2003 - Can't connect to MySql server on 'XX.XX.XX.XX' (10038)

When I use telnet to access corresponding port on that server the error message is :

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

Could anyone know about it or let me know how the find the useful log ? Thanks in advance.

Answer 1

I think your Navicat or your windows os are not ok.

In my localhost,

songguo@songuo:~$ telnet 107.170.239.240 3306
Trying 107.170.239.240...
Connected to 107.170.239.240.
Escape character is '^]'.
4
5.1.73
^^=Vxyw1P"Ue}ffL+.G
Connection closed by foreign host.
songguo@songuo:~$ mysql -uroot -h107.170.239.240 -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'211.151.238.51' (using      password: YES)

So, if I have the correct password, I can connect to the mysql server.

Answer 2

It looks like Port 3306 is being blocked by a firewall or some other network device between you and your CentOS server.

I’d recommend that you connect to the MySQL server through an SSH connection so that you're connecting to the server as a localhost user. Using SSH should allow you to connect from your office while also providing an encrypted connection and protection from potential man-in-the-middle attacks. I haven’t ever used Navicat but the connection settings MySQL Workbench make it easy to connect using SSH.

In the interest of security, you should also re-enable iptables on your server and block external network access to Port 3306 so that only localhost users (including those using an SSH tunnel) can connect.

As an aside, you could also enable SSL to connect securely to the server but the logistics of creating, signing and distributing the private keys and public certificates to server and all clients make it harder to implement. Also, connecting via SSL uses the same TCP port (3306) which doesn’t help the case where traffic to that port is being blocked.