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

This article mainly introduces "how to troubleshoot the failure of client connection MySQL". In daily operation, I believe many people have doubts about how to eliminate the failure of client connection MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "how to troubleshoot client connection MySQL failure". Next, please follow the editor to study!

1. The mysqld process is not running properly

In this case, first go to the server to see if the mysqld process is alive, using the following command:

Mysqladmin ping or ps-ef | grep mysqld II. The client cannot communicate with the process mysqld

If the mysqld process on the MySQL server is running properly, let's see if the client can communicate with mysqld and test the network connectivity using the following command:

Telnet localhost 3306

If you can get through locally, go to the client's machine and change the localhost to the ip address of the MySQL server for testing. If it cannot be connected, there are usually two reasons, one is the problem of OS or network, or the firewall; the other is that mysqld itself does not listen to the connection request of the client at all, and there are three kinds of listening to the client after mysqld starts.

The first case

Using the parameter-skip-networking to skip the network connection of the listening client, we can see that MySQL is not listening on port 3306 at all with the following command.

Mysqld-no-defaults-console-user mysql-skip-networking & netstat-plunt | grep 3306

The second case

Use the parameter-bind-address followed by a restriction on client access to IP addresses, such as listening only for local connections:

Mysqld-- no-defaults-- user mysql-- bind-address=127.0.0.1 & netstat-plunt | grep 3306 tcp 0 0127.0.1 user mysql 3306 0.0.0.0 no- * LISTEN 22767/mysqld tcp6 0 0:: 33060:: * LISTEN 22767/mysqld mysqld-- no- Defaults-- user mysql-- bind-address='192.168.17.40' & netstat-plunt | grep 3306 tcp 0 0 192.168.17.40 bind-address='192.168.17.40' 3306 0.0.0.0 * LISTEN 23053/mysqld tcp6 0 0:: 33060:: * LISTEN 23053/mysqld

The third situation

There are no restrictions on client access to IP addresses.

Mysqld-- no-defaults-- user mysql & netstat-plunt | grep 3306 tcp6 0 0: 33060: * LISTEN 23582/mysqld tcp6 0 0: 3306:: * LISTEN 23582/mysqld

We can infer the parameter settings of the mysqld process by looking at the network port listening.

III. The problem of account password

The last case is the account password. To deal with this situation, we have a powerful tool to check the error log of MySQL. The details of error log records are controlled by the parameter-log-error-verbosity. The function of this parameter is as follows:

The default is 2, set to 3 to record more information, and this parameter can be set online:

Mysql > set global log_error_verbosity=3; Query OK, 0 rows affected (0.00 sec)

When the password is wrong

Mysql-uroot-perrorpasswordmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

It is recorded in MySQL's error log:

2020-11-03T07:59:40.720835Z 7 [Note] [MY-010926] [Server] Access denied for user 'root'@'localhost' (using password: YES)

Seeing such a record, we at least know that the client is connected to the MySQL service. If the parameter-log-error-verbosity is set to the default value of 2 without this prompt, that is to say, there is no information of type note.

Account error

ERROR 1130 (HY000): Host '192.168.17.149' is not allowed to connect to this MySQL server

Note that when the account is wrong, the prompt is "is not allowed to connect to this MySQL server", and when the password is wrong, it is "Access denied for user".

An account in MySQL consists of two parts, user and host. In MySQL, there is a mysql database with a user table in which Host and User are two primary key columns (primary key) that uniquely represent a user. In a case like this, the host field is usually localhost, so just change it to the wildcard "%".

At this point, the study on "how to troubleshoot the failure of client connection MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.