Error ‘mysqli::real_connect(): (HY000/1524): Plugin ‘mysql_native_password’ is not loaded’ (SOLVED)

ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
How to switch a user from mysql_native_password to another authentication plugin
Solution for ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded in Windows
Solution to ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded on Linux
Resolve ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded on macOS/Homebrew
Resolve ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded in mysql docker tag
Alternative way to start MySQL without checking the password of connecting users
How to find all users using mysql_native_password
Choose UPDATE mysql.user SET or ALTER USER?

ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

After upgrading to MySQL 9, you may encounter an error that mentions mysql_native_password.

The error occurs on various operating systems, and it does not matter whether you use the MySQL client to connect to the DBMS or phpMyAdmin.

This is what the error looks like if you are using phpMyAdmin:

phpMyAdmin, I get this error: 'mysqli::real_connect(): (HY000/1524): Plugin 'mysql_native_password' is not loaded'

An example of a log where the problem is more clearly stated:

2024-09-19T08:37:19.574570Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-09-19T08:37:19.680765Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2024-09-19T08:37:19.681254Z 0 [System] [MY-010116] [Server] C:\Server\bin\mysql\bin\mysqld (mysqld 9.0.1) starting as process 16512
2024-09-19T08:37:19.721573Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-19T08:37:20.395562Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-19T08:37:20.965885Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-09-19T08:37:20.966671Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-09-19T08:37:20.988637Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'root'@'localhost' is not loaded. Nobody can currently login using this account.
2024-09-19T08:37:21.004935Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2024-09-19T08:37:21.004981Z 0 [System] [MY-010931] [Server] C:\Server\bin\mysql\bin\mysqld: ready for connections. Version: '9.0.1'  socket: ''  port: 3306  MySQL Community Server - GPL.

