169

I installed mysql server on linux box IP = 192.168.1.100 but when i try to connect to this IP it alway error(111). but use localhost and 127.0.0.1 is OK.

beer@beer-laptop# ifconfig | grep "inet addr"
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet addr:192.168.1.100  Bcast:192.168.1.255  Mask:255.255.255.0

beer@beer-laptop# mysql -ubeer -pbeer -h192.168.1.100
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.100' (111)

beer@beer-laptop# mysql -ubeer -pbeer -hlocalhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 160
Server version: 5.1.31-1ubuntu2 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

beer@beer-laptop# mysql -ubeer -pbeer -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 161
Server version: 5.1.31-1ubuntu2 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Connect from another machine it also error 111.

another@another-laptop# mysql -ubeer -pbeer -h192.168.1.100
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.100' (111)

How difference between use localhost/127.0.0.1 and 192.168.1.100 in this case. I don't know how to connect to this database from another machine.

Help please. Thank.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
wearetherock
  • 3,721
  • 8
  • 36
  • 47

4 Answers4

280

It probably means that your MySQL server is only listening the localhost interface.

If you have lines like this :

bind-address = 127.0.0.1

In your my.cnf configuration file, you should comment them (add a # at the beginning of the lines), and restart MySQL.

sudo service mysql restart

Of course, to do this, you must be the administrator of the server.

Rafaf Tahsin
  • 7,652
  • 4
  • 28
  • 45
Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
41

111 means connection refused, which in turn means that your mysqld only listens to the localhost interface.

To alter it you may want to look at the bind-address value in the mysqld section of your my.cnf file.

Michael Krelin - hacker
  • 138,757
  • 24
  • 193
  • 173
  • 1
    It's also worth checking if the port number is valid. Mismatching `:3306` and `:3307` can result in 111 error, too. – NXT Dec 18 '15 at 12:54
  • 2
    @NXT, I don't even know if mysql can listen on different ports on different interfaces, but it's very unlikely scenario (given the symptoms in the original post) even if it can. Then there can be firewall rules, etc. There are many ways to achieve that, but the likelihood differs… – Michael Krelin - hacker Dec 18 '15 at 16:09
  • Doesn't always mean that. My `my.cnf` files do not have any `bind-address` or `skip-networking` lines, and yet I get the same error. No firewall installed either. Ran out of ideas. – CoderGuy123 Oct 14 '16 at 03:49
  • Well, 111 does always mean connection refused :) And the localhost-only is in this particular case due to other evidence. If you provide more details about your case I may be able to help? Also, depending on your system nowadays you're likely to have configuration scattered across multiple files. – Michael Krelin - hacker Oct 14 '16 at 09:29
  • what if i dont have bind-address value in the mysqld ? – Toma Tomov Oct 05 '20 at 17:40
  • Then it gets the default (no idea what it is, you can check the docs). You can add one if there isn't. Again you can check the docs to understand what you're doing prior to doing it. – Michael Krelin - hacker Oct 05 '20 at 21:07
11

If all the previous answers didn't give any solution, you should check your user privileges.

If you could login as root to mysql then you should add this:

CREATE USER 'root'@'192.168.1.100' IDENTIFIED BY  '***';
GRANT ALL PRIVILEGES ON * . * TO  'root'@'192.168.1.100' IDENTIFIED BY  '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Then try to connect again using mysql -ubeer -pbeer -h192.168.1.100. It should work.

Gilles 'SO- stop being evil'
  • 104,111
  • 38
  • 209
  • 254
xnome
  • 145
  • 1
  • 2
  • 20
    i don't think that user privileges related error would give error 111. – ufk Jun 05 '12 at 16:16
  • 1
    It actually worked, and I was getting 111 error too. – Borjante Jul 04 '15 at 20:09
  • looks like this is a better solution than opening up mysql to the entire network(s) by commenting out the 127.0.0.1 line. it should be the best solution. – nyxee Oct 26 '15 at 00:16
8

If you're running cPanel/WHM, make sure that IP is whitelisted in the firewall. You will als need to add that IP to the remote SQL IP list in the cPanel account you're trying to connect to.

0b10011
  • 18,397
  • 4
  • 65
  • 86
Dragos.
  • 89
  • 1
  • 1
  • 3
    A good "tell" to see if it's a firewall causing the error is that it will take quite a while for the error to be returned. If MySQL is causing problems the response should be pretty much instant. – Grim... Jun 26 '17 at 13:34
  • Can you explain how this is done? – User Mar 12 '19 at 19:04