Changing the MySQL user authentication plugin

The mysql_native_password authentication plugin has been deprecated since MySQL version 8.0 and is also disabled in version 8.4.

Starting with MySQL version 9.0, the mysql_native_password plugin has been removed from the MySQL distribution and is no longer available for use.

Therefore, it is recommended to use the caching_sha2_password plugin. To migrate MySQL server users, update the data in the user table in the mysql database for each user that uses the deprecated plugin.

Below is a query to list all users that use the mysql_native_password plugin:

SELECT user, host, plugin FROM mysql.user where plugin = "mysql_native_password";

If you know the user's password, use the following request template for each individual user:

ALTER USER "<username>"@localhost IDENTIFIED WITH caching_sha2_password BY "<user_password>";

Below is a request to change the authentication plugin for all users that use mysql_native_password and generate random passwords for them.

Generating random passwords for database users will disrupt the operation of websites and applications connected to these databases!

It's required to change the passwords in the website files or notify users to enter current, valid passwords through the control panel in the Databases - Database users section, even if the Password field already contains a valid password. This is because the password in the control panel is not updated if it was manually changed through the database.

If you don't know the passwords of the users whose authentication method needs to be changed, bulk change the authentication method, but this will generate random passwords for these users:

SELECT 
    user,
    host,
    SUBSTRING(MD5(RAND()) FROM 1 FOR 16) AS new_password,
    CONCAT(
        "ALTER USER '", user, "'@'", host, 
        "' IDENTIFIED WITH caching_sha2_password BY '", 
        SUBSTRING(MD5(RAND()) FROM 1 FOR 16), 
        "';"
    ) AS alter_command
FROM mysql.user
WHERE plugin = 'mysql_native_password'
AND user NOT IN ('mysql.session', 'mysql.sys', 'mysql.infoschema');

To change the authentication plugin for a specific user back to the legacy one, use the following queries:

ALTER USER "<username>"@"localhost" IDENTIFIED VIA mysql_native_password USING PASSWORD("<user_password>");
FLUSH PRIVILEGES;

Database servers

Changing the MySQL user authentication plugin