惠主机
在连接Docker容器中的MySQL数据库实例时，你是否遇到过无法加载身份验证插件“ caching_sha2_password”的错误提示？从MySQL 8.0开始，默认身份验证插件已从mysql_native_password更改为caching_sha2_password。如果你使用的是较旧的MySQL客户端，它可能无法连接到数据库服务器，并显示错误提示“无法加载身份验证插件’caching_sha2_password’”。
为了证实上述情况，本文将创建一个运行MySQL 8数据库服务器实例的Docker容器，并且Ubuntu 20.04 Linux服务器上执行此操作。这里小编首先安装Docker CE，然后创建MySQL 8 docker容器。
在Ubuntu上安装Docker CE
我将从在Ubuntu上安装Docker CE开始。使用最新的应用程序包索引：
sudo apt -y update
sudo apt -y install apt-transport-https ca-certificates curl gnupg-agent software-properties-common导入Docker仓库GPG密钥：
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -将Docker CE存储库添加到Ubuntu：
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"最后在Ubuntu上安装Docker CE：
sudo apt update
sudo apt -y install docker-ce docker-ce-cli containerd.io将用户帐户添加到docker组。
sudo usermod -aG docker $USER
newgrp docker通过检查Docker版本来验证安装：
$ docker version
Client: Docker Engine - Community
Version: 19.03.14
API version: 1.40
Go version: go1.13.15
Git commit: 5eb3275d40
Built: Tue Dec 1 19:20:26 2020
OS/Arch: linux/amd64
Experimental: false
Server: Docker Engine - Community
Engine:
Version: 19.03.14
API version: 1.40 (minimum version 1.12)
Go version: go1.13.15
Git commit: 5eb3275d40
Built: Tue Dec 1 19:18:53 2020
OS/Arch: linux/amd64
Experimental: false
containerd:
Version: 1.3.9
GitCommit: ea765aba0d05254012b0b9e595e995c09186427f
runc:
Version: 1.0.0-rc10
GitCommit: dc9208a3303feef5b3839f4323d9beb36df0a9dd
docker-init:
Version: 0.18.0
GitCommit: fec3683在Docker上运行MySQL数据库实例
现在可以基于MySQL 8基本映像创建一个Docker容器，首先创建数据目录：
mkdir ~/mysql_data启动数据库实例：
docker run -d \
--name mysql8 \
-p 3306:3306 \
-v ~/mysql_data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='RootUserPssw0rd' \
-e MYSQL_USER=app1 \
-e MYSQL_PASSWORD='app1Password' \
-e MYSQL_DATABASE=app1db \
mysql:8接下来确认容器正在运行：
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
368b02d943ad mysql:8 "docker-entrypoint.s…" 51 seconds ago Up 50 seconds 3306/tcp, 33060/tcp mysql8安装MariaDB客户端工具。
sudo apt install mariadb-client尝试以root用户身份连接到数据库服务器实例。
$ mysql -uroot -p'RootUserPssw0rd' -h 127.0.0.1
ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory上面可以看到已经出现“Plugin caching_sha2_password could not be loaded“.错误。
当然，如果使用较新版本的MySQL客户端（例如8），应该不会遇到此类问题。
$ mysql --version
mysql Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
# mysql -uroot -p'RootUserPssw0rd' -h 127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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>修复无法加载身份验证插件“ caching_sha2_password”错误的方法
1、检查正在运行的容器以选择MySQL容器。
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
72d48298731e mysql:8 "docker-entrypoint.s…" 10 minutes ago Up 10 minutes 0.0.0.0:3306->3306/tcp, 33060/tcp mysql82、连接到容器外壳。
$ docker exec -ti mysql8 bash
root@72d48298731e:/#3、使用运行期间在环境变量中传递的密码连接到MySQL Shell。
root@72d48298731e:/# mysql -uroot -p'RootUserPssw0rd'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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>4、更新身份验证插件
①、对于数据库root用户
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'RootUserPssw0rd';
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye
root@72d48298731e:/# exit
exit确认连接性：
$ mysql -uroot -p'RootUserPssw0rd' -h 127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>②、对于任何其他数据库用户
对于任何其他数据库用户，请使用以下命令语法：
ALTER USER 'dbusername' IDENTIFIED WITH mysql_native_password BY 'DBUserPassword';
# OR
ALTER USER 'dbusername'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'DBUserPassword';5、具体可以参见下面的示例
在该示例中我们将为先前创建的数据库用户更新身份验证插件。
mysql> ALTER USER 'app1'@'%' IDENTIFIED WITH mysql_native_password BY 'app1Password';mysq
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye
root@07164b718a3f:/# exit
exit\
$ mysql -u app1 -papp1Password -h 127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| app1db |
| information_schema |
+--------------------+
2 rows in set (0.008 sec)
MySQL [(none)]> \q
Bye
