❝

This article will solve the problem that the local Navicat connection to the remote MySQL server prompts that the connection is not allowed.

❞

1. Problem restoration

Local Navicat connects to the MySQL database of the centos7.3 virtual machineAfter confirming the connection, an error like this will be reported

2. Found the problem

#This problem seems to be a remote connection problem, but for new students, It is probably difficult for partners to think of this problem, so Kaka will help solve this problem step by step.

First we go to the virtual machine terminal and connect to MySQL.

Execute the commandmysql -uroot -p, then enter the password to enter MySQL

Execute the commandshow databases;You can view all the current databasesWhat we mainly need to focus on is the mysql library, switch databases, and execute the commanduse mysql, there is a user table in the mysql database. Let me make a small point here. In the terminal, we used to execute clear to clear the screen, but executing clear in mysql has no effect.

The screen clear command in the mysql terminal is system clear

Okay, back to the topic, in the user table, we only need two pieces of information , one is hos and the other is user. We need to execute the query statement to query it.

In the above figure, the host column specifies the IP that allows users to log in. If it is localhost, then you can only log in locally, but not remotely.

This is why our local navicat cannot connect to mysql.

Since the local virtual machine does not have security issues, Kaka will set the host to %.

% represents a wildcard character, which means that all connections are allowed and no IP is restricted.

Note: In the production environment, you cannot set host to % to save trouble. Doing so will cause security problems. The specific settings can be set according to the IP of the production environment;

The problem has been found. Next go figure it out.

3. Solve the problem

#Execute the commandupdate user set host='%' where user= 'root';, change localhost to %, allowing all IPs to connect. You still need to refresh the permissions hereflush privileges

Local navicta connects to the database again. That's it at this time.

##4. Extension

flush privilegesWhen needs to be executed.