Mysql - Authentication plugin ：ERROR 2059

最新推荐文章于 2024-05-13 20:04:32 发布

weixin_34419326

最新推荐文章于 2024-05-13 20:04:32 发布

阅读量94

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/johnsken/blog/1840348

本文介绍了解决MySQL 8.x版本中出现的“caching_sha2_password”密码认证插件问题的方法。通过更改root用户的密码加密方式为mysql_native_password，可以有效解决客户端连接失败的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

mysql - ERROR 2059: Authentication plugin 'caching_sha2_password'

MySQL Connection Failed

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/Volumes/Video/Downloads/mysql-connector-c-6.1.9-src/install/lib/plugin/caching_sha2_password.so, 2): image not found (2059)

Double-check your MySQL user name and password are correct, and that the MySQL user has valid privileges to connect from your current machine's address.

问题分析

Client does not support authentication protocol requested by server; consider upgrading MySQL client
注意： Authentication plugin 'caching_sha2_password' cannot be loaded
原因：密码加密方式【caching_sha2_password】，客户端不支持。
这个问题是由于我的mysql更新至8版本以上了，在安装的时候我并没有指定用户登入密码加密方式，所以默认被设置为 caching_sha2_password

解决方法

在终端输入命令

// 终端进入mysql
JerrydeMacBook-Pro:~ jerrylam$ mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.11 Homebrew

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

// 查看加密的方式
mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.03 sec)

// 查看本地mysql用户的信息
mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

// 注意 caching_sha2_password， 只修改root的密码加密方式就可以了
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.04 sec)

mysql> **select host,user,plugin from mysql.user;**
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

转载于:https://my.oschina.net/johnsken/blog/1840348