分享最新优惠信息
购买主机更加划算

无法加载身份验证插件“ caching_sha2_password”错误的简单解决方法

在连接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   mysql8

2、连接到容器外壳。

$ 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
未经允许不得转载:惠主机 » 无法加载身份验证插件“ caching_sha2_password”错误的简单解决方法