[mysql] Authentication plugin 'caching_sha2_password' cannot be loaded

I am connecting MySQL - 8.0 with MySQL Workbench and getting the below error:

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

I have tried with other client tool as well.

Any solution for this?

This question is related to mysql database mysql-workbench mysql-8.0

The answer is


This is my databdase definition in my docker-compose:

dataBase:
    image: mysql:8.0
    volumes:
        - db_data:/var/lib/mysql
    networks:
        z-net:
            ipv4_address: 172.26.0.2
    restart: always
    entrypoint: ['docker-entrypoint.sh', '--default-authentication-plugin=mysql_native_password']
    environment:
        MYSQL_ROOT_PASSWORD: supersecret
        MYSQL_DATABASE: zdb
        MYSQL_USER: zuser
        MYSQL_PASSWORD: zpass
    ports:
        - "3333:3306"

The relevant line there is entrypoint.

After build and up it, you can test it with:

$ mysql -u zuser -pzpass --host=172.26.0.2  zdb -e "select 1;"
Warning: Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

MySQLWorkbench 8.0.11 for macOS addresses this. I can establish connection with root password protected mysql instance running in docker.


This error comes up when the tool being used is not compatible with MySQL8, try updating to the latest version of MySQL Workbench for MySQL8


I was installing MySQL on my Windows 10 PC using "MySQL Web Installer" and was facing the same issue while trying to connect using MySQL workbench. I fixed the issue by reconfiguring the server form the Installer window.

MySQL Web Installer - Home Screen

Clicking on the "Reconfigure" option it will allow to reconfigure the server. Click on "Next" until you reach "Authentication Method".

MySQL Installer - Authentication Method

Once on this tab, use the second option "Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)".

Keep everything else as is and that is how I solved my issue.


Open my sql command promt:

step 1

then enter mysql password

step 2

finally use:

ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

refer:https://stackoverflow.com/a/49228443/6097074

Thanks.


The below solution worked for me enter image description here

Go to Mysql Workbench -> Server-> Users and Privileges 1.Click Add Account

2.Under Login Tab provide new details and make sure to choose the Authentication Type as standard and choose respective administrative roles and Schema Privileges

enter image description here


Ok, wasted a lot of time on this so here is a summary as of 19 March 2019

If you are specifically trying to use a Docker image with MySql 8+, and then use SequelPro to access your database(s) running on that docker container, you are out of luck.

See the sequelpro issue 2699

My setup is sequelpro 1.1.2 using docker desktop 2.0.3.0 (mac - mojave), and tried using mysql:latest (v8.0.15).

As others have reported, using mysql 5.7 works with nothing required:

docker run -p 3306:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:5.7

Of course, it is possible to use MySql 8+ on docker, and in that situation (if needed), other answers provided here for caching_sha2_password type issues do work. But sequelpro is a NO GO with MySql 8+

Finally, I abandoned sequelpro (a trusted friend from back in 2013-2014) and instead installed DBeaver. Everything worked out of the box. For docker, I used:

docker run -p 3306:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:latest --default-authentication-plugin=mysql_native_password

You can quickly peek at the mysql databases using:

docker exec -it mysql1 bash

mysql -u root -p

show databases;

Note: For Linux (Debian, Ubuntu, Mint)

I got this error:

MySQL Error Message: 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

I solved with this steps:

  1. Enter on mysql console: $ mysql -u root -p, if you don't has a password for root user, then:

  2. Use mysql db: mysql> use mysql;

  3. Alter your user for solve the problem: mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

  4. Exit... mysql> quit;

  5. Done!


Note: For MAC OS

  1. Open MySQL from System Preferences > Initialize Database >
  2. Type your new password.
  3. Choose 'Use legacy password'
  4. Start the Server again.
  5. Now connect the MySQL Workbench

Image description


I had the same problem, but the answer by Aman Aggarwal didn't work for me with a Docker container running mysql 8.X. I loged in the container

docker exec -it CONTAINER_ID bash

then log into mysql as root

mysql --user=root --password

Enter the password for root (Default is 'root') Finally Run:

ALTER USER 'username' IDENTIFIED WITH mysql_native_password BY 'password';

You're all set.


If you are trying to connect to a MySQL server from a text-based MySQL client from another computer (be it Docker or not)

Most answers here involve connecting from a desktop client, or ask you to switch to an older authentication method. If you're connecting it with the MySQL client (text-based), I made it work with a Debian Buster in a Docker container.

Say you have the apt system and wget set up, do the following:

  1. sudo apt-get update
  2. sudo apt-get install lsb-release -y
  3. Download a Debian package which update apt sources for you from the MySQL web site.
  4. sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb and select the options you want. In my case I only need MySQL Tools & Connectors to be enabled.
  5. sudo apt-get update
  6. sudo apt-get install mysql-client -y
  7. Done. You can now run the new MySQL client and connect with the new authentication method.

I solved this problem by installing MySQL 5.7:

Step 1 – Enable MySQL Repository

First of all, You need to enable MySQL 5.7 community release yum repository on your system. The rpm packages for yum repository configuration are available on MySQL official website. Use on of below command as per your operating system version.

On CentOS and RHEL 7

yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

On CentOS and RHEL 6

yum localinstall https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm

On Fedora 27

dnf install https://dev.mysql.com/get/mysql57-community-release-fc27-9.noarch.rpm

On Fedora 26

dnf install https://dev.mysql.com/get/mysql57-community-release-fc26-9.noarch.rpm

On Fedora 25

dnf install https://dev.mysql.com/get/mysql57-community-release-fc25-9.noarch.rpm

Step 2 – Install MySQL 5.7 Server

As you have successfully enabled MySQL yum repository on your system. Now, install MySQL 5.7 community server using following commands as per your operating system version.

