Linux 错误 2003 (HY000):无法连接到 MySQL 服务器 (111)
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/11758339/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR 2003 (HY000): Can't connect to MySQL server (111)
提问by Nick Ruiz
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:
我正在我的本地机器上配置一个新的 MySQL (5.1) 服务器。我需要提供对数据库的远程访问。我做了以下步骤:
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
bind-address
在 my.cnf 中评论:# bind-address = 192.168.1.3
授予特权:
GRANT ALL PRIVILEGES ON *.* TO 'nickruiz'@'%' IDENTIFIED BY PASSWORD 'xxxx';
- 在路由器上设置端口转发(TCP 和 UDP,端口 3306、192.168.1.3)
为防火墙配置 iptables
sudo iptables -I INPUT -p udp --dport 3306 -j ACCEPT sudo iptables -I INPUT -p tcp --dport 3306 --syn -j ACCEPT sudo iptables-save
重启mysql服务器
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.
很明显,有什么问题阻止了我使用我的全球 IP 地址。
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.
- 我试过在同一台机器上测试远程连接,也试过从远程机器通过 SSH 测试。
- 我不确定我的 ISP 是否给了我一个静态 IP。
Any ideas?
有任何想法吗?
Update: telnet doesn't seem to be working.
更新:telnet 似乎不起作用。
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.
回答by InternetSeriousBusiness
errno 111 is ECONNREFUSED, I suppose something is wrong with the router's DNAT.
errno 111 是 ECONNREFUSED,我想路由器的 DNAT 有问题。
It is also possible that your ISP is filtering that port.
您的 ISP 也可能正在过滤该端口。
回答by metalfight - user868766
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[从 mysql 数据库中的授权表重新加载权限]:
flush privileges;
刷新权限;
You need to execute it after GRANT
您需要在 GRANT 之后执行它
Hope this help!
希望这有帮助!
回答by NickGPS
Check that your remote host (i.e. the web hosting server you're trying to connect FROM) allows OUTGOING traffic on port 3306.
检查您的远程主机(即您尝试连接的网络托管服务器)是否允许端口 3306 上的 OUTGOING 流量。
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.
我在这种情况下看到了 (100) 错误。我可以从我的 PC/Mac 连接,但不能从我的网站连接。MySQL 实例可通过 Internet 访问,但我的托管公司不允许我的网站连接到端口 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.
一旦我要求我的托管公司在端口 3306 上打开我的网络托管帐户以发送流量,我的网站就可以连接到我的远程数据库。
回答by jammyWolf
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
如果您使用的系统是CentOS/RedHat,并且您安装MySQL的方式是rpm,则/etc/文件夹中没有my.cnf,您可以使用:#whereis mysql #cd /usr/share/mysql/ cp -f /usr/share/mysql/my-medium.cnf /etc/my.cnf
回答by Toi Lee
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)
这对您的远程连接没问题。你还应该检查你的防火墙(如果你是 centos/redhat 则是 iptables)
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';
回答by Cees Timmerman
/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
.
MY_IP
可以使用ifconfig
或找到curl -L whatismyip.org |grep blue
。
Restart mysql to ensure the new config is loaded:
重新启动 mysql 以确保加载新配置:
/etc/mysql$ sudo service mysql restart
回答by dawncold
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.
我也有和你一样的问题,我用wireshark抓我发的tcp包,发现用mysql
bin连接远程主机的时候,连接的是远程的3307端口,这是我的问题/etc/mysql/my.cnf
,3307是另一个项目mysql端口,但是我在my.cnf
[client] 部分更改了该配置,当我使用-P
选项指定 3306 端口时,就可以了。
回答by bsautner
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!
我按照上面正确设置了我的绑定地址,但忘记重新启动 mysql 服务器(或重新启动):) face palm- 所以这对我来说是这个错误的根源!
回答by Dan King
I had the same problem trying to connect to a remote mysql db.
我在尝试连接到远程 mysql 数据库时遇到了同样的问题。
I fixed it by opening the firewall on the db server to allow traffic through:
我通过打开数据库服务器上的防火墙以允许流量通过来修复它:
sudo ufw allow mysql
回答by maszynka
Sometimes when you have special characters in password you need to wrap it in ''
characters, so to connect to db you could use:
有时,当密码中有特殊字符时,您需要将其包装在''
字符中,因此要连接到 db,您可以使用:
mysql -uUSER -p'pa$$w0rd'
mysql -uUSER -p'pa$$w0rd'
I had the same error and this solution solved it.
我有同样的错误,这个解决方案解决了它。