New MySQL Authentication Change Will Break Things!

A new change to the authentication of MySQL v9.0 is going to end up breaking lots of things and will be a headache for system database administrators.

Administrators will see a new error message stating: Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead.

The problem is: there is currently no known way to convert users to the new method without knowing the user's password, and how many folks will remember their database passwords? It just takes a simple line of SQL to convert the account, but finding all of the passwords and their use cases will be a nightmare.

To identify the users on the system who need to be updated, execute the following SQL on your MySQL server:

SELECT user, host, plugin from mysql.user WHERE plugin='mysql_native_password';

And then using the user's password, convert the authentication from mysql_native_password to caching_sha2_password with the following SQL:

ALTER USER '<USERNAME>'@'<HOST>' IDENTIFIED WITH caching_sha2_password BY '<PASSWORD>';

You'll obviously replace USERNAME, HOST, and PASSWORD with the appropriate values.