背景

最近使用Node连接数据库的时候,控制台报错:

Client does not support authentication protocol requested by server; consider upgrading MySQL client

遂寻找了出错的原因

原因

MySQL 8 has supports pluggable authentication methods. By default, one of them named caching_sha2_password is 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!

Now, the problem is mysqljs in Node (the package you install with npm i mysql and 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/1507 and is still open, after 3 years, as of July 2019.

在网上有看到这样一段描述,翻译过来,大概意思就是 MySQL 8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password,但是在Node中的mysqljs还不支持mysql8默认的新规则。 所以将账户的加密规则改为旧的即可

查看用户信息

select host,user,plugin,authentication_string from mysql.user;

果然,当前的方式是chching_sha2_password

解决方式

账户登录mysql

root账户为例,这里可以为其他账户

mysql -u root -p

更改root加密规则

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

root就是上一步登录的root账户,当然如果使用其他账户也可以; password替换成你想要修改的密码

更新权限

flush privileges;