Mysql database cannot be accessed by other ip addresses.

Mysql database cannot be accessed by other ip addresses.

Preface

As mentioned earlier, the environment is ready. A problem is found in the project preparation and verification phase. The application enters authentication from the upper-layer application to access the application and remains in the waiting status, the user name and password entered are not accessible. It seems that there is no way to access the database connection.

After mysql is deployed, the port is usually 3306. Try to ping and telnet port 3306, and find that the ip can be pinged, but port 3306 cannot be telnet, and the following error is reported:

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

After seeing this error, I checked the Internet. There are basically many solutions, but why do I need to write it?

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

1. after logging on to mysql for the first time, you need to change the root password in time. There is a difference between mysql 5.6 and mysql 5.7, that is, the password field of mysql 5.6 is: password, but in mysql 5.7, this field is canceled and replaced with the 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 database. Note that this mysql database actually exists in mysql and stores database-related information.

Command:use mysql;

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

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

There are many ways to modify this item on the Internet. There should be a total of four methods, one of which I use.

This command modifies the root user's access permissions so that all ip addresses can access this user. In this way, our application can access this mysql from an external ip address, otherwise, it is inconvenient to access only localhost.

4. Final executionflush privileges;Command to restart the Mysql service and successfully log on to the application.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.