On CentOS and RHEL 7/6

yum install mysql-community-server

On Fedora 27/26/25

 dnf install mysql-community-server

source: https://tecadmin.net/install-mysql-5-7-centos-rhel/


I found that

ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

didn't work by itself. I also needed to set

[mysqld]
    default_authentication_plugin=mysql_native_password

in /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu 18.04 running PHP 7.0


Rolling back to the previous installations (to MySQL Community Server 5.7 and Workbench 6.1)and setting up new MySQL credentials worked for me!


Here is the solution which worked for me after MySQL 8.0 Installation on Windows 10.

Suppose MySQL username is root and password is admin

Open command prompt and enter the following commands:

cd C:\Program Files\MySQL\MySQL Server 8.0\bin

mysql_upgrade -uroot -padmin

mysql -uroot -padmin

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin'


Currently (on 2018/04/23), you need to download a development release. The GA ones do not work.

I was not able to connect with the latest GA version (6.3.10).

It worked with mysql-workbench-community-8.0.11-rc-winx64.msi (from https://dev.mysql.com/downloads/workbench/, tab Development Releases).


For those using Docker or Docker Compose, I experienced this error because I didn't set my MySQL image version. Docker will automatically attempt to get the latest version which is 8.

I set MySQL to 5.7 and rebuilt the image and it worked as normal:

version: '2'
services: 
  db:
   image: mysql:5.7

For Windows 10:

Open the command prompt:

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -u root -p
Enter password: *********

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newrootpassword';
Query OK, 0 rows affected (0.10 sec)

mysql> exit

Alternatively, you can change the my.ini configuration as the following:

[mysqld]

default_authentication_plugin=mysql_native_password

Restart the MySQL Server and open the Workbench again.


  • Open MySQL Command Line Client

  • Create a new user with a new pass

Considering an example of a path to a bin folder on top, here's the code you need to run in the command prompt, line by line:

cd C:\Program Files\MySQL\MySQL Server 5.7\bin
MySQL -u root -p    
current password...***  
CREATE USER 'nativeuser'@'localhost'  
IDENTIFIED WITH mysql_native_password BY 'new_password';
  • Then, you can access Workbench again (you should be able to do that after creating a new localhost connection and using the new credentials to start using the program).

Set up a new local host connection with the user name mentioned above (native user), login using the password (new_password)

Image

Courtesy: UDEMY FAQs answered by Career365 Team


like this?

docker run -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql --default-authentication-plugin=mysql_native_password
mysql -uroot --protocol tcp

Try in PWD

https://github.com/GitHub30/docs/blob/change-default_authentication_plugin/mysql/stack.yml

or You shoud use MySQL Workbench 8.0.11.


Try using legacy password while downloading and installing MySql, that helped me. Or follow the method posted by Santhosh Shivan for Mac OS.


you can change the encryption of the password like this.

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

Just downloaded the latest mysqlworkbench which is compatible with the latest encryption:

https://downloads.mysql.com/archives/workbench/

Note: On Mac big Sur, the latest two versions: 8.0.22 and 8.0.23 are buggy and do not work.

Use 8.0.21 until these are fixed


Although this shouldn't be a real solution, it does work locally if you are stuck

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

For Windows 10,

  1. Modify my.ini file in C:\ProgramData\MySQL\MySQL Server 8.0\

    [mysqld]
    default_authentication_plugin=mysql_native_password
    
  2. Restart the MySQL Service.

  3. Login to MySQL on the command line, and execute the following commands in MySQL:

    • Create a new user.

      CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
      
    • Grant all privileges.

      GRANT ALL PRIVILEGES ON * .* TO 'user'@'localhost';
      
  4. Open MySQL workbench, and open a new connection using the new user credentials.

I was facing the same issue and this worked.


Actually MySql allows two type of authentication at the time of installation.

  1. Password Encryption
  2. Legacy Encryption

enter image description here

Read Here

So by checking legacy authentication the issue was resolved.


For me this started happening because on a project, I was using Docker image mysql:latest (which was version 5, and which was working fine), and during a later build, the latest version was switched to version 8, and stopped working. I changed my image to mysql:5 and I was no longer getting this error.


Almost like answers above but may be in simple queries, I was getting this error in my spring boot application along with hibernate after MySQL upgrade. We created a new user by running the queries below against our DB. I believe this is a temp work around to use sha256_password instead of latest and good authentication caching_sha2_password.

CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pa$$word';

GRANT ALL PRIVILEGES ON * .* TO 'username'@'localhost';

If you are getting this error on GitLab CI like me: Just change from latest to 5.7 version ;)

# .gitlab-ci.yml

rspec:
  services:
    # - mysql:latest (I'm using latest version and it causes error)
    - mysql:5.7 #(then I've changed to this specific version and fix!)

Downloading a development release of 8.0.11-rc worked for me on a mac. with the following docker commands:

docker run --name mysql -p 3406:3306 -e MYSQL_ROOT_PASSWORD=mypassword -d mysql

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to mysql-workbench

Authentication plugin 'caching_sha2_password' cannot be loaded Cannot connect to MySQL Workbench on mac. Can't connect to MySQL server on '127.0.0.1' (61) Mac Macintosh MySQL Workbench not displaying query results Can't connect to MySQL server on '127.0.0.1' (10061) (2003) How to unblock with mysqladmin flush hosts How to create localhost database using mysql? MySQL Workbench - Connect to a Localhost MySQL Workbench not opening on Windows How to view table contents in Mysql Workbench GUI? Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

Examples related to mysql-8.0

PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client How to grant all privileges to root user in MySQL 8.0 phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded How do you use the "WITH" clause in MySQL?