MYSQL无法访问8.0数据库 Authentication plugin 'caching_sha2_password'

 

mysql> set global validate_password.length = 6;

mysql> set global validate_password.policy = LOW;

# 授权所有主机都可以通过root用户，密码123456，进行访问数据库
# 123456：给新增权限用户设置的密码
# %：代表所有主机，也可以具体到主机ip地址
# MySQL 8.0之后的版本，需要先创建一个用户，再进行授权

create user root@'%' identified by '123456';
grant all privileges on *.* to root@'%' with grant option;

 

[root@VM-4-11-centos ~]# mysql -h 124.222.13.22 -P 3306 -u admin -p
Enter password:
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
[root@VM-4-11-centos ~]# mysql -h 124.222.13.22 -P 3306 -u admin_user -p

 

查看8.0的 状态  需要跟 mysql5.7的兼容  mysql_native_password  如果是 caching_sha2_password 则不兼容

mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | admin | caching_sha2_password |
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+-----------+------------------+-----------------------+

 

CREATE USER 'admin_user'@'%' IDENTIFIED WITH mysql_native_password BY 'MyNewPass4!';
GRANT ALL PRIVILEGES ON your_database.* TO 'admin_user'@'%';
FLUSH PRIVILEGES;

 

新增用户 设置密码 格式 mysql_native_password  在测试已经可以了

 

 

[root@VM-4-11-centos ~]# mysql -h 124.222.13.22 -P 3306 -u admin_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>