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

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;

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

Recently, flush privileges need to be executed;

flush privileges;

Then Navicat will be connected successfully.