Change Default Authentication Plugin in MySQL

In MySQL 8.0, the default authentication plugin is caching_sha2_password rather than mysql_native_password.

If application get errors related with caching_sha2_password plugin, it is possible that connector does not support this plugin yet.

The default authentication plugin is defined by the default_authentication_plugin system variable. We can use the SHOW VARIABLES statement to determine which authentication plugin is default.

SHOW VARIABLES LIKE 'default_authentication_plugin';

+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+

The default authentication plugin can be changed in MySQL configuration file. After that, MySQL server must be restarted.

[mysqld]
default_authentication_plugin=mysql_native_password

The authentication plugin which will be used to authenticate the user is stored in the mysql.user system table.

SELECT user, host, plugin, authentication_string FROM mysql.user;

+--------+-----------+-----------------------+----------------------------------------------+
| user   | host      | plugin                | authentication_string                        |
+--------+-----------+-----------------------+----------------------------------------------+
| myuser | localhost | caching_sha2_password | $A$005$}-(RWHh"|[v`cXyMqYNSSeEANm5... |
| root   | localhost | mysql_native_password | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B    |
+--------+-----------+-----------------------+----------------------------------------------+

We can create the user and set its password without specifying the authentication plugin. In this case, the default authentication plugin will be used.

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'pwd123';

When creating a new user, we can specify the authentication plugin explicitly by using the IDENTIFIED WITH statement.

CREATE USER 'myuser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'pwd123';

The IDENTIFIED WITH statement sets the user authentication plugin and clears the credentials to the empty string. So, when changing the user authentication plugin, we need to specify the plugin and the password at the same time.

ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'psw123';