This has been one of the most noticed changes when upgrading to MySQL 8.4. And for anyone that has made the upgrade without prior research, getting the MySQL native password not loaded error can certainly cause some headaches.
Please be aware that MySQL 9.0 will remove this plugin entirely!
If you didn’t know, the native password error is referring to an authentication plugin. And because MySQL 8.4 no longer loads this authentication plugin, it’ll stop your program connecting.
I’ve also written this on Medium!
The Error Messages – MySQL Native Password Not Loaded
You might get a slightly different error messages, depending on your method of connecting. You could see something like these examples:
mysqli_sql_exception Plugin 'mysql_native_password' is not loaded.
SQLSTATE[HY000] [1524] Plugin 'mysql_native_password' is not loaded
One saving grace, is that MySQL 8.0.34 to 8.3 actually display the following warning when logging in using the native authentication plugin:
[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'
How Can We Fix MySQL Native Passwords Not Loaded
From my research and experience, there are two methods for getting around this authentication deprecation.
How to Upgrade MySQL Community to MySQL Enterprise on Red Hat 9
Been a while since I made a post, so let’s jump straight back in with…
How to Setup WordPress Backups (FREE!)
Personally, I like my website. A lot. And I wouldn’t want one mistake to make…
Install NGINX on an Offline RHEL System – 2 Systems Required!
Been a while since my previous post, but let’s hope the magic hasn’t rubbed off…
MySQL 8.4 Replication with SSL on Ubuntu 22.02
So I recently wrote a post and made a YouTube video on setting up MySQL…
Gutenberg Just Broke my Website
More of a story and vent than an informative article. So to save you reading…
How to Design a Website in 5 Steps
You can either update the MySQL users stored in the database to use the caching_sha2_password authentication method, or you can tell MySQL to still load the native password authentication plugin.
I’d recommend updating the password for each user rather than loading the deprecated plugin, since MySQL 9.0 will completely remove the mysql_native_password option.
Let’s go through both options!
Update MySQL Users to caching_sha2_password
To update the authentication plugin for all MySQL users, we can first select all users currently using the mysql_native_password plugin using:
SELECT user, host, plugin FROM mysql.user WHERE plugin='mysql_native_password';
Once you know which users need updating, you can run this command to update them to the newer authentication plugin:
ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY 'password_here';
If you have a lot of users to update, I’m sure you can find a way to script this behaviour. If not, leave a command and I’ll help wherever possible.
Re-Enable MySQL Native Password Plugin
Since the mysql_native_password plugin is only removed in MySQL 9.0, we can still tell MySQL 8.4 to allow this plugin. This could be useful if you have a bunch of users that are affected, and you want to provide some breathing room before updating the database.
In order to do this, locate your MySQL configuration file. This is usually in the below locations:
Windows: C:\ProgramData\MySQL\MySQL Server 8.4\my.ini
Ubuntu: /etc/mysql/mysql.conf.d/mysqld.cnf
Open up this file and look for the [mysqld] section. Under this section, add the following config:
[mysqld]
#Enable the mysql_native_password plugin
mysql_native_password=ON
As I’ve warned above, this option will be removed entirely for MySQL 9.0. It’s likely this setting could result in an invalid config error, or MySQL 9.0 could just ignore it and display a warning. If it doesn’t display a warning, that could get confusing!
