更新时间:2023-12-01 21:27:46
某些系统如 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:
mysql_native_password
插件db_user
system_user
(推荐)mysql_native_password
plugindb_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 登录.并在提示中提供该用户的密码.不需要UPDATE user SET plugin";步骤.
对于 8.0.4 默认身份验证插件更新,请参阅 https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/