This is part of a log from a Windows OS (although a similar problem has been reported by macOS/Homebrew users, and users who use MySQL in a Docker container, and apparently, regardless of the installation method, when upgrading to MySQL 9, you may encounter this error.

The key message in the MySQL service startup log is the following:

The plugin 'mysql_native_password' used to authenticate user 'root'@'localhost' is not loaded. Nobody can currently login using this account.

When trying to connect to MySQL on the command line:

mysql -u root

An error is displayed again mentioning mysql_native_password.

ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

As you might have guessed, the mysql_native_password plugin, which is involved in user authentication, was removed from MySQL 9. And root (or even all users) use this authentication method. That's why login fails.

How to switch a user from mysql_native_password to another authentication plugin

There are different ways to solve this problem, for example, some users use the MariaDB client to connect, which has support for the mysql_native_password plugin, and downgrading to MySQL 8 will also work.

But, in my opinion, the best option is to switch root and other users to the authentication plugins supported in MySQL 9. But how can you do this if you cannot log in?

You can use the same technique that is used to reset the password in MySQL and MariaDB. Below is a method for solving the problem in various operating systems.

Solution for ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded in Windows

Open the first terminal window with administrator rights. To do this, press Win+x and select Terminal (Admin). In the first terminal, stop the MySQL process:

net stop mysql

Here, go to the executable file and start the MySQL process with the --skip-grant-tables option:

cd C:\Server\bin\mysql-9.0\bin\
.\mysqld --skip-grant-tables --skip-networking --shared-memory

Please note that on my computer, the MySQL files are located in the C:\Server\bin\mysql-9.0\ directory – if you have a different path, edit the first command accordingly.

Without closing the first terminal, open a second terminal window and connect to MySQL:

cd C:\Server\bin\mysql-9.0\bin\
.\mysql -u root

Then, after connecting to the DBMS, execute the following SQL queries:

USE mysql;
UPDATE user SET plugin = 'caching_sha2_password' WHERE User = 'root';
FLUSH PRIVILEGES;
exit;

Go back to the first terminal window – stop the MySQL process (press Ctrl+c to do this) and then start the service normally:

net start mysql

You can now close all terminal windows – the problem with mysql_native_password should be gone.

Solution to ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded on Linux

Stop the DBMS process:

sudo systemctl stop mysql

If you start MySQL without loading the user privilege information, it will allow you to access the database command line with root privileges without identifying the user.

To do this, you need to prevent the database from loading the privilege tables, which contain the user privilege information. Since this is a security risk, you should also avoid network activity to prevent other clients from connecting.

Start the database without loading the grant tables and without network access:

sudo mysqld_safe --skip-grant-tables --skip-networking &

The ampersand at the end of this command will make the process run in the background, so you can continue to use your terminal.

If you see the error

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

Then run the following commands:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

And run it again:

sudo mysqld_safe --skip-grant-tables --skip-networking &

Now you can connect to the database as the root user, who will not be asked for a password.

mysql -u root

You will immediately see the database shell prompt.

Then, after connecting to the DBMS, execute the following SQL statements:

USE mysql;
UPDATE user SET plugin = 'caching_sha2_password' WHERE User = 'root';
FLUSH PRIVILEGES;
exit;

Stop the database server instance that you started manually. The following command looks for the PID or process ID of the MySQL or MariaDB process and sends a SIGTERM to tell the process to exit gracefully after it has completed its cleanup operations.

sudo kill `sudo cat /var/run/mysqld/mysqld.pid`

Then restart the service using systemctl.

sudo systemctl start mysql

Now you can confirm that the new password works, run:

mysql -u root -p

Resolve ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded on macOS/Homebrew

Run the following commands:

brew services stop mysql
mysqld_safe --skip-grant-tables &
mysql -u root
USE mysql;
UPDATE user SET plugin = 'caching_sha2_password' WHERE User = 'root';
EXIT;
killall mysqld
brew services start mysql

Resolve ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded in mysql docker tag

Add the following option to your docker compose file, then rebuild the container.

command: --skip-grant-tables

Connect to the mysql container shell as root. I did this via the portainer web interface. Type:

mysql

then press Enter and you should be able to connect to the mysql instance.

After that, run the following commands:

FLUSH PRIVILEGES;

The following SQL query is used to display all users identified by the mysql_native_password plugin:

SELECT User,Host,plugin FROM mysql.user WHERE plugin='mysql_native_password';

For each user and host, run the following command:

ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY 'new_password';

At the very end, run again:

FLUSH PRIVILEGES;

And to exit, run the command:

exit;

Remove from docker-compose

command: --skip-grant-tables

then recreate the container.

After completing the above steps, you should be able to connect to the MySQL instance again.

Alternative way to start MySQL without checking the password of connecting users

If the command line method shown above seemed confusing to you, here is another option. You can specify the skip-grant-tables option in the MySQL configuration file in the [mysqld] section, so it should look like this:

[mysqld]
skip-grant-tables

If the [mysqld] section exists, then simply add the skip-grant-tables option. If the [mysqld] section does not exist, then add both of the above lines.

The MySQL configuration file is usually called my.ini or my.cnf.

Examples of the location of this file in Linux:

/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf

In macOS, the path to the file can be as follows:

/opt/homebrew/etc/my.cnf

Example path to the MySQL configuration file in Windows:

C:\Server\bin\mysql-9.0\my.ini

After making changes to the file, you need to restart the MySQL service so that it starts using the new file.

Restart the MySQL service in Linux:

sudo systemctl restart mysql

Restart the MySQL in macOS:

brew services restart mysql

Restart the MySQL service in Windows:

net stop mysql
net start mysql

Then connect to the MySQL server using the MySQL client. Depending on the operating system, the commands differ slightly:

mysql -u root
.\mysql -u root
C:\Server\bin\mysql-9.0\bin\mysql -u root

Then run the following SQL queries:

USE mysql;
UPDATE user SET plugin = 'caching_sha2_password' WHERE User = 'root';
FLUSH PRIVILEGES;
exit;

When you are done, you need to edit the MySQL configuration file again, namely, return it to its original state and remove the skip-grant-tables option. After you edit and save the configuration file, restart the MySQL service again (as shown above) for all new changes to take effect.

How to find all users using mysql_native_password

After connecting to MySQL, you can find all users using mysql_native_password using the following SQL query:

SELECT User,Host,plugin FROM mysql.user WHERE plugin='mysql_native_password';

For each of them, run the following SQL query (replace root with the user name):

UPDATE user SET plugin = 'caching_sha2_password' WHERE User = 'root';

Choose UPDATE mysql.user SET or ALTER USER?

To change user settings, you can choose one of the following commands:

UPDATE mysql.user SET plugin = 'caching_sha2_password' WHERE User = 'root';

Or:

ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY 'new_password';

You may have already noticed these commands in different instructions.

ALTER USER is a specialized command for changing user settings.

But since user settings are stored in the user table in the mysql database, these settings can be changed using the universal “UPDATE” command.

There is no fundamental difference which command to choose – you can use either of them. But there are still some nuances.

If you chose ALTER USER, you must specify the password:

ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY 'new_password';

If you do not specify the password, then instead of saving the old password (as you might expect), it will reset to login without a password:

ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password

And another important difference of ALTER USER is that you must specify the host. Remember that the host values can be different. You can display all users and their hosts with the following SQL query:

SELECT User,Host,plugin,authentication_string FROM mysql.user;

Example from Windows (MySQL 9):

Example from Linux (MariaDB 11.5.2):

If you select “UPDATE mysql.user SET”, you will not be able to change the password, but you will be able to fine-tune other user settings, such as the plugin used for authentication, as shown above.

Note that if you do not specify the host, the settings in “UPDATE mysql.user SET” are applied to all users with the same name but different hosts. You can change this by explicitly specifying the host:

UPDATE mysql.user SET plugin = 'caching_sha2_password' WHERE User = 'root' AND Host = 'localhost';