Linux安装完mysql远程连接Authentication plugin

[root@iZ2ze8bpfv23icsz3g2hp2Z log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.18 MySQL Community Server - GPL

Copyright © 2000, 2019, 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> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)

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 user.Host=’%’ where user.User=‘root’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

本机远程连接发现身份认证出现问题
mysql8-2059 Authentication plugin ‘caching_sha2_password’ cannot be loaded
原因
mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password
解决方案
一种是升级navicat驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password.

mysql> alter user ‘root’@’%’ identified with mysql_native_password by ‘我的密码这个隐藏掉’;
Query OK, 0 rows affected (0.01 sec)

mysql> select user ,plugin from user
-> ;
±-----------------±----------------------+
| user | plugin |
±-----------------±----------------------+
| root | mysql_native_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session | caching_sha2_password |
| mysql.sys | caching_sha2_password |
±-----------------±----------------------+
4 rows in set (0.00 sec)

这里发现已经把root的远程登陆加密规则还原掉了
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

尝试远程登陆成功

mysql> ^C
mysql> exit
Bye
[root@iZ2ze8bpfv23icsz3g2hp2Z log]#