MySQL 8.0 - 客户端不支持服务器请求的认证协议；考虑升级 MySQL 客户端
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50093144/
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 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client
提问by Pig
I can't make a simple connection to the server for some reason. I install the newest MySQL Community 8.0 database along with Node.JS with default settings.
由于某种原因，我无法与服务器建立简单的连接。我安装了最新的 MySQL Community 8.0 数据库以及默认设置的 Node.JS。
This is my node.js code
这是我的 node.js 代码
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
insecureAuth : true
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
Below is the error found in Command Prompt:
以下是在命令提示符中发现的错误：
C:\Users\mysql-test>node app.js
C:\Users\mysql-test\node_modules\mysql\lib\protocol\Parse
r.js:80
throw err; // Rethrow non-MySQL errors
^
Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
at Handshake.Sequence._packetToError (C:\Users\mysql-
test\node_modules\mysql\lib\protocol\sequences\Sequence.js:52:14)
at Handshake.ErrorPacket (C:\Users\mysql-test\node_mo
dules\mysql\lib\protocol\sequences\Handshake.js:130:18)
at Protocol._parsePacket (C:\Users\mysql-test\node_mo
dules\mysql\lib\protocol\Protocol.js:279:23)
at Parser.write (C:\Users\mysql-test\node_modules\mys
ql\lib\protocol\Parser.js:76:12)
at Protocol.write (C:\Users\mysql-test\node_modules\m
ysql\lib\protocol\Protocol.js:39:16)
at Socket.<anonymous> (C:\Users\mysql-test\node_modul
es\mysql\lib\Connection.js:103:28)
at Socket.emit (events.js:159:13)
at addChunk (_stream_readable.js:265:12)
at readableAddChunk (_stream_readable.js:252:11)
at Socket.Readable.push (_stream_readable.js:209:10)
--------------------
at Protocol._enqueue (C:\Users\mysql-test\node_module
s\mysql\lib\protocol\Protocol.js:145:48)
at Protocol.handshake (C:\Users\mysql-test\node_modul
es\mysql\lib\protocol\Protocol.js:52:23)
at Connection.connect (C:\Users\mysql-test\node_modul
es\mysql\lib\Connection.js:130:18)
at Object.<anonymous> (C:\Users\mysql-test\server.js:
11:5)
at Module._compile (module.js:660:30)
at Object.Module._extensions..js (module.js:671:10)
at Module.load (module.js:573:32)
at tryModuleLoad (module.js:513:12)
at Function.Module._load (module.js:505:3)
at Function.Module.runMain (module.js:701:10)
I've read up on some things such as: https://dev.mysql.com/doc/refman/5.5/en/old-client.htmlhttps://github.com/mysqljs/mysql/issues/1507
我已经阅读了一些内容，例如：https: //dev.mysql.com/doc/refman/5.5/en/old-client.html https://github.com/mysqljs/mysql/issues/1507
But I am still not sure how to fix my problem. Any help would be appreciated :D
但我仍然不确定如何解决我的问题。任何帮助将不胜感激：D
回答by Pras
Execute the following query in MYSQL Workbench
在 MYSQL Workbench 中执行以下查询
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
Where rootas your user
localhostas your URL
and passwordas your password
哪里root作为您的用户
localhost作为您的 URL 和password您的密码
Then run this query to refresh privileges:
然后运行此查询以刷新权限：
flush privileges;
flush privileges;
Try connecting using node after you do so.
这样做后尝试使用节点连接。
If that doesn't work, try it without @'localhost'part.
如果这不起作用，请尝试不@'localhost'参与。
回答by Aidin
Let's first make it clear what's going on.
让我们先弄清楚发生了什么。
MySQL 8 has supports pluggable authentication methods. By default, one of them named caching_sha2_passwordis used rather than our good old mysql_native_password(source). It should be obvious that using a crypto algorithm with several handshakes is more secure than plain password passing that has been there for 24 years!
MySQL 8 支持可插拔的身份验证方法。默认情况下，使用其中一个命名caching_sha2_password而不是我们的旧版本mysql_native_password（source）。很明显，使用带有多次握手的加密算法比已经存在24 年的普通密码传递更安全！
Now, the problem is mysqljsin Node (the package you install with npm i mysqland use it in your Node code) doesn't support this new default authentication method of MySQL 8, yet. The issue is in here: https://github.com/mysqljs/mysql/issues/1507and is still open, after 3 years, as of July 2019.
现在，问题在于mysqljsNode（您安装npm i mysql并在 Node 代码中使用它的包）不支持 MySQL 8 的这种新的默认身份验证方法。问题在这里：https: //github.com/mysqljs/mysql/issues/1507并且在 3 年后，截至 2019 年 7 月仍然开放。
(UPDATE June 2019:There is a new PR in mysqljsnow to fix this!)(UPDATE Feb 2020:Apparently it's scheduled to come in version 3 of mysqljs.)
（2019 年 6 月更新：现在mysqljs 中有一个新的 PR来解决这个问题！）（2020 年 2 月更新：显然它计划在 mysqljs 的第 3 版中出现。）
Your options
您的选择
Option 1) Downgrade "MySQL" to authenticate using good old "native_password"
选项 1) 降级“MySQL”以使用旧的“native_password”进行身份验证
That's what everybody suggests here (e.g. top answer above). You just get into mysqland run a query saying rootis fine using old native_passwordmethod for authnetication:
这就是每个人在这里建议的（例如上面的最佳答案）。您只需进入mysql并运行一个查询，说root使用旧的native_password身份验证方法很好：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password ...
The good thing is, life is going to be simple and you can still use good old tools like Sequel Pro without any issue. But the problem is, you are not taking advantage of a more secure (and cool, read below) stuffs available to you.
好消息是，生活将是简单的，你仍然可以使用旧的好工具，如续集专业没有任何问题。但问题是，您没有利用更安全（而且很酷，请阅读下文）的东西。
Option 2) Replace "Node" package with MySQL Connecter X DevAPI
选项 2) 用 MySQL Connecter X DevAPI 替换“Node”包
MySQL X DevAPI for Nodeis a replacement to Node's Mysqljs package, provided by http://dev.mysql.comofficial guys.
MySQL X DevAPI for Node是Node 的 Mysqljs 包的替代品，由http://dev.mysql.com官方提供。
It works like a charm supporting caching_sha2_passwordauthentication. (Just make sure you use port 33060for X Protocol commmunications.)
它就像一个支持caching_sha2_password身份验证的魅力。（只需确保您使用端口33060进行X 协议通信。）
The bad thing is, you have left our old mysqlpackage that everyone is so used to and relies on.
不好的是，你离开了我们mysql每个人都习惯和依赖的旧包。
The good thing is, your app is more secure now and you can take advantage of a ton of new things that our good old friends didn't provide! Just check out the tutorial of X DevAPIand you'll see it has a ton of new sexy features that can come in handy. You just need to pay the price of a learning curve, which expectedly comes with any technology upgrade. :)
好消息是，您的应用程序现在更加安全，您可以利用我们的老朋友没有提供的大量新功能！只需查看X DevAPI的教程，您就会发现它具有大量可以派上用场的新功能。您只需要付出学习曲线的代价，任何技术升级都会带来学习曲线的代价。:)
PS. Unfortunately, this XDevAPI Package doesn't have types definition (understandable by TypeScript) yet, so if you are on typescript, you will have problems. I tried to generate .d.ts using dts-genand dtsmake, but no success. So keep that in mind.
附注。不幸的是，这个 XDevAPI 包还没有类型定义（TypeScript 可以理解），所以如果你使用typescript，你会遇到问题。我尝试使用dts-genand生成 .d.ts dtsmake，但没有成功。所以记住这一点。
Cheers!
干杯!
回答by upupming
Using the old mysql_native_passwordworks:
使用旧mysql_native_password作品：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourRootPassword';
-- or
CREATE USER 'foo'@'%' IDENTIFIED WITH mysql_native_password BY 'bar';
-- then
FLUSH PRIVILEGES;
This is because caching_sha2_passwordis introduced in MySQL 8.0, but the Node.js version is not implemented yet. You can see this pull requestand this issuefor more information. Probably a fix will come soon!
这是因为caching_sha2_password在 MySQL 8.0 中引入了，但 Node.js 版本尚未实现。您可以查看此拉取请求和此问题以获取更多信息。可能很快就会有修复！
回答by joshuakcockrell
Full Steps For MySQL 8
MySQL 8 的完整步骤
Connect to MySQL
连接到 MySQL
$ mysql -u root -p
Enter password: (enter your root password)
Reset your password
重置您的密码
(Replace your_new_passwordwith the password you want to use)
（替换your_new_password为您要使用的密码）
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
mysql> FLUSH PRIVILEGES;
mysql> quit
Then try connecting using node
然后尝试使用节点连接
回答by Siddhant
Although the accepted answer is correct, I'd prefer creating a new user and then using that user to access the database.
尽管接受的答案是正确的，但我更喜欢创建一个新用户，然后使用该用户访问数据库。
create user nodeuser@localhost identified by 'nodeuser@1234';
grant all privileges on node.* to nodeuser@localhost;
ALTER USER 'nodeuser'@localhost IDENTIFIED WITH mysql_native_password BY 'nodeuser@1234';
回答by Rich
If you encountered this error but still wanted to use MySQLversion 8. You can achieve this by telling MySQL Server to use the legacy authentication plugin when you create the database using Docker.
如果您遇到此错误但仍想使用MySQL版本 8。您可以通过在使用Docker.
So, your composefile will look like this:
因此，您的compose文件将如下所示：
# Use root/example as user/password credentials
version: '3.1'
services:
db:
image: mysql:8.0.15
command: --default-authentication-plugin=mysql_native_password
restart: always
environment:
MYSQL_ROOT_PASSWORD: 'pass'
MYSQL_DATABASE: 'db'
MYSQL_USER: 'user'
MYSQL_PASSWORD: 'pass'
ports:
- 3318:3306
# Change this to your local path
volumes:
- ~/Database/ORM_Test:/var/lib/mysql
回答by MacielJr
In Mysql Latest docker container
在 Mysql 最新的 docker 容器中
ALTER USER root IDENTIFIED WITH mysql_native_password BY 'password';
回答by Campalo
If the ALTER USER ... command line doesn't work for you AND if you are using Windows 10 then try to follow those steps:
如果 ALTER USER ... 命令行对您不起作用，并且您使用的是 Windows 10，请尝试按照以下步骤操作：
1) Type MySQL in the windows search bar
1）在windows搜索栏中输入MySQL
2) Open the MySQL Windows Installer - Community
2) 打开 MySQL Windows 安装程序 - 社区
3) Look for "MySQL server" and click on Reconfigure
3）寻找“MySQL服务器”并点击重新配置
4) Click on "Next" until you reach the "Authentification Method" phase
4) 点击“下一步”直到到达“身份验证方法”阶段
5) On the "Authentification Method" phase check the second option "Use Legacy Authentication Method"
5）在“身份验证方法”阶段，选中第二个选项“使用传统身份验证方法”
6) Then follow the steps given by the Windows installer until the end
6）然后按照Windows安装程序给出的步骤直到结束
7) When it's done, go into "Services" from the Windows search bar, click on "start" MySql81".
7) 完成后，从 Windows 搜索栏中进入“服务”，单击“启动”MySql81”。
Now, try again, the connection between MySQL and Node.js should work!
现在，再试一次，MySQL 和 Node.js 之间的连接应该可以工作了！
回答by Lester
In MySQL 8.0,
caching_sha2_passwordis the default authentication plugin rather thanmysql_native_password. ...
在 MySQL 8.0 中，
caching_sha2_password是默认的身份验证插件而不是mysql_native_password. ...
Most of the answers in this question result in a downgrade to the authentication mechanism from caching_sha2_passwordto mysql_native_password. From a security perspective, this is quite disappointing.
这个问题中的大多数答案都会导致身份验证机制从 降级caching_sha2_password到mysql_native_password。从安全的角度来看，这非常令人失望。
Thisdocument extensively discusses caching_sha2_passwordand of course why it should NOT be a first choice to downgrade the authentication method.
本文档广泛讨论caching_sha2_password了为什么不应该将其作为降级身份验证方法的首选。
With that, I believe Aidin's answershould be the accepted answer. Instead of downgrading the authentication method, use a connectorwhich matches the server's version instead.
回答by iomar
I have MYSQL on server and nodejs application on another server
我在服务器上有 MYSQL，在另一台服务器上有 nodejs 应用程序
Execute the following query in MYSQL Workbench
在 MYSQL Workbench 中执行以下查询
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'
