MySQL-1698 : Access denied for user 'root'@'localhost'解决方法

川长思鸟来 2022-05-11 07:40 328阅读 0赞

用root用户连接MySQL 数据库报"ERROR 1698 : Access denied for user ‘root’@‘localhost’" 错误。  
\*\*客户端：\*\*terminal，Navicat，连接都该错误报错误.  
\*\*系统环境：\*\*ubuntu 18,MySQL5.7  
\*\*原因：\*\*The reason is that recent Ubuntu installation (maybe others also), mysql is using by default the UNIX auth\_socket plugin.

Basically means that: db\_users using it, will be “auth” by the system user credentias. You can see if your root user is set up like this by doing the following:

As you can see in the query, the root user is using the auth\_socket plugin  
There are 2 ways to solve this:  
You can set the root user to use the mysql\_native\_password plugin  
You can create a new db\_user with you system\_user (recommended)  
**Option 1:**

> $ sudo mysql -u root \# I had to use “sudo” since is new installation  
> mysql> USE mysql;  
> mysql> UPDATE user SET plugin=‘mysql\_native\_password’ WHERE User=‘root’;  
> mysql> FLUSH PRIVILEGES;  
> mysql> exit;  
> $ service mysql restart

**Option 2: (replace YOUR\_SYSTEM\_USER with the username you have)**

> $ sudo mysql -u root \# I had to use “sudo” since is new installation  
> mysql> USE mysql;  
> mysql> CREATE USER ‘YOUR\_SYSTEM\_USER’@‘localhost’ IDENTIFIED BY ‘’;  
> mysql> GRANT ALL PRIVILEGES ON *.* TO ‘YOUR\_SYSTEM\_USER’@‘localhost’;  
> mysql> UPDATE user SET plugin=‘auth\_socket’ WHERE User=‘YOUR\_SYSTEM\_USER’;  
> mysql> FLUSH PRIVILEGES;  
> mysql> exit;  
> $ service mysql restart

Remember that if you use option \#2 you’ll have to connect to mysql as your system username (mysql -u YOUR\_SYSTEM\_USER)

Note: On some systems (e.g., Debian stretch) ‘auth\_socket’ plugin is called ‘unix\_socket’, so the corresponding SQL command should be: UPDATE user SET plugin=‘unix\_socket’ WHERE User=‘YOUR\_SYSTEM\_USER’;