Solve the problem that the authentication plug-in “caching_sha2_password” cannot be loaded

You can download this article in PDF format via the link below to support us.
Download the guide in PDF formatturn off

Did you get an error message Could not load authentication plugin “caching_sha2_password” When trying to connect to a MySQL database instance in a Docker container? Starting from MySQL 8.0, the default authentication plugin has been changed from mysql_native_password to caching_sha2_password. If you are using an older MySQL client, it may not be able to connect to the database server and display the error message “Unable to load the authentication plugin’caching_sha2_password'”.

To demonstrate this, we will create a Docker container running a MySQL 8 database server instance. I am performing this operation on an Ubuntu 20.04 Linux server. I will install Docker CE first, and then create a MySQL 8 docker container.

Install Docker CE on Ubuntu

I will start by installing Docker CE on Ubuntu. Use the latest application package index:

sudo apt -y update
sudo apt -y install apt-transport-https ca-certificates curl gnupg-agent software-properties-common

Import the Docker repository GPG key:

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

Add the Docker CE repository to Ubuntu:

sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"

Finally, install Docker CE on Ubuntu:

sudo apt update
sudo apt -y install docker-ce docker-ce-cli containerd.io

Add your user account to the docker group.

sudo usermod -aG docker $USER
newgrp docker

Verify the installation by checking the Docker version:

$ 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

Run MySQL database instance on Docker

Now, we can create a Docker container based on the MySQL 8 base image. Let’s create the data directory:

mkdir ~/mysql_data

Start the database instance:

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

Let’s confirm that the container is running:

$ 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

Install MariaDB client tools.

sudo apt install mariadb-client

Try to connect to the database server instance as the root user.

$ 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

We can confirm the error “Unable to load plugin caching_sha2_password”.

With a newer version of the MySQL client (such as 8), you should not encounter such problems.

$ 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>

Fix that the authentication plugin “caching_sha2_password” could not be loaded

Check the running container to select the MySQL container.

$ 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

Connect to the container shell.

$ docker exec -ti mysql8 bash
[email protected]:/#

Connect to MySQL Shell using the password passed in the environment variable during runtime.

[email protected]:/# 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>

Update the authentication plugin.

For root database users

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'RootUserPssw0rd';
Query OK, 0 rows affected (0.01 sec)

mysql> q
Bye

[email protected]:/# exit
exit

Confirm connectivity:

$ 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)]>

For any other database users

For any other database users, use the following command syntax:

ALTER USER 'dbusername' IDENTIFIED WITH mysql_native_password BY 'DBUserPassword';
# OR
ALTER USER 'dbusername'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'DBUserPassword';

See the example below, where we will update the authentication plugin for the database user we created earlier.

mysql> ALTER USER 'app1'@'%' IDENTIFIED WITH mysql_native_password BY 'app1Password';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> q
Bye

[email protected]:/# exit
exit

Re-run the command to connect to the MySQL instance.

$ 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

More information about the database:

Install a single-node TiDB database cluster on CentOS 8

Install and configure Apache Kafka on Ubuntu

Install and configure Apache Kafka with CMAK on CentOS 8

How to install MySQL Workbench on Ubuntu

You can download this article in PDF format via the link below to support us.
Download the guide in PDF formatturn off

Related Posts