ERROR 2003 (HY000): Can't connect to MySQL server (111)

Question

This question is related to the following questions:

I am configuring a new MySQL (5.1) server on my local machine. I need to provide remote access to the database. I did the following steps:

Comment bind-address in my.cnf:
```
# bind-address      = 192.168.1.3
```

Grant privileges:

GRANT ALL PRIVILEGES ON *.* TO 'nickruiz'@'%' IDENTIFIED BY PASSWORD 'xxxx';

Set port forwarding on router (TCP and UDP, port 3306, 192.168.1.3)

Configure iptables for firewall

sudo iptables -I INPUT -p udp --dport 3306 -j ACCEPT

sudo iptables -I INPUT -p tcp --dport 3306 --syn -j ACCEPT

sudo iptables-save

Restart mysql server sudo /etc/init.d/mysql restart

When testing, I get the following:

LAN:

mysql -h 192.168.1.3 -u nickruiz -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 95
Server version: 5.1.63-0ubuntu0.11.04.1 (Ubuntu)

Remote:

mysql -h 1xx.xx.4.136 -u nickruiz -p
ERROR 2003 (HY000): Can't connect to MySQL server on '1xx.xx.4.136' (111)

Clearly there's something wrong that's preventing me from being able to use my global IP address.

Notes:

I've tried testing the remote connection on the same machine and also via SSH from a remote machine.
I'm not sure if my ISP has given me a static IP.

Any ideas?

Update: telnet doesn't seem to be working.

telnet 192.168.1.3 3306
Trying 192.168.1.3...
Connected to 192.168.1.3.
Escape character is '^]'.
E
5.1.63-0ubuntu0.11.04.1,0g8!:@pX;]DyY0#\)SIConnection closed by foreign host.

And `telnet 192.168.1.3 3306`. If it opens any sort of prompt, the the port is listening and accepting connections. — Michael Berkowski, Aug 01 '12 at 12:19
Actually, that is telnet working properly. If you get the connected and `Escape character is ^]`, then you have _successfully_ established a connection. You won't see a MySQL prompt, but you have a raw connection to the MySQL port so it is correctly listening on that address! — Michael Berkowski, Aug 01 '12 at 15:59
But I missed that you were trying to do it with your public IP. Try the same with the public IP and it probably won't work. Is this a residential ISP? It is possible that your ISP blocks 3306 entirely... — Michael Berkowski, Aug 01 '12 at 16:00
The answer has been given here: http://stackoverflow.com/a/16164426/1768736 — FBB, Dec 08 '14 at 12:01
I figured out that the "bind-address" parameter is in the "/etc/mysql/mysql.conf.d/mysqld.cnf" file. Commented it out over there and it worked! — Yahya, Aug 17 '17 at 07:38

Toi Lee · Answer 1 · 2014-10-27T05:33:11.457

16

Please check your listenning ports with :

netstat -nat |grep :3306

If it show

 tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

Thats is ok for your remote connection.

But in this case i think you have

tcp        0     192.168.1.3:3306            0.0.0.0:*               LISTEN

Thats is ok for your remote connection. You should also check your firewall (iptables if you centos/redhat)

services iptables stop

for testing or use :

iptables -A input -p tcp -i eth0 --dport 3306 -m state NEW,ESTABLISHED -j ACCEPT
iptables -A output -p tcp -i eth0 --sport 3306 -m state NEW,ESTABLISHED -j ACCEPT

And another thing to check your grant permission for remote connection :

GRANT ALL ON *.* TO remoteUser@'remoteIpadress' IDENTIFIED BY 'my_password';

edited Oct 27 '14 at 05:33

answered Oct 27 '14 at 05:27

Toi Lee

171
2
15

3

If you do not see '0.0.0.0:3306' when doing the netstat command, see the answer provided here: http://stackoverflow.com/a/16164426/1768736 – FBB Dec 08 '14 at 12:02
`192.168.1.3` is part of the 16-bit block of [private](https://en.wikipedia.org/wiki/Private_network) IPv4 address spaces, so not very remote unless there's a VPN. – Cees Timmerman Nov 12 '15 at 15:31

score 6 · Answer 2 · answered Aug 01 '12 at 12:00

6

errno 111 is ECONNREFUSED, I suppose something is wrong with the router's DNAT.

It is also possible that your ISP is filtering that port.

answered Aug 01 '12 at 12:00

InternetSeriousBusiness

2,605
16
17

score 5 · Answer 3 · answered Feb 04 '13 at 15:53

Check that your remote host (i.e. the web hosting server you're trying to connect FROM) allows OUTGOING traffic on port 3306.

I saw the (100) error in this situation. I could connect from my PC/Mac, but not from my website. The MySQL instance was accessible via the internet, but my hosting company wasn't allowing my website to connect to the database on port 3306.

Once I asked my hosting company to open my web hosting account up to outgoing traffic on port 3306, my website could connect to my remote database.

This was the issue for me, the outbound port was closed on the client machine — Moad Ennagi, Jun 25 '22 at 08:04

score 3 · Answer 4 · answered Nov 12 '15 at 15:24

/etc/mysql$ sudo nano my.cnf

Relevant portion that works for me:

#skip-networking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = MY_IP

MY_IP can be found using ifconfig or curl -L whatismyip.org |grep blue.

Restart mysql to ensure the new config is loaded:

/etc/mysql$ sudo service mysql restart

score 2 · Answer 5 · answered Jun 29 '17 at 19:50

2

I had the same problem trying to connect to a remote mysql db.

I fixed it by opening the firewall on the db server to allow traffic through:

sudo ufw allow mysql

answered Jun 29 '17 at 19:50

Dan King

564
10
18

score 0 · Answer 6 · answered Jun 16 '14 at 07:47

0

if the system you use is CentOS/RedHat, and rpm is the way you install MySQL, there is no my.cnf in /etc/ folder, you could use: #whereis mysql #cd /usr/share/mysql/ cp -f /usr/share/mysql/my-medium.cnf /etc/my.cnf

answered Jun 16 '14 at 07:47

jammyWolf

2,743
1
13
7

score 0 · Answer 7 · answered Dec 24 '15 at 05:23

I have got a same question like you, I use wireshark to capture my sent TCP packets, I found when I use mysql bin to connect the remote host, it connects remote's 3307 port, that's my falut in /etc/mysql/my.cnf, 3307 is another project mysql port, but I change that config in my.cnf [client] part, when I use -P option to specify 3306 port, it's OK.

score 0 · Answer 8 · answered Apr 02 '17 at 15:08

0

i set my bind-address correctly as above but forgot to restart the mysql server (or reboot) :) face palm - so that's the source of this error for me!

answered Apr 02 '17 at 15:08

bsautner

4,479
1
36
50

maszynka · Answer 9 · 2017-09-04T11:22:22.287

0

Sometimes when you have special characters in password you need to wrap it in '' characters, so to connect to db you could use:

mysql -uUSER -p'pa$$w0rd'

I had the same error and this solution solved it.

edited Sep 04 '17 at 11:22

answered Sep 04 '17 at 00:26

maszynka

196
4
11

score 0 · Answer 10 · answered Oct 14 '20 at 17:12

I had this same error and I didn't understand but I realized that my modem was using the same port as mysql. Well, I stop apache2.service by sudo systemctl stop apache2.service and restarted the xammp, sudo /opt/lampp/lampp start

Just maybe, if you were not using a password for mysql yet you had, 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES), then you have to pass an empty string as the password

score -1 · Answer 11 · answered Aug 01 '12 at 12:17

-1

Not sure as cant see it in steps you mentioned.

Please try FLUSH PRIVILEGES [Reloads the privileges from the grant tables in the mysql database]:

flush privileges;

You need to execute it after GRANT

Hope this help!

answered Aug 01 '12 at 12:17

metalfight - user868766

2,722
1
16
20

ERROR 2003 (HY000): Can't connect to MySQL server (111)

11 Answers11

Linked