Docer MySQL Authentication plugin 'caching_sha2_password' cannot be loaded:

Docker MySQL 终端无法连接

问题描述

在使用Docer安装MySQL容器后，在终端（Sequel Pro、Navcat）无法连接。

错误日志

Connection failed!
Unable to connect to host 47.98.150.85, or the request timed out.
Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).
MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

问题分析

出现该问题的原因是因为我使用的MySQL镜像版本是8.0以上的。而MySQL 8.0默认的认证方式是：caching_sha2_password，之前版本的认证方式是：mysql_native_password。
这种情况下一些连接工具的jdbc连接尚未升级，所以会出现上述问题。
其次，在Java程序中，也将连接失败。

解决方案

创建容器时，加载默认认证方式

在创建MySQL容器时，传入默认的认证方式--default-authentication-plugin=mysql_native_password，完整命令如下：

docker run --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d -p 3306:3306 mysql   --default-authentication-plugin=mysql_native_password

创建容器后，修改默认认证方式

这种方式在创建容器时，不需要传入默认认证试--default-authentication-plugin=mysql_native_password。使用如下方式创建MySQL容器：

docker run --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d -p 3306:3306 mysql

通过docker exec -it mysql bash进入容器，并登录MySQL数据库：

[root@izbp13xko46hud9vfr5s94z ~]# docker exec -it mysql bash 
root@551ffc60c933:/# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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>

之后，切换至mysql数据库，对原有数据库信息进行修改。
使用use mysql切换数据库, 如下：

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>

查看当前数据库认证方式：

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

mysql>

可以看出当前数据库root用户认证方式是caching_sha2_password。
修改认证方式：

mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.06 sec)

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

mysql> 

至此，便可解决此问题。个人更推荐使用第一种方式。

参考链接：
https://www.cnblogs.com/atuotuo/p/9402132.html
https://dev.mysql.com/doc/refman/5.7/en/caching-sha2-pluggable-authentication.html