今天使用navicat连接MySQL8.0 的时候,出现如下报错提示:
ERROR 2059 (HY000): Authentication plugin ‘caching_sha2_password’ cannot be loaded: …
猜测是插件问题,排查下:
SELECT `user`, `host`, `authentication_string`, `plugin` FROM mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| root | % | $A$005$==t@l=SP'G{U[1})D8yLwA6ti2uHtmUKNuHxQSUggrBRMBR2CheCw0Oxad9 | caching_sha2_password |
| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
修改arhiver账号的密码验证插件类型:
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
再查询看下:
SELECT `user`, `host`, `authentication_string`, `plugin` FROM mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| root | % | *13D295FD7B8108ABBC89FCDDD342FFBFF5DA803C | mysql_native_password |
| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
再次使用navicat连接就正常了。