ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

created at 12-29-2021 views: 332

problem

I found a lot of posts on the Internet and copied each other. The key is not to solve the problem after execution. Here is my solution.

The problem is that Navicat reports an error when connecting

1130 - Host "xxx" is not allowed to connect to this MySQL server.

Then set the mysql password on the server to report an error

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

solution

In fact, this question is very simple:

show databases;
show tables;

After execution, you will find a user table;

select host,user from user;

user table

The host in the first line is added by myself. The bottom line is the root account that I had at the beginning. You can directly change the host corresponding to the root account in the user table to '%' to log in remotely on Navicat, that is SQL update statement, don’t have to be so complicated.

update user set host ='%' where host ='localhost' and user ='root';

Check again and found that the update has been successful

updated user table

Recently, flush privileges need to be executed;

flush privileges;

Then Navicat will be connected successfully.

created at:12-29-2021
edited at: 12-29-2021: