Mysql 错误 1698 (28000)： 拒绝用户 'root'@'localhost' 的访问

有些系统，比如Ubuntu，MySQL默认使用UNIX auth_socket plugin.

基本上，这意味着:db_users using it, will be "authenticated" by 101您可以通过执行以下操作来查看您的root用户是否设置为这样:

sudo mysql -u root # I had to use "sudo" since it was a new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

正如您在查询中看到的，root用户正在使用auth_socket插件.

有两种方法可以解决这个问题:

1. 您可以将root用户设置为使用mysql_native_password插件
2. 你可以用system_user创建一个新的db_user(推荐)

Option 1:

sudo mysql -u root # I had to use "sudo" since it was new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

sudo service mysql restart

Option 2:(用你的用户名替换你的系统用户)

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 'YOUR_PASSWD';
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;

sudo service mysql restart

请记住，如果使用选项#2，则必须以系统用户名(mysql -u YOUR_SYSTEM_USER)连接到MySQL.

Note:在某些系统上(例如Debian 9(Stretch))"auth_socket"插件被称为'unix_socket'，因此相应的SQL命令应该是:UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

从@andy的 comments 来看，MySQL 8.x、 x更新/替换了caching_sha2_password的auth_socket.我没有MySQL 8的系统设置.x、 x来测试这个.然而，以上步骤应该有助于您理解这个问题.答复如下:

One change as of MySQL 8.0.4 is that the new default authentication plugin is 'caching_sha2_password'. The new 'YOUR_SYSTEM_USER' will have this authentication plugin and you can log in from the Bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. There isn’t any need for the "UPDATE user SET plugin" step.

For the 8.0.4 default authentication plugin update, see 100