无法连接到MySQL服务器错误111

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.

我在linux box IP = 192.168.1.100上安装了mysql服务器，但是当我尝试连接到这个IP时，它总是出错(111)。但是使用localhost和127.0.0.1就可以了。

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.

从另一台机器连接，它也错误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.

在本例中，如何使用localhost/127.0.0.1和192.168.1.100。我不知道如何从另一台机器连接到这个数据库。

Help please. Thank.

请帮助。谢谢。

4 个解决方案

#1

223

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

这可能意味着MySQL服务器只监听本地主机接口。

If you have lines like this :

如果你有这样的句子:

skip-networking
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.

在my.cnf配置文件中，应该对它们进行注释(在行首添加#)，然后重新启动MySQL。

sudo service mysql restart

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

当然，要做到这一点，您必须是服务器的管理员。

#2

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

111表示拒绝连接，这反过来意味着您的sqld只监听localhost接口。

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

要修改它，您可能需要查看my.cnf文件的mysqld部分中的绑定地址值。

#3

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:

如果你可以作为root登录到mysql，那么你应该添加以下内容:

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.

然后尝试再次使用mysql -ubeer -pbeer -h192.168.1.100连接。它应该工作。

#4

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.

如果您正在运行cPanel/WHM，请确保在防火墙中使用了IP。需要将该IP添加到要连接的cPanel帐户中的远程SQL IP列表中。

#1

223