Resolving the MySQL Error: ‘caching_sha2_password’ Authentication Plugin Issue

Are you encountering the frustrating Authentication plugin ‘caching_sha2_password’ cannot be loaded error when trying to connect to MySQL 8.0, especially through SQLyog? Fret not! This article will guide you through the process of resolving this error and getting your MySQL connection up and running.

Understanding the Error

The error message Authentication plugin ‘caching_sha2_password’ cannot be loaded typically occurs when there is a mismatch between the authentication method used by MySQL and the method expected by the client application, such as SQLyog. MySQL 8.0 introduced the ‘caching_sha2_password’ authentication plugin, which may not be supported by some older client applications.

The Solution

To resolve this issue and make your MySQL 8.0 database compatible with SQLyog or similar applications, you can configure MySQL to use the ‘mysql_native_password’ authentication plugin, which is supported by most client applications. Here’s how you can do it:

Solution 1: Changing Password Encryption via Command Prompt

1. Open the command prompt:
   cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

   1 2 3 4 5

   cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
2. Log in to MySQL as the root user:
   C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -u root -p Enter password: *********

   1 2 3 4 5 6

   C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -u root -p Enter password: *********
3. Enter your MySQL root password when prompted.
4. Alter the user to use ‘mysql_native_password’ for authentication. Replace ‘yourpassword‘ with your actual MySQL desired password:
   mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newrootpassword'; Query OK, 0 rows affected (0.10 sec)

   1 2 3 4 5 6

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

5. Exit MySQL:
   mysql> exit

   1 2 3 4 5

   mysql> exit

   
    

Solution 2: Modifying my.ini Configuration

1. Open the ‘my.ini’ file (usually found in the MySQL installation directory) using a text editor.
2. Add the following line under the [mysqld] section to set the default authentication plugin to ‘mysql_native_password’:
   [mysqld] default_authentication_plugin = mysql_native_password

   1 2 3 4 5 6 7

   [mysqld]   default_authentication_plugin = mysql_native_password

3. Save the ‘my.ini’ file.
4. Restart the MySQL server to apply the changes.

Note: If you need to allow remote connections, replace ‘localhost‘ with ‘%‘ in the ALTER USER command. This change will enable your user to connect to the database from any host.

By following these straightforward steps, you can resolve the “Authentication plugin ‘caching_sha2_password’ cannot be loaded” error and configure your MySQL 8.0 database to work seamlessly with SQLyog and other client applications. Ensuring compatibility between your database and your preferred tools is essential for smooth development and data management. Remember to back up your MySQL database before making any changes, and consult your database administrator or developer for guidance if needed. Happy data management!

This set of instructions is for Windows 10 operating system, while the following is for MacOS.

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