新安装mysql 8.0,在用户登录时提示这样的错误
Failed to connect to MySQL server: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/xtrabackup/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory.
在网上搜了下,发现主要是因为mysql 8.0的默认认证方式有了调整,可以选择不同的plugin。
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
修改默认认证方式
默认的认证方式是 caching_sha2_password
mysql> show global variables like '%default_auth%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)
修改
# vi /etc/my.cnf
[mysqld]
default_authentication_plugin = mysql_native_password
修改已有用户的认证方式
mysql> select user,host,authentication_string,plugin,account_locked from mysql.user;
mysql> alter user 'root'@'192.168.56.%' identified with mysql_native_password by 'MyNewPass@123' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.39 sec)
直接修改
mysql> alter user 'root'@'192.168.56.%' identified with mysql_native_password by 'MyNewPass@123' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.39 sec)
caching_sha2_password、mysql_native_password
下面的问题源自mysql 官方文档
Important
In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. For information about the implications of this change for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.
参考:
https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html