MySQL 将身份验证类型从标准更改为 caching_sha2_password
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49931541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL changing authentication type from standard to caching_sha2_password
提问by Matthew van Boheemen
I've setup a new MySQL instance on a computer and every time I add a user it sets the Authentication Type to caching_sha2_password.
我在计算机上设置了一个新的 MySQL 实例，每次添加用户时，它都会将身份验证类型设置为caching_sha2_password.
This happens even if I set the Authentication Type to "Standard", it then changes it when I save the user. I've also changed the default authentication plug in to "mysql_native_password", but it still keeps doing it.
即使我将身份验证类型设置为“ Standard”，也会发生这种情况，然后在我保存用户时更改它。我还将默认身份验证插件更改为“ mysql_native_password”，但它仍然继续这样做。
With it using the caching_sha2_passwordI can't connect to the database from .net core as I get an error stating:
使用它时，caching_sha2_password我无法从 .net core 连接到数据库，因为我收到一条错误消息：
MySqlException: Authentication method 'caching_sha2_password' not supported by any of the available plugins
MySqlException：任何可用插件都不支持身份验证方法“caching_sha2_password”
How do I get it to save users with the Standard authentication type?
如何使用标准身份验证类型保存用户？
回答by Meko Perez Estevez
I had the same problem today. The only way I found to fix it was:
我今天遇到了同样的问题。我发现修复它的唯一方法是：
- executing the install file
- select "Reconfigure" over the mysql server
- In Authentication Method tab, select "Use Legacy Authentication Method"
- 执行安装文件
- 在 mysql 服务器上选择“重新配置”
- 在“身份验证方法”选项卡中，选择“使用传统身份验证方法”
It should fix your problem.
它应该可以解决您的问题。
回答by Elias
Run
跑
mysql> CREATE USER ‘username'@‘localhost' IDENTIFIED WITH mysql_native_password BY ‘password';
回答by gem007bd
Reset the user password with mysql_native_password
使用 mysql_native_password 重置用户密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{NewPassword}';
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{NewPassword}';
回答by Marius Van Der Berg
For those of you that are using MySQL Workbench 8.0 CE and still struggling, the following worked for me:
对于那些使用 MySQL Workbench 8.0 CE 并且仍在苦苦挣扎的人，以下对我有用：
- Under Navigator, click on Administration Administration under Navigator preview, then select Options File.
- Administration - Options Filewill open. Under the security tab, scroll down until you see "default authentication plugin". Mine was on caching_sha2_password. Changing it to mysql_native_password, clicking apply and restarting MySQL Workbench worked for me.
- 在 Navigator 下，单击Navigator preview 下的Administration Administration，然后选择 Options File。
- 管理 - 选项文件将打开。在安全选项卡下，向下滚动直到看到“默认身份验证插件”。我的是 caching_sha2_password。将其更改为 mysql_native_password，单击应用并重新启动 MySQL Workbench 对我有用。
Administration - Options File preview
I had to delete and re-add the user. It did not automatically change the user's authentication type back to caching_sha2_password, it kept it on standard.
我不得不删除并重新添加用户。它没有自动将用户的身份验证类型改回 caching_sha2_password，而是保持标准。
回答by SalkinD
You can also do it with MySQL Workbench:
您也可以使用 MySQL Workbench 来完成：
"Users and Privilegues" -> "Add Account" -> Authentication Type: "Standard"
“用户和权限”->“添加帐户”-> 身份验证类型：“标准”
This user now can login with default login, i.e. for phpMyAdmin.
该用户现在可以使用默认登录名登录，即 phpMyAdmin。
回答by Dibyajyoti Nayak
import mysql.connector
def connect():
conn = mysql.connector.connect(host='localhost',
database='mydb',
user='root_new',
password='root_new')
if conn.is_connected():
print('Connected to MySQL database')
if name == 'main':
connect()
Output : Connected to MySQL database
1.Stop database server in preferences for MacOS.
2.initialise DB with legacy authentication.
3.Open mysqlWorkBench and Create a new user with standard authentication.
4.Create a new schema(DB) in sqlWorkbench.
5.Execute python Code in Eclipse.
