How to fix mysql_native_password not loaded errors on MySQL 8.4

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely.

This change affects PHP and other applications that use MySQL database with the mysql_native_password authentication plugin. Because the mysql_native_password plugin is no longer loaded by default or not available at all, PHP PDO/MySQLi connections fail.

When attempting to connect to the database using the mysql_native_password plugin that is no longer loaded, PDO/MySQLi throws the error returned by MySQL:

PDO:

SQLSTATE[HY000] [1524] Plugin 'mysql_native_password' is not loaded

MySQLi:

 mysqli_sql_exception  Plugin 'mysql_native_password' is not loaded.

On MySQL 8.0.34 through 8.3, using the mysql_native_password plugin resulted in warnings logged in the MySQL error log:

[Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

MySQL 8.4 makes the change to not load the mysql_native_password plugin anymore, which results in the errors shown above. On MySQL 9.0, the mysql_native_password plugin is removed completely, which also results in the same errors.

PHP has support for caching_sha2_password authentication since PHP 7.4. To fix this error, change the authentication plugin of the MySQL user to caching_sha2_password.

List MySQL Users using mysql_native_password

On a MySQL console, run the following to list users using the deprecated authentication plugin:

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

Running the above command should list all users that use the mysql_native_password plugin:

Update mysql_native_password users to caching_sha2_password

On a MySQL console with sufficient permissions, run the following command on each user using mysql_native_password plugin:

ALTER USER ''@'' IDENTIFIED WITH caching_sha2_password BY '';

After the authentication plugin is updated, PHP and other applications will be able to connect to the database over the caching_sha2_password plugin.

Re-enable MySQL Native Password Plugin

Although MySQL 8.4 no longer enables the mysql_native_password plugin by default, it is still possible to enable this plugin. It is not recommended to do it unless the PHP application is running PHP 7.3 or older versions, where it cannot use the caching_sha2_password plugin.

To enable the mysql_native_password plugin, add the following to the [mysqld] section of the MySQL configuration file and restart the MySQL server service.

On Debian/Ubuntu-based systems, this file is located in /etc/mysql/ directory. It is recommended to create a new file (named /etc/mysql/conf.d/enable-mysql-native-password.cnf, for example) for this.

SQLSTATE[HY000] [1524] Plugin 'mysql_native_password' is not loaded

Note that the mysql_native_password plugin is removed in MySQL 9.0, so adding the configuration above does not work and can cause a configuration error because the mysql_native_password configuration is no longer valid on MySQL 9.0.

The above is the detailed content of How to fix mysql_native_password not loaded errors on MySQL 8.4. For more information, please follow other related articles on the PHP Chinese website!