Sequel pro 连接mysql 报MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded...
简单描述起因
由于是因为mysql升级后默认使用的身份验证机制默认为caching_sha2_password方式,以前用的是mysql_native_password 方式,然而现在的Sequel pro版本 就用的是mysql_native_password 方式 。
解决办法
有两种解决办法,如果是用官网dmg安装包的形式安装就比较方便,直接在设置页面进行设置,另外一种就是在设置页面没有mysql选项,通过命令的方式进行解决。第一种能搜索出特别多解决办法,但是另外一种还是比较少的,这里注重介绍第二种
- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';
如下:
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
- 修改validate_password.policy
set global validate_password.policy=0;
如下:
mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)
3.修改validate_password.length
set global validate_password.length=1;
如下:
mysql> set global validate_password.length=1;
Query OK, 0 rows affected (0.01 sec)
4.查看修改
SHOW VARIABLES LIKE 'validate_password%';
如下:
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------+
length 为4 因为mysql长度最短是4位
5.修改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin' PASSWORD EXPIRE NEVER;
如下:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)
admin是密码
6.更新用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';
如下:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';
Query OK, 0 rows affected (0.01 sec)
7.刷新权限
FLUSH PRIVILEGES;
如下:
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
end