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

Verify installation by checking 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

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

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

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

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

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

More on Databases:

Install 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

, , , , ,
Show Comments