前言

安装新的MySQL数据库后navicat无法连接到数据库。
⚠️：此片文章基于Mac操作系统


一、2059 - 错误

1、错误展示

连接navicat时错误代码如下

1
2059 - Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(../Frameworks/caching_sha2_password. so, 0x0002): tried: '../Frameworks/caching_sha2_password. so' (no such file), '/System/Volumes/Preboot/Cryptexes/OS../Frameworks/caching_sha2_password. so' (no such file), '/Applications/Navicat Premium中.app/Contents/Frameworks/../Frameworks/caching_sha2_password. so' (no such file), '/Applications/Navicat Premium中.app/Contents/Frameworks/../Frameworks/caching_sha2_password. so' (no such file), '/usr

2、错误说明

由于我所使用的navicat属于较老的12.0版本，并不支持MySQL8.0之后的新加密方式

3、解决方法

使用终端进行操作

如果解决过程中出现ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'错误，请见下面标题三三、ERROR 1396 (HY000)的解决方案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# 1、在终端中进入MySQL
➜  ~ mysql -uroot -p
-------------------------------------------------------------------------------------------

# 2、选择MySQL的系统库mysql（show databases;命令可查看所有数据库）
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
-------------------------------------------------------------------------------------------

# 3、查看mysql库user表中的root加密方式
mysql> select user,plugin from user where user='root';
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | caching_sha2_password |
+------+-----------------------+
1 row in set (0.01 sec)
-------------------------------------------------------------------------------------------

# 4、将root用户的加密方式由`caching_sha2_password`改为`mysql_native_password`
mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
-------------------------------------------------------------------------------------------

# 5、刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
-------------------------------------------------------------------------------------------

# 6、复查是否已经更新加密方式
mysql> select user,plugin from user where user='root';
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 row in set (0.00 sec)



二、1251 - 错误

1、错误展示

连接navicat时错误代码如下

1251 - Client does not support authentication protocol requested by server; consider upgrading MysQL client

2、错误说明

起初我以为2059错误是这个😄，因为都是因为版本问题需要修改加密方式；两者的解决方式都一样

3、解决方法（同一、2059 - 错误中的解决方式）

同一、2059 - 错误中的解决方式



三、ERROR 1396 (HY000)

1、错误展示

错误代码如下

ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@‘%’

2、错误说明

更改MySQL用户登录密码加密规则时，权限报错，需要将root用户的权限由localhost改为%。

3、解决方法

使用终端进行操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
# 1、在终端中进入MySQL
➜  ~ mysql -uroot -p
-------------------------------------------------------------------------------------------

# 2、选择MySQL的系统库mysql（show databases;命令可查看所有数据库）
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
-------------------------------------------------------------------------------------------

# 3、查看mysql库user表中的root用户权限
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)
-------------------------------------------------------------------------------------------

# 4、将root用户的权限由`localhost`改为`%`
mysql> update user set host = '%' where host = 'localhost' and user = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-------------------------------------------------------------------------------------------

# 5、刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
-------------------------------------------------------------------------------------------

# 6、复查是否已经更新权限
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.01 sec)




借鉴链接

2059 - Authentication plugin ‘caching_sha2_password‘ cannot be loaded: dlopen(…/Frameworks/caching_
更多关于MySQL的知识持续更新中!!!


声明

借鉴部分均注明了原文出处，可在文章的`借鉴链接`处获取原文出处
文中若内容有涉及原版权，请邮件联系elvin-chen@qq.com，涉及的相关文章或内容将会及时更改或取消发布