一些系统如 Ubuntu、MySQL 正在使用 UNIX auth_socket 插件 默认.

Some systems like Ubuntu, MySQL is using the UNIX auth_socket plugin by default.

基本上这意味着:db_users 使用它，将被认证"；通过系统用户凭据.您可以通过执行以下操作来查看您的 root 用户是否设置为这样:

Basically it means that: db_users using it, will be "authenticated" by the system user credentials. You can see if your root user is set up like this by doing the following:

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 插件.

As you can see in the query, the root user is using the auth_socket plugin.

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

There are two ways to solve this:

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

1. You can set the root user to use the mysql_native_password plugin
2. You can create a new db_user with you system_user (recommended)

选项 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

选项 2:(将 YOUR_SYSTEM_USER 替换为您拥有的用户名)

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 '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.

Remember that if you use option #2 you'll have to connect to MySQL as your system username (mysql -u YOUR_SYSTEM_USER).

注意:在某些系统上(例如，Debian 9 (Stretch)) 'auth_socket' 插件被称为 'unix_socket'，所以对应的SQL命令应该是:UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

Note: On some systems (e.g., Debian 9 (Stretch)) the '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';

从@andy 的评论看来，MySQL 8.x.x 更新/替换了 caching_sha2_password 的 auth_socket.我没有使用 MySQL 8.x.x 的系统设置来测试这个.但是，上述步骤应该可以帮助您理解问题.回复如下:

From @andy's comment it seems that MySQL 8.x.x updated/replaced the auth_socket for caching_sha2_password. I don't have a system setup with MySQL 8.x.x to test this. However, the steps above should help you to understand the issue. Here's the reply:

MySQL 8.0.4 的一个变化是新的默认身份验证插件是caching_sha2_password".新的YOUR_SYSTEM_USER"将具有此身份验证插件，您现在可以使用mysql -u YOUR_SYSTEM_USER -p"从 Bash shell 登录.并在提示时提供此用户的密码.不需要更新用户设置插件"；步骤.

有关 8.0.4 默认身份验证插件更新，请参阅 https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/