Client does not support authentication protocol requested by server

用户2146693

发布于 2020-05-06 19:20:21

6340

发布于 2020-05-06 19:20:21

文章被收录于专栏：架构师进阶架构师进阶

背景

环境：MAC Docker, Windows10 Docker

Mysql版本：8.0.19

启动命令：docker run -p 3307:3306 --name 8mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:latest

Navicat：11.0.19

现象：

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

解决方案

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

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

flush privileges;

原因

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password.

mysql8.0以后，默认加密plugin，是caching_sha2_password。

需要改为Navi cat支持的mysql_native_password。

mysql -u root -p

use mysql;

select host,user,plugin from user;

+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+

执行完后：

+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+