Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "MySQL remote connection Tip ERROR 2003 (HY000): the solution of Can't connect to MySQL serv". In the operation of practical cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Problem description:

An ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.85' error occurred when connecting remotely from one linux to the MySQL on another linux.

[@ vvmvcs0 ~] $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'

[mysql@vvmvcs0 ~] $perror 111

OS error code 111: Connection refused

View errorCode

[mysql@vvmvcs0 ~] $perror 111

OS error code 111: Connection refused

Problem analysis:

1, possible network connection, remote ping xxx.xxx.xxx.85, can ping, exclude this situation

[mysql@vvmvcs0 ~] $ping xxx.xxx.xxx.85

PING xxx.xxx.xxx.85 (xxx.xxx.xxx.85) 56 (84) 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 the configuration of skip_networking or bind_address in the my.cnf on 85, which only allows local socket connections.

2.1 set skip_networking under [mysqld]

Knowledge description: the use of MySQL can only be connected through the native Socket connection (socket connection is also the default way of local connection), give up the monitoring of TCP/IP www.2cto.com

Of course, local java programs are not allowed to connect to MySQL (Connector/J can only be connected through TCP/IP).

2.2 bind_address=127.0.0.1 may be used (of course, it can be other ip)

[mysqld]

Bind_address=127.0.0.1

Knowledge description: in this case, you can connect with TCP/IP.

By looking at the my.cnf file, both of the above are not set, and these two situations are excluded.

3. Troubleshoot the DNS parsing problem and check whether: skip_name_resolve is set. This is definitely not possible, because I am using ip, not the host name.

[mysqld]

Skip_name_resolve

Knowledge description: after this parameter is added, the connection mode of hostname is not supported.

4. Troubleshoot user and password problems. In fact, errors in users and passwords will not occur in 111, so rule out user password problems.

ERROR 1045 (28000): Access denied for user 'root'@'XXXX' (using password: YES)

5. Troubleshoot-- port problems. It is possible that the MySQL port of 85 is not 3306 by default, so when I connect remotely, I do not specify-- port, which uses 3306, and 3306 is not monitored on 85.

Ps-ef | grep mysqld

Sure enough: the MySQL on 85 uses 3308 port.

Final connection method: add-- port=3308

[mysql@vvmvcs0] $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 MySQL on 85, and every time I connect directly with mysql-uroot, I do not specify-- port, so I always thought that the port of this MySQL has always been the default of 3306.

In fact, the root cause is:

1. MySQL local connection. If you do not refer to mysql-- protocol=tcp, the connection is connected in socket mode by default. Everyone knows that. Www.2cto.com

2. The MySQL socket connection is based on the sokect file and has nothing to do with-- port. If it is multiple instances on one machine, use-S (or-- socket=name) to specify which instance to connect to.

It is this socket connection that has no recognition effect on-- port, which leads to troubleshooting this problem for so long.

See below: in fact, there is only one MySQL instance with a port of 3308 on 85, but this instance is still connected with 3306, 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.

Once again, the basic details are important.

This is the end of "MySQL remote connection Tip ERROR 2003 (HY000): Can't connect to MySQL serv solution". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.