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)
