How to solve the problem that MySQL database cannot be accessed by other IPs

This article mainly introduces to you the solution to the problem that the MySQL database cannot be accessed by other IPs. The article introduces it in detail through the example code. It has certain reference and learning value for everyone's study or work. Friends who need it can follow Let’s learn together with the editor.

Preface

It’s still the project mentioned before. The environment is currently ready. A problem was discovered during the project preparation and verification phase. Application from the upper level I entered the authentication to access the application, but it was always waiting. I entered the correct username and password, but could not access it. It seems like there is no way to access the database connection.

The port after mysql deployment is generally 3306. Try to ping and telnet the 3306 port, and find that the ip can be pinged, but the 3306 port cannot be telneted, and an error like this is given:

ERROR 1130: Host *.*.*.* is not allowed to connect to MySQL

After seeing this error, I checked online and found that there are basically many solutions, but why do I need to write about it specifically?

The answer is: There are many solutions to mysql 5.6 on the Internet. In 5.7, the solution to this error is as follows:

1. After logging in to mysql for the first time, you need to change the root password in time. There is a difference between 5.6 and 5.7, that is, the password field in 5.6 is: password, but in version 5.7, this field is canceled and replaced by: authentication_string field .

The correct modification method is:

update user set authentication_string=password("xxxx") where user = "root";
flush privileges;

2. After modification, we use the use mysql command to switch to the mysql library. Note , this mysql library really exists in mysql, and is a library that stores some db-related information.

Command: use mysql;

3. Find the users table and execute the following command:

grant all privileges on *.* to 'root'@'%' identified by 'JLwg!2017' with grant option;

There are many modification methods for this on the Internet, there should be 4 in total, and I use one of them.

The meaning of this command is to modify the access permissions of the root user so that all IPs can access this user. In this way, our application can access this mysql from the external IP, otherwise only localhost can Access is very inconvenient.

4. Finally execute the flush privileges; command to restart the Mysql service, and the application logs in successfully.

Summarize

The above is the detailed content of How to solve the problem that MySQL database cannot be accessed by other IPs. For more information, please follow other related articles on the PHP Chinese website!