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-commonImport 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 dockerRun 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_dataStart 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 mysql8Install MariaDB client tools.
sudo apt install mariadb-clientTry 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 directoryWe 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 mysql8Connect 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
exitConfirm 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
exitRerun 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
ByeMore on Databases:
