背景
最近使用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 oldmysql_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 withnpm 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;