欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.165' (113) 有大用 有大大用

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.165' (113)

1、程序报错:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link
2、尝试远程连接数据库:
[root@inode1 ~]# mysql -h192.168.1.165 -uroot -p123456
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.1.165’ (113)
3、问题找到了,不能连接远程数据库。

4、测试是否能ping到远程机器

[root@inode1 ~]# ping 192.168.1.165

PING 192.168.1.165 (192.168.1.165) 56(84) bytes of data.
64 bytes from 192.168.1.165: icmp_seq=1 ttl=128 time=0.776 ms
64 bytes from 192.168.1.165: icmp_seq=2 ttl=128 time=1.26 ms
64 bytes from 192.168.1.165: icmp_seq=3 ttl=128 time=1.19 ms
^C
— 192.168.1.165 ping statistics —
3 packets transmitted, 3 received, 0% packet loss, time 2008ms
rtt min/avg/max/mdev = 0.776/1.078/1.268/0.215 ms
说明网络是连通的,再测试以下端口号

5、登录到数据库端

[root@dbServer ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.52-MariaDB MariaDB Server
Copyright © 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> select user,host,password from mysql.user;
±-----±----------±------------------------------------------+
| user | host | password |
±-----±----------±------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
±-----±----------±------------------------------------------+

添加%

MariaDB [(none)]> grant all privileges on . to ‘root’@’%’ identified by ‘123456’;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

再次远程访问:

[root@hadron ~]# mysql -h192.168.1.165 -uroot -p123456
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.1.165’ (113)

6、还是无法连接到数据库,可能不能访问端口号,再次测试

[root@hadron ~]# telnet 192.168.1.165 3306
Trying 192.168.1.165…
telnet: connect to address 192.168.1.165: No route to host

找到问题所在了,防火墙屏蔽了这个端口号。

7、直接关闭防火墙即可。

[root@dbServer ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.
[root@dbServer ~]# service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service

再次远程访问

[root@hadron ~]# telnet 192.168.1.165 3306
Trying 192.168.1.165…
telnet: connect to address 192.168.1.165: No route to host
[root@hadron ~]# mysql -h192.168.1.165 -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.52-MariaDB MariaDB Server

Copyright © 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

成功了。

【2018年01月30日再次遇到这个问题】

在ubuntu下安装MariaDB 10,遇到远程无法登录

root@kylin3:/etc/mysql/mariadb.conf.d# mysql -uroot -p123456 -h 192.168.1.161
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.1.161’ (111 “Connection refused”)

按照之前的方法操作一遍,还是没有解决问题。

这个问题又百度了一个下午,掉坑了。

静心再看看,喜出望外:

查看端口号,发现3306对应的地址依然时127.0.0.1,修改没有起作用

root@kylin1:/etc/mysql# netstat -npl|grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 9191/mysqld

可能进程出问题了,直接kill掉,再启动

root@kylin1:/etc/mysql# kill -9 9191
root@kylin1:/etc/mysql# netstat -npl|grep 3306
root@kylin1:/etc/mysql# systemctl restart mysql
再次查看端口号,正常了
root@kylin1:/etc/mysql# netstat -npl|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 23476/mysqld
root@kylin1:/etc/mysql#

也可以远程登录了。

ERROR 2003 (HY000): Can’t connect to MySQL server问题

解决办法总结:

1、确定远程机器的防火墙关闭,或在防火墙允许3306端口号

2、确定数据库允许远程访问,通过语句grant all privileges on . to ‘root’@’%’ identified by '123456’即可。

3、如果上面方法还是没有解决。直接kill进程,重启。

作者:程裕强
来源:CSDN
原文:https://blog.csdn.net/chengyuqiang/article/details/54285857
版权声明:本文为博主原创文章,转载请附上博文链接!


来自  https://blog.csdn.net/Camille66/article/details/89358946


普通分类: