前言

安装新的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,涉及的相关文章或内容将会及时更改或取消发布