Learn how to - Solve Unable to load authentication plugin ‘caching_sha2_password’

Learn how to – Solve Unable to load authentication plugin ‘caching_sha2_password’.

Did you get the error message unable to load authentication plugin ‘caching_sha2_password’ while trying to connect to a MySQL database instance in a Docker container?. As of MySQL 8.0 the default authentication plugin has been changed to caching_sha2_password from mysql_native_password. If you’re using an older MySQL client it may fail connecting to the database Server with error message “unable to load authentication plugin ‘caching_sha2_password’”.

To demonstrate this we’ll create a Docker container running MySQL 8 database server instance. I’m performing this operation on Ubuntu 20.04 Linux server but any other OS can be used. I’ll begin with the installation of Docker CE, then the creation of MySQL 8 docker containers.

Install Docker CE on Ubuntu

I’ll start with the installation of 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 Docker repository GPG key:

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

Add 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 docker group.

sudo usermod -aG docker $USER
newgrp docker

Run MySQL Database Instance on Docker

We can now create a Docker container based on MySQL 8 base image. Let’s create data directory:

mkdir ~/mysql_data

Start 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 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 connecting to the database server instance as 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 “Plugin caching_sha2_password could not be loaded“.

With a newer version of MySQL client such as 8 you should not experience such issue.

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

Fixing Unable to load authentication plugin ‘caching_sha2_password’

Check running containers to pick 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 container shell.

$ docker exec -ti mysql8 bash
root@72d48298731e:/#

Connect to MySQL shell with the password passed in environment variable during run.

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>

Update authentication plugin.

For root database user

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

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 user use the 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 example below where we are updating 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

root@07164b718a3f:/# exit
exit

Rerun the command to connect to 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

Cart

Learn how to – Solve Unable to load authentication plugin ‘caching_sha2_password’