ERROR 2003 (HY000): Can't connect to MySQL server on 'ip-address' (111)

andrew2110 · 08-20-2018, 03:56 PM

Hello!

The internets got a lot of help for this error I've been having so have been able to try quite a few steps but am still at a loss so here we are. The issue is I can't connect to my mysql server from a remote IP address.

Steps taken so far

1. make sure bind address is set to 0.0.0.0 in /etc/mysql/mysql.conf.d/mysqld.cnf
2. make sure port 3306 is open and accessible on the server (https://www.yougetsignal.com/tools/open-ports/ says (80.0.21.46 is accessible on port 3306)
3. Forward port 3306 from the router to the ip address of the server in my LAN:

http://80.0.21.46/portforwarding.png

4. Make sure that the user I'm connecting as to mysql has privileges to access from remote IP addresses:

+---------------------------------------------------------------+
| Grants for myusername@% |
+---------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------+

Whilst on the local area network, I can telnet into the machine on port 3306 using the public IP address, however when I try it from a machine outside of the local area network I get the can't connect to mysql server and am not too sure what to try next.

andrew2110 · 08-21-2018, 05:50 AM

An update to this, when connecting from a different remote server, it actually allows it to connect which leads me to believe that my machine is just blocking the IP address of my main server. I'm running elementary OS and am not sure at all where to find whether this is the case or not. The firewall is turned off in settings and I've tried removing all rules from iptables. There's no chance that on my remote server its just blocking me trying to connect to mysql on my home server is there?

Habitual · 08-21-2018, 08:55 AM

Connects from here:

Code:

telnet 80.0.21.46 3306
Trying 80.0.21.46...
Connected to 80.0.21.46.
Escape character is '^]'.
[
5.7.23-0ubuntu0.16.04.1tF:1u|@ T8jumysql_native_passwordConnection closed by foreign host.

andrew2110 · 08-21-2018, 09:13 AM

Thank you, my main server blocking any data transfer on 3306 is what has turned out to be the issue here. I've opened that port on the server and now I can access the home server from it

Habitual · 08-21-2018, 06:29 PM

Quote:

Originally Posted by andrew2110

Code:

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

I can't begin to tell you of the inherent risks associated with wildcard hosts in a grant statement such as yours.
Literally, anyone w\your username and password can access your db without restriction, from any host ('%')

Quote:

Originally Posted by andrew2110

I've opened that port on the server

If your home system is the workhorse, and that is where you'll connect from,
get your forward-facing IP and use that in your grant statement. That is my suggestion.

whatismyip.com or 100 others. Let's assume yours comes back with
121.212.112.221

The grant statement to allow your computer from "home" is

Code:

GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'121.212.112.221' WITH GRANT OPTION ;

and some ufw goodness:

Code:

ufw deny 3306
ufw INSERT 1 allow from 121.212.112.221 to any port 3306

Scans via nmap come back as "Filtered", except if the nmap port scan comes from 121.212.112.221 in which case it will say OPEN
And you should be better "set"
Good Luck.