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

35
votes

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:

  1. Comment bind-address in my.cnf: 

    # bind-address      = 192.168.1.3

  2. Grant privileges:

    GRANT ALL PRIVILEGES ON *.* TO 'nickruiz'@'%' IDENTIFIED BY PASSWORD 'xxxx';
  3. Set port forwarding on router (TCP and UDP, port 3306, 192.168.1.3)

  4. 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

  5. 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.
11
mysqllinuxremote-accessmysql-error-2003
You don't have skip-networking in your my.cnf, do you? – Michael Berkowski
And telnet 192.168.1.3 3306. If it opens any sort of prompt, the the port is listening and accepting connections. – Michael Berkowski
No skip-networking in my.cnf. – Nick Ruiz
Telnet didn't work. See above. – Nick Ruiz
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

11 Answers

16
votes

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';
6
votes

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.

4
votes

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.

3
votes
/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
0
votes

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

0
votes

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.

0
votes

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!

0
votes

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
0
votes

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.

0
votes

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

-1
votes

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!