"Authentication plugin 'caching_sha2_password' cannot be loaded. The specific module can not be found"

Before following all these instructions to downgrade your server security, make sure you have the latest version of MySQL Workbench! I kept getting that error when I tried running Workbench on an old computer. It had an older version of Workbench installed on it. I ran MySQL Installer and upgraded Workbench to the latest version and stopped getting that error.

The easy way around would be to reconfigure your MySQL server with a new authentication method. Just open the MySQL Installer community, and click on reconfigure next to the product MySQL Server. Keep clicking the 'Next' button until you see the authentication method window.

In this window ensure that the 'Use Legacy Authentication Method' option is selected. If not, select that option and proceed with the reconfiguration without changing any more settings. This will handle all errors that you may face when connecting to MySQL from Excel or R, etc.

In your specific case, it could be because your server is not running. To handle that, right-click on This PC on your computer and click on 'Manage'. Select 'Services and Applications' and then 'Services'. Scroll down the list that appears until you see your MySQL server. Click on the service and then click on start the service.

1. In your text editor of choice, open (or create) the /usr/local/etc/my.cnf file and add the following to the [mysqld] section of the file:

   default-authentication-plugin=mysql_native_password

2. Open a terminal window, open an SSH session to your naked Mac Mini Server, and enter the following at the shell prompt:

   mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';

Where NEWPASSWORD is the password you want to assign to the MySQL root user.

1. exit
2. Reboot your Mac.

Contents of this post is taken from farces.com

You have to "mysql_native_password" here to connect or else you have to configure "caching_sha2_password" plugin properly, as new MySQL comes with "caching_sha2_password" as below:

DROP USER 'your_user_name'@'localhost';
CREATE USER 'your_user_name'@'%' IDENTIFIED WITH mysql_native_password BY 'your_user_password';
GRANT ALL PRIVILEGES ON <db_name>.* TO 'your_user_name'@'%' identified by 'your_user_password';