Authentication plugin 'caching_sha2_password&#

问题:

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?

回答1:

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

回答2:

you can change the encryption of the password like this.

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

回答3:

You can change the encryption of the user\'s password by altering the user with below Alter command :

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

OR

We can avoid this error by make it work with old password plugin:

First change the authentication plugin in my.cnf file for Linux / my.ini file in Windows:

[mysqld]

default_authentication_plugin=mysql_native_password

Restart the mysql server to take the changes in affect and try connecting via MySQL with any mysql client.

If still unable to connect and getting the below error:

Unable to load plugin \'caching_sha2_password\'

It means your user needs the above plugin. So try creating new user with create user or grant command after changing default plugin. then new user need the native plugin and you will able to connect MySQL.

Thanks

回答4:

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).

回答5:

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.

回答6:

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.

Clicking on the \"Reconfigure\" option it will allow to reconfigure the server. Click on \"Next\" until you reach \"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.

回答7:

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.

回答8:

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.

回答9:

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

回答10:

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

Courtesy : UDEMY FAQs answered by Career365 Team

回答11:

For Windows 10,

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

[mysqld] default_authentication_plugin=mysql_native_password

1. Restart the MySQL Service

2. login to MySQL in the command line, execute the following commands in MySQL

3. Create a new user

CREATE USER \'user\'@\'localhost\' IDENTIFIED BY \'password\';

1. Grant all privilages

GRANT ALL PRIVILEGES ON * .* TO \'user\'@\'localhost\';

1. Open MySQL workbench, open a new connection using the new user credentials

I was facing the same issue and this worked.

回答12:

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

回答13:

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\'

回答14:

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!)

回答15:

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\';

回答16:

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

回答17:

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.

回答18:

Open my sql command promt:

then enter mysql password

finally use:

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

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

Thanks.

回答19:

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