Solution to the problem that the MySQL database cannot be accessed by other IP addresses

Preface

Still referring to the project mentioned before, the environment is now ready. During the project preparation and verification phase, a problem was discovered. When entering authentication access to the application from the upper-level application, it was always in a waiting state. Even after entering the correct username and password, it was still not accessible. It looks like there is no way to access the database connection.

The port of MySQL after deployment is usually 3306. Try to ping and telnet port 3306. It is found that the IP can be pinged, but port 3306 cannot be telnetted, and an error message is given:

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

After seeing this error, I searched 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 on the Internet for MySQL 5.6. In 5.7, the solution to this error is as follows:

1. After logging into MySQL for the first time, you need to change the root password in time. There is a difference between 5.6 and 5.7. The password field in 5.6 is: password, but in version 5.7, this field is cancelled 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 the modification, we use the use mysql command to switch to the mysql library. Note that this mysql library really exists in mysql and 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 ways to modify this on the Internet, there should be 4 in total, and I use one of them.

This command means to modify the access rights of the root user so that all IP addresses can access this user. In this way, our application can access this mysql from an external IP address. Otherwise, only localhost can access it, which is very inconvenient.

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

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.