ER_NOT_SUPPORTED_AUTH_MODE - MySQL 服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44946270/
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
ER_NOT_SUPPORTED_AUTH_MODE - MySQL server
提问by Jo?o Gouveia
I have setup a MySQL database. When I try to access it through my Node JS server, I am getting the error
我已经设置了一个 MySQL 数据库。当我尝试通过 Node JS 服务器访问它时，出现错误
"ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MariaDB client"
“ER_NOT_SUPPORTED_AUTH_MODE：客户端不支持服务器请求的认证协议；考虑升级 MariaDB 客户端”
which is strange since I already uninstalled MariaDB and installed MySQL using Homebrew.
这很奇怪，因为我已经卸载了 MariaDB 并使用 Homebrew 安装了 MySQL。
This is my attempt of connection:
这是我的连接尝试：
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '********',
database : 'vod_bill_database'
});
Any ideas? Thanks
有任何想法吗？谢谢
采纳答案by Jo?o Gouveia
I figured that in some MySQL versions they have the authentication for the establishment of a connection a bit messed. All I had to do was add the line "insecureAuth : true" to my connection attempt.
我认为在某些 MySQL 版本中，他们对建立连接的身份验证有点混乱。我所要做的就是在我的连接尝试中添加“insecureAuth：true”行。
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '********',
database : 'vod_bill_database',
insecureAuth : true
});
It is working fine now.
它现在工作正常。
回答by Archil Labadze
For now 8.0 version is way to change plugin : Use this example:
目前 8.0 版本是更改插件的方法：使用此示例：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
回答by Vinayak Shedgeri
Do Reconfigure Authentication Method,there you select legacy type password support for 5.1
重新配置身份验证方法，在那里选择 5.1 的遗留类型密码支持
Reason for the error
错误原因
*You Installed latest Mysql 8.0 ,it has deferent authentication method. degrading 5.6 or 5.1 is simple solution
*您安装了最新的Mysql 8.0，它具有不同的身份验证方法。降级 5.6 或 5.1 是简单的解决方案
回答by Sheyan Sandaruwan
1st run this code ->
第一次运行此代码->
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
2nd
第二
flush privileges;
回答by pingle60
You can either alter an existing user to use mysql_native_password, or create a new user,
您可以更改现有用户以使用 mysql_native_password，或创建一个新用户，
CREATE USER 'new_user'@'%' IDENTIFIED WITH mysql_native_password BY '***';
GRANT USAGE ON *.* TO 'new_user'@'%';
ALTER USER 'new_user'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT ALL PRIVILEGES ON `new_user`.* TO 'new_user'@'%';
FLUSH PRIVILEGES;
replace new_user with your new user name, and set your password.
用您的新用户名替换 new_user，并设置您的密码。
Now you can access mysql from node using the mysql package,
现在您可以使用 mysql 包从节点访问 mysql，
npm install mysql
recommended to use pool connection for this package.
建议为此包使用池连接。
回答by Aft3rL1f3
The top rated answers in this Q/A thread are for the most part accurate/valid, but unorganized to say the least. A soluion is here, however; the solutions is pieces and bits found amongest three answers. To offer an answer and solution that exist in a single post, is easier to read, inturpret, and all around more helpfull and a time saver, I will make an attempt to answer the question with a clear, concise and orderly set of instructions that covers the whole Promblem that Ubuntu users are experiancing. In addition I will add information neccassary, not included in other answers, for understanding the issue being solved. Like that the problem is not a SQL problem it is an Ubuntu. To bring clarity to readers, of what the problem in presistence is; 'ROOT' user doesn't have a password in Ubuntu. Ubuntu users use the sudo command, and organize authority through permission of use of the sudo cmd for pretty much everything, there are exceptions and there is a Ubuntu Root User option, but it doesn't help to discuss those things right here, right now. The important thing to note is the following:
此问答线程中评分最高的答案在很大程度上是准确/有效的，但至少可以说是无组织的。然而，这里有一个解决方案；解决方案是在三个答案中找到的点点滴滴。为了提供存在于单个帖子中的答案和解决方案，更易于阅读、解释，并且更有用且更节省时间，我将尝试用一组清晰、简洁和有序的说明来回答问题涵盖了 Ubuntu 用户正在经历的整个问题。此外，我将添加其他答案中未包含的必要信息，以了解正在解决的问题。就像这个问题不是 SQL 问题，它是一个 Ubuntu。使读者清楚存在的问题是什么；'ROOT' 用户在 Ubuntu 中没有密码。Ubuntu 用户使用 sudo 命令，并通过对几乎所有内容使用 sudo cmd 的许可来组织权限，也有例外，并且有一个 Ubuntu Root 用户选项，但现在在这里讨论这些事情无济于事。需要注意的重要事项如下：
Ubuntu lacks a 'ROOT PASSWORD' and this is why everyone experiancing the issue that we are discussing runs a Distrabution of the Ubuntu OS/SHELL. We cannot give Ubuntu SHELL a 'ROOT PASSWORD', however, we can, and we are, going to give a 'ROOT PWD' to our 'MYSQL-COMMUNITY-SERVER'.
Firstly you need NPM, NodeJS, MySql, and NPM's MySql Driver. If you don't already have them you shouldn't even be reading this, becuase your cant even know 100% if the issue you are solving is this...
...If you do have everything on the list, and are running a Ubuntu dist., and are getting error authentication messages and Connection err messages that look like the following
ERROR: (28000): Access denied for user 'ajc'@'localhost'
- ERROR: ERROR_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
- 'Client does not support authentication protocol requested by server; consider upgrading MySQL client
Ubuntu 缺少“根密码”，这就是为什么每个遇到我们正在讨论的问题的人都运行 Ubuntu OS/SHELL 的 Distrabution 的原因。我们不能给 Ubuntu SHELL 一个“ROOT PASSWORD”，但是，我们可以，而且我们将给我们的“MYSQL-COMMUNITY-SERVER”一个“ROOT PWD”。
首先你需要 NPM、NodeJS、MySql 和 NPM 的 MySql 驱动程序。如果你还没有它们，你甚至不应该阅读这篇文章，因为如果你正在解决的问题是这个，你甚至不能 100% 知道......
...如果您确实拥有列表中的所有内容，并且正在运行 Ubuntu 发行版，并且收到如下所示的错误身份验证消息和连接错误消息
错误: (28000): 用户 'ajc'@'localhost' 访问被拒绝
- ERROR: ERROR_NOT_SUPPORTED_AUTH_MODE: 客户端不支持服务器请求的认证协议；考虑升级 MySQL 客户端
- '客户端不支持服务器请求的认证协议；考虑升级 MySQL 客户端
Then you are likley in the right place.
那么您来对地方了。
To solve the problem start be creating an Empty 'dot JS' file for testing, call it 'sqltest.js' or whatever you like.
Add the CODE bellow to it.
In the method called 'createConnection' is a JSON OBJ parameter holding the credential values to make a valid connection to the MySql Database server. The user has to equal to 'root', and the database has to exist. Also for a later test add a testing table to the database, with some BS data."
要解决这个问题，首先要创建一个空的 'dot JS' 文件进行测试，将其命名为 'sqltest.js' 或任何您喜欢的名称。
将下面的代码添加到其中。
在名为“createConnection”的方法中，有一个 JSON OBJ 参数，用于保存与 MySql 数据库服务器建立有效连接的凭据值。用户必须等于“root”，并且数据库必须存在。还要在以后的测试中向数据库添加一个测试表，其中包含一些 BS 数据。”
let mysql = require('mysql');
let connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '********',
database : 'DB_App_00',
});
connection.connect(function(err) {
if (err) {
return console.error('error: ' + err.message);
}
console.log('Connected to the MySQL server.');
});
Now open a terminal window, and do your typical updates & upgrades, this is important, which is why every tut asks you to do them.
现在打开一个终端窗口，并进行典型的更新和升级，这很重要，这就是为什么每个人都要求你这样做。
~$: sudo apt update
~$: sudo apt upgrade
let connection = mysql.createConnection({ host : 'localhost', user : 'root', password : '********', database : 'DB_App_00', });
let connection = mysql.createConnection({ host : 'localhost', user : 'root', password : '********', database : 'DB_App_00', });
Now type the following cmd
现在输入以下cmd
~$: sudo mysql -u root
It sould prompt you for your Ubuntu Password, type it and [ENTER].
Now here is the step that could be considered the medicine and/or the cure to the problem. Your terminal should be open, and you should be inside of the MYSQL Server, under the user 'root'. The terminal should have the cursor flashing at a blank mysql commandline. Within the CMDL copy & paste this:
它会提示你输入你的 Ubuntu 密码，输入它并 [ENTER]。
现在这是可以被认为是药物和/或解决问题的方法的步骤。您的终端应该是打开的，并且您应该在 MYSQL Server 中，在用户“root”下。终端应该让光标在空白的 mysql 命令行上闪烁。在 CMDL 中复制并粘贴：
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ChoosePassword';
mysql> FLUSH PRIVILEGES;
The next part is obvious, change 'ChoosePassword' to a password you will remember while leaving the password within single quotation marks. Change absolutley nothing else, press [ENTER]
If you followed the steps correctly, you now have a MySQL 'ROOT USER' with its own password now. Test it by copy and pasting the following at the Ubuntu CMDL:
下一部分很明显，将“选择密码”更改为您将记住的密码，同时将密码保留在单引号内。绝对没有改变，按[ENTER]
如果您正确地遵循了这些步骤，您现在就拥有了一个带有自己密码的 MySQL 'ROOT USER'。通过在 Ubuntu CMDL 中复制和粘贴以下内容来测试它：
~$: mysql -u root -p
- It will prompt you for your new password, type it and [ENTER]
- 它会提示您输入新密码，输入它并 [ENTER]
...you should be in, now exit.
……你应该进去了，现在退出。
mysql>exit
- Back to your 'testsql.js' file, alter the credentials to root for user, password to your password, a valid databaswe, and host to localhost, unless you have a unique need for a different host name.
- 返回到您的“testsql.js”文件，将用户凭据更改为 root，将密码更改为您的密码，将有效的数据库更改为主机，并将主机更改为 localhost，除非您对不同的主机名有独特的需求。
let connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '********',
database : 'DB_App_00',
});
now use node to run the node test file
现在使用 node 运行 node 测试文件
~$: node testsql.js
If it doesn't say connected you did somthing wrong, but if all went well, you should connect. It took some effort before I got it to work, but this answer should save you some time from reading all the other half written answers.
如果它没有说已连接，则说明您做错了，但如果一切顺利，则应该连接。在我开始工作之前需要付出一些努力，但是这个答案应该可以为您节省一些时间来阅读所有其他一半的书面答案。
回答by Mohammad Yousefi
in order to overcome this error use the following code:
为了克服此错误，请使用以下代码：
var connectionString = 'mysql://*root:*password@*localhost/*database?charset=utf8_general_ci&timezone=-0700';
var connection= mysql.createConnection(connectionString);
but make sure that you changed the * marks in the connectionString based on your setup.
但请确保根据您的设置更改了 connectionString 中的 * 标记。
回答by Abhay Katiyar
try the older version of mysql like 5.6.40, it uses by default SHA256_password auth where new version 8.0. uses by default sha2_password auth which is more secure and throw this authentication protocol error. MYSQL installer 5.6.40
尝试旧版本的 mysql 像 5.6.40，它默认使用 SHA256_password auth 其中新版本 8.0. 默认情况下使用更安全的 sha2_password auth 并抛出此身份验证协议错误。 MYSQL 安装程序 5.6.40
回答by Arshil
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '********',
database : 'vod_bill_database',
port : 3308
});
I had the same error and since i changed my port in phpmyadmin from 3306 to 3308 therefore here also i had to write port: 3308 and it started working.
我遇到了同样的错误，因为我将 phpmyadmin 中的端口从 3306 更改为 3308，因此在这里我也必须写入端口：3308 并开始工作。
回答by Ernesto
yellow yow bros !
黄色的兄弟们！
// mysql.ts
const pool = mysql.createPool({
connectionLimit: 10,
host: "localhost",
user: "root",
password: "password",
database: "rest-resume-api",
});
and then I have a docker-compose file such as
然后我有一个 docker-compose 文件，例如
# docker-compose.yml
version: '3.3'
services:
db:
image: mysql
restart: always
command: --default-authentication-plugin=mysql_native_password
environment:
MYSQL_DATABASE: 'rest-resume-api'
MYSQL_USER: 'root'
MYSQL_ROOT_PASSWORD: 'password'
ports:
- '3306:3306'
expose:
- '3306'
volumes:
- my-db:/var/lib/mysql2
volumes:
my-db:
