MySQL: if the remote connection using navicatip fails, prompt “is not allowed to connect to this MySQL server”

Problem:
when using Navicat premium to remotely connect to MySQL via IP, it fails, indicating “is not allowed to connect to this MySQL server”, but it is normal to use localhost instead of IP locally.

*Operate on the server side

Solution:
modify the user table in the MySQL library, change the host value corresponding to the user root from localhost to%, and restart the MySQL service to take effect.

Method 1 (simplest): use Navicat premium local login to modify.

You can log in successfully with localhost locally. Open the user table in the MySQL library, change the host of the corresponding root user to%, submit and restart the MySQL service.

Method 2: login and modify with MySQL command line.

1. Connect to server: MySQL – U root – P

2. View all current databases: show databases( Note the comma at the end)

3. Enter MySQL database: use MySQL;

4. View all the tables in MySQL database: show tables;

5. View the data in the user table:

select Host, User,Password from user;

6. Modify the host in the user table.

update user set Host=’%’ where User=‘root’;

7. Refresh: flush privileges;

8. The test connection is successful

Link to the original text: https://blog.csdn.net/quan278905570/article/details/106327719


Read More: