最近mac 升级安装mysql后遇到一系列问题,稍作记录。
1、升级8以后,使用sequelpro 连接报错
MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found
原因: caching_sha2_password, 是mysql8.0的一个新特性, 默认使用该身份认证插件进行加密. 但是一些客户端, 诸如sequel pro, Navicat 不支持这种插件, 这个时候就会出现如上错误。
解决方法:
在终端登录mysql -u root -p账户密码
修改登陆账户,使用mysql_native_password加密密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
2、使用sequelpro连接mysql 后,查看数据库,或者切换数据库,始终在loading ,出现一种假死状态。
原因:mysql8.0升级后造成的。
3、无法使用远程连接
原因:MySQL默认绑定了ip:127.0.0.1。
解决方法:
修改ip绑定
#修改配置文件vi /usr/local/etc/my.cnf
#注释掉ip-address选项
[mysqld]
# Only allow connections from localhost
#bind-address = 127.0.0.1
2. 将登陆账号host设置为%
首先了解一下mysql.user 表
~ » mysql -uroot -p Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 19Server version:8.0.19Homebrew
mysql>use mysql
Reading table informationforcompletion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql> selectuser,Host,authentication_string,plugin from user;+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | Host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| test | % | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | mysql_native_password |
| test0 | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root | localhost | $A$005$J})udp=0Q2mo/U/lyljEgy6h0gn3lUxydvreeLg.qJ5QJp7BwlSA4ttZ40 | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.00 sec)
Host字段中% 表示允许任何ip地址访问,localhost只允许本地访问。