MySQL Remote connection Error 2003 (HY000): Can ' t connect to MySQL server on ' XXXXX ' issue

MySQL Remote connection Error 2003 (HY000): Can ' t connect to MySQL server on ' XXXXX ' problem

Problem Description:

From a Linux remote connection on another Linux MySQL, error 2003 (HY000): Can ' t connect to MySQL server on ' xxx.xxx.xxx.85 ' (111) errors.

[Email protected] ~]$ mysql-hxxx.xxx.xxx.85-uroot-p

Enter password:www.2cto.com

ERROR 2003 (HY000): Can ' t connect to MySQL server on ' xxx.xxx.xxx.85 ' (111)

[Email protected] ~]$ perror 111

OS error code 111:connection refused

View ErrorCode

[Email protected] ~]$ perror 111

OS error code 111:connection refused

Problem Analysis:

1, may 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.

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 with skip_networking or bind_address, only allow local socket connection

2.1 Set the skip_networking under [Mysqld],

Knowledge Note: This uses MySQL only through the native socket connection (socket connection is also the default way of local connection), discard the TCP/IP monitoring www.2cto.com

Of course, the local Java program is not allowed to connect to MySQL (connector/j can only be connected via TCP/IP).

2.2 may be using bind_address=127.0.0.1 (or other IP as well)

[Mysqld]

bind_address=127.0.0.1

Knowledge Note: This situation can be TCP/IP connected

By viewing the my.cnf file, the above two are not set, excluding the two cases

3, troubleshoot the DNS resolution problem, check whether it is set: Skip_name_resolve. This situation is certainly not possible, because I use IP, not hostname.

[Mysqld]

Skip_name_resolve

Knowledge Note: After this parameter is added, the host name connection is not supported.

4, troubleshoot the user and password problems, in fact, the user and password errors, will not appear 111, so troubleshooting user password issues

ERROR 1045 (28000): Access denied for user ' root ' @ ' XXXX ' (using Password:yes)

5, troubleshoot the--port problem, it is possible that 85 of MySQL port is not the default 3306, so I connect remotely, did not specify--port, with 3306, and 85 did not listen to 3306.

Ps-ef | grep mysqld

Sure enough: MySQL on 85 uses 3308 port.

Final connection method: Plus--port=3308

[Email protected] ~]$ mysql-hxxx.xxx.xxx.85-uroot-p--port=3308

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Why is there such a low-level mistake?

Because I have been using the 85 MySQL, and each time is directly connected with Mysql-uroot, no designated--port, so I always thought this MySQL port has been the default of 3306.

In fact, the root cause is:

1. mysql local connection, if not referred to as MySQL--protocol=tcp, the connection by default is the socket connection.  We all know this. Www.2cto.com

2, MySQL socket connection is based on the Sokect file, not related to--port, if it is a multi-instance, then use-s (or--socket=name) to specify which instance to connect.

This is the socket connection to--port No recognition effect, resulting in troubleshooting this problem for so long.

See below: In fact, there is only one port of 85 on the MySQL instance, but with 3306 is still connected on this instance, indicating that the socket connection method ignores the--port parameter.

-bash-3.2$ Mysql-uroot--port=3308

Welcome to the MySQL Monitor. Commands End With; or \g.

Mysql-uroot--port=3306

Welcome to the MySQL Monitor. Commands End With; or \g.

It is important to explain the basic details again.

This article is from the "See" blog, please be sure to keep this source http://732233048.blog.51cto.com/9323668/1635940

MySQL Remote connection Error 2003 (HY000): Can ' t connect to MySQL server on ' XXXXX ' issue