Problem Description:
From a Linux remote connection on another Linux MySQL, error 2003 (HY000): Can ' t connect to MySQL server on ' IP ' (111) errors. [[email protected] ~]$ mysql-hxxx.xxx.xxx.85-uroot-penter password:123456 ERROR 2003 (HY000): Can ' t connect to MySQL Server on ' xxx.xxx.xxx.85 ' (111)
Problem Analysis: 1. Possible network connection ask, remote ping xxx.xxx.xxx.85, can ping pass, exclude this condition [[email protected] ~]$ ping xxx.xxx.xxx.85 Ping xxx.xxx.xxx.85 ( xxx.xxx.xxx.85) bytes of data.64 bytes from xxx.xxx.xxx.85:icmp_seq=1 ttl=63 time=0.230 ms
2. Troubleshooting may be due to 85 on the my.cnf configured skip_networking or bind_address, only allow local socket connection 2.1 under [mysqld] set skip_networking, knowledge Description: This uses MySQL only through the native socket connection (socket connection is also the default way of local connection), the abandonment of TCP/IP monitoring of course also does not allow the local Java program to connect to MySQL (connector/j can only be connected via TCP/IP). 2.2 may have used bind_address=127.0.0.1 (or other IP, of course) [mysqld] bind_address=127.0.0.1 please comment out this line #bind_address =127.0.0.1
3. Troubleshoot the DNS resolution problem and check if it is set: Skip_name_resolve. This situation is certainly not possible, because I use IP, not hostname. [Mysqld]skip_name_resolve Knowledge Description: This parameter plus, does not support the host name connection mode.
4. Troubleshoot the user and password problems, in fact, the user and password error, does not appear 111, so troubleshoot the user password Error 1045 (28000): Access denied for user ' root ' @ ' XXXX ' (using Password:yes)
5. To troubleshoot the--port problem, it is possible that 85 of MySQL port is not the default 3306, so I do not specify--port when I connect remotely, 3306 is used, and 85 does not listen for 3306. NETSTAT-NPLT | grep mysql TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 11107/mysqld Test Connection: Mysql-u root-p-H xxx.xxx.xxx.85--port 3306
6. The most pit of the firewall:
1. Check the firewall status [[email protected] ~]# service iptables statusredirecting to/bin/systemctl status iptables.serviceiptables.se Rvice-ipv4 Firewall with iptables loaded:loaded (/usr/lib/systemd/system/iptables.service; enabled) active:active (exited) since Wed 2016-11-02 23:10:51 CST; 14min ago process:12024 execstop=/usr/libexec/iptables/iptables.init Stop (code=exited, status=0/success) process:120 Execstart=/usr/libexec/iptables/iptables.init Start (code=exited, status=0/success) Main pid:12078 (code=exited, status=0/success) Nov 23:10:51 iz288zn7gymz iptables.init[12078]: iptables:applying firewall rules: [OK]nov 02 23:1 0:51 Iz288zn7gymz systemd[1]: Started IPv4 Firewall with iptables. Normal start.
See if MySQL port is released
[[email protected] ~]# iptables-l-N (or: Iptables--list)
Chain INPUT (Policy DROP)
Target Prot opt source destination &NB Sp; accept All -- 0.0.0.0/0 0.0.0.0/0 &NBSP ; accept All -- 0.0.0.0/0 0.0.0 .0/0 state related,establishedaccept TCP -- 0.0.0.0/0 & nbsp 0.0.0.0/0 state NEW TCP dpt:22accept &NB Sp TCP -- 0.0.0.0/0 0.0.0.0/0 s Tate NEW TCP dpt:80accept TCP -- 0.0.0.0/0 0.0.0.0/0 &N Bsp state NEW TCP dpt:21accept TCP -- 0.0.0.0/0 &NBS P &nbsP 0.0.0.0/0 state NEW TCP dpt:3306accept TCP --&N bsp;0.0.0.0/0 0.0.0.0/0 state NEW TCP DP T:443accept ICMP-- 0.0.0.0/0 0.0.0.0/0  LIMIT:AVG 100/sec burst 100ACCEPT ICMP-- 0.0.0.0/0 & nbsp 0.0.0.0/0  LIMIT:AVG 1/sec burst 10syn-flood tcp -- 0.0.0 .0/0 0.0.0.0/0  TCP Flags:0x17/0x02reject All -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
If you do not have 3306 join the firewall rule:
Iptables-a input-p tcp-m State--state new-m TCP--dport 3306-j ACCEPT
Note that you can only temporarily join the firewall to save the rules to the/etc/sysconfig/iptables file
[[Email protected] ~]# service iptables saveiptables:saving firewall rules to/etc/sysconfig/iptables:[OK]
、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
or simply rude.
Direct Vim/etc/sysconfig/iptables
Add one line-a input-p tcp-m state--state new-m TCP--dport 3306-j ACCEPT
Upon completion of my following:
# Generated by Iptables-save v1.4.21 on Thu Jan 19:16:55 2016*filter:input DROP [0:0]:forward ACCEPT [0:0]:output accep T [1:152]:syn-flood-[0:0]-a input-i lo-j accept-a input-m State--state related,established-j ACCEPT-A input-p TCP -M state--state new-m TCP--dport 22-j accept-a input-p tcp-m State--state new-m TCP--dport 80-j accept-a INPUT- P tcp-m State--state new-m TCP--dport 21-j accept-a input-p tcp-m State--state new-m TCP--dport 3306-j accept-a Input-p tcp-m State--state new-m TCP--dport 443-j accept-a input-p icmp-m limit--limit 100/sec--limit-burst 100 -j accept-a input-p icmp-m limit--limit 1/sec--limit-burst 10-j accept-a input-p tcp-m TCP--tcp-flags FIN,SYN,RST , ACK syn-j syn-flood-a input-j REJECT--reject-with icmp-host-prohibited-a syn-flood-p tcp-m limit--limit 3/sec--lim It-burst 6-j return-a syn-flood-j REJECT--reject-with icmp-port-unreachablecommit# completed on Thu Jan 28 19:16:55 201 6
Then restart the firewall:
[Email protected] ~]# service iptables restart
Redirecting To/bin/systemctl restart Iptables.service
PS. Please ensure that the MySQL process starts normally, and then check the above points individually.
Reference: MySQL Remote connection Error 2003 (HY000): Can ' t connect to MySQL server on ' XXXXX ' problem
MySQL permissions and password problems see: http://www.cnblogs.com/wangdaijun/p/5312424.html
ERROR 2003 (HY000): Can ' t connect to MySQL server on ' IP ' (111)
