Shulou(Shulou.com)06/02 Report--

This article introduces the knowledge of "MySQL prompts is not allowed to connect for local connection to virtual machines". In the operation of actual cases, many people will encounter this dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. problem reduction

Local Navicat connects to the MySQL database of the centos7.3 virtual machine

An error like this will be reported after confirming the connection.

Second, find problems

At first glance, this problem is a problem of remote connection, but it is probably very difficult for new learners to think of this problem, and then click to help solve the problem step by step.

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

Execute the command mysql-uroot-p, then enter the password to enter MySQL

Execute the instruction show databases; to see all the current databases we mainly need to focus on the mysql library, switch the database, execute the command use mysql, and there is a user table in the database of mysql. To talk about a small point here, we can clear the screen by executing clear at the terminal, but it is useless to execute clear in mysql.

At the mysql terminal, the screen clearing instruction is system clear.

All right, let's get back to the point. 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 out.

In the figure above, the host column specifies the ip that allows the user to log in. If it is localhost, it can only be logged in locally, not remotely.

This is why our local navicat connection mysql cannot be connected.

Since there is no security problem with the local virtual machine, Kaka will set host to%.

% stands for wildcard characters, which means that all these connections are OK, without restricting any ip.

Note: host cannot be set to% to save trouble in the production environment. There will be safety problems. The specific settings can be set according to the IP of the production environment.

The problem has been found, and then we will solve it.

Third, solve the problem

Execute the instruction update user set host='%' where user='root';, to modify the localhost to%, allowing all ip connections. One more permission refresh flush privileges is required here.

The local navicta connects to the database again. This time will be fine.

IV. Expansion

About when flush privileges needs to be executed.

This command extracts the user information or permissions from the user and privilege tables from the mysql library into memory.

When the user data and permissions are modified and you want to take effect directly if you do not want to restart the database, you need to execute this directive.

That is to say, this directive is only for user data and user rights changes.

"Local connection to the virtual machine MySQL prompts what to do with is not allowed to connect" ends here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.