【用Navicat Premium 12连接mysql一报错】2059 - Authentication plugin ‘caching_sha2_password‘ cannot be loaded

用Navicat Premium 12连接mysql报错

 

一、报错：1130 - Host XXX is not allowed to connect to this MySQL server 错误提示的解决办法。

报错原因：此时的MySQL配置不支持远程连接 。
解决：修改配置，使之支持远程连接。

1、登录mysql

mysql -u root -p

2、按提示输入root密码

ffdeMacBook-Pro /Library % mysql -u root -p
Enter password: 

3、修改配置添加远程访问权限：update user set host = '%' where user ='root';将localhost设置为通配符%。localhost设置了“%”后便可以允许远程访问。

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
mysql> update user set host = '%' where user ='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

4、使配置生效
localhost修改完成后执行以下命令使配置立即生效。然后在查看配置

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>  select host from user where user='root';
+------+
| host |
+------+
| %    |
+------+
1 row in set (0.00 sec)

已成功修改，这个时候就可以连接了。

二、在 Mac 系统上，mysql 不允许远程连接。

发现，即使在本地使用 IP 也无法连接。那估计就是 mysql 服务绑定的 IP 有问题，要找到 mysql 的配置文件看看。

当时用 Homebrew 安装的 mysql，查看 brew info mysql ，没有找到 mysql 配置文件的位置，却有这样一句话：

MySQL is configured to only allow connections from localhost by default

查看 msyql --help ，mysql 提示会按照下面的顺序查找配置文件。

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

最终发现，使用 Homebrew 安装 mysql，默认配置在 /usr/local/etc/my.cnf，内容是：

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1

顾名思义，bind-addres的配置绑定了本地IP，所以远程无法连接。再看看 mysql 官方文档， bind-address 的配置是这样描述的：

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.

If the server was started with —bind-address=127.0.0.1, it will listen for TCP/IP connections only locally on the loopback interface and will not accept remote connections.

于是修改配置为： bind-address = 0.0.0.0
重启 mysql：brew services restart mysql

再次尝试远程连接。

三、2059 - Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(../Frameworks/caching_sha2_password.so, 2): image not found

解决方法：在控制台登陆后重新改下密码即可

1、先登录mysql：mysql -u root -p

2、输入密码

3、mysql> use mysql;

mysql> use mysql;
Database changed
mysql> select Host,User from user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

注意我的root，host是'%'

你可能执行的是:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';

改成:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

结果：

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';
Query OK, 0 rows affected (0.00 sec)

再试一下远程连接：