[trouble shooting] ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.107' (113)
[root@centos6 data]# mysql --host=192.168.1.107 --protocol=tcp --user=root --password
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.107' (113)
[root@centos6 data]# ping 192.168.1.107
PING 192.168.1.107 (192.168.1.107) 56(84) bytes of data.
64 bytes from 192.168.1.107: icmp_seq=1 ttl=64 time=0.455 ms
64 bytes from 192.168.1.107: icmp_seq=2 ttl=64 time=1.87 ms
64 bytes from 192.168.1.107: icmp_seq=3 ttl=64 time=0.498 ms
64 bytes from 192.168.1.107: icmp_seq=4 ttl=64 time=0.465 ms
能够ping说明防火墙是没有阻拦的，在107机器上的用户
mysql> select user,host,password from mysql.user;
+---------+-----------------------+-------------------------------------------+
| user | host | password |
+---------+-----------------------+-------------------------------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| repl | % | *809534247D21AC735802078139D8A854F45C31F3 |
| lijunda | % | *A6269BA24FFF4F9E84F753B3A28E7262B13FC50B |
| root | % | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+---------+-----------------------+-------------------------------------------+
9 rows in set (0.00 sec)
权限也是有的
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@% |
+----------------+
1 row in set (0.00 sec)
mysql> show grants\G
*************************** 1. row ***************************
Grants for root@%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'
1 row in set (0.00 sec)
端口也是开放的（/etc/my.cnf已经加入了bind-address = 192.168.1.107）
[root@centos7 ~]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.1.107:3306 0.0.0.0:* LISTEN 5041/mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1232/sshd
tcp6 0 0 :::22 :::* LISTEN 1232/sshd
[解决方案]
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.107' (113)
查询一下错误代码代表什么意思
[root@centos6 data]# perror 113
OS error code 113: No route to host
使用telnet去测试3306端口
[root@centos6 data]# telnet 192.168.1.107 3306
Trying 192.168.1.107...
telnet: connect to address 192.168.1.107: No route to host
尝试在107上的防火墙上加上端口的允许
iptables -I INPUT -s 0/0 -p tcp --dport 3306 -j ACCEPT
这条规则的意思是，想要在输入数据INPUT中，protocol为tcp/IP的方式，访问端口3306，都会被允许的
[root@centos7 /]# iptables -L -n|grep 3306
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306
或者通过另外一种方式检查
[root@centos7 /]# iptables-save|grep 3306
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
现在telnet端口成功了
[root@centos6 data]# telnet 192.168.1.107 3306
Trying 192.168.1.107...
Connected to 192.168.1.107.
Escape character is '^]'.
N
5.5.47-logU)ld1f1]!€bRW>o5W:X-u(mysql_native_password
^CConnection closed by foreign host.
远程也能够连接成功了
[root@centos6 data]# mysql --host=192.168.1.107 --user=root --protocol=tcp
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.47-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+--------------------+
| user() |
+--------------------+
| root@192.168.1.108 |
+--------------------+
总结：ping通了目标机器，不等于能够访问目标机器的某个端口，这好比你可以顺利进入某条街道，但不等于你能进入街道里的某间房子(port)
