【用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)
再试一下远程连接:
相关文章
- MySQL Binlog Digger
- 连接MySql报错Unknown system variable 'query_cache_size'
- Pandas 中的 SQLAlchemy ,create_engine清理数据库连接,pandas读取及存储MySQL
- 解决Navicat连接MySQL时“Client does not support authentication protocol requested by server; consider upgrading MySQL client”的问题
- Brew 卸载MySql以及安装Mysql
- Mysql集群-----(附录2) Mysql 主从复制,从服务器无法连接问题
- 【Selenium项目实战】项目环境搭建:安装JDK、mysql、Tomcat、jpress和测试系统
- MySQL 调优基础(一) CPU与进程
- Mysql联合,连接查询
- MySQL基础之 存储引擎
- 局域网下共享 MySQL 数据库连接
- mysql远程连接 Host is not allowed to connect to this MySQL server
- 【MySQL】MySQL内连接和外连接详细总结
- Mysql数据类型DECIMAL(M,D)用法
- mysql 游标的使用方法
- liunux mysql MySQL表名不区分大小写的设置方法
- linux上lua操作MySql增删改查(centos7)
- MySQL数据库远程连接开启方法
- python 连接 mysql 的三种驱动
- MySQL读写分离
- MySQL连接控制插件介绍
- 【动软.Net代码生成器】连接MySQL生成C#的POCO实体类(Model)
- python连接mysql数据库把取数据存入数据库
- mysql面试题
- [Mysql] 多表连接查询