ERROR 1524 (HY000): Plugin ‘mysql_native_password’ is not loaded

Earlier today, I copied the mysq01 jail to a new mysql02. That’s all part of my migrating from 8.0 to MySQL 8.4 jail adventure.

In this post:

FreeBSD 14.3
MySQL 8.4.7

I hit a problem.

I couldn’t log in. The password procedure has changed.

[18:58 mysql02 dvl ~] % mysql -u root -p
Enter password: 
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

I found and used Fixing mysql_native_password is not loaded in MySQL 8.4+

In short:

add mysql_native_password=ON to my.cnf
restart MySQL

Then it worked:

[19:04 mysql02 dvl /var/db/mysql] % mysql -u root -p                    
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.7 Source distribution

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Next, I need to adjust all users to the new password scheme. I tried, and seemingly failed:

mysql> alter user root IDENTIFIED WITH caching_sha2_password BY 'foo';
Query OK, 0 rows affected (0.02 sec)

Nothing changed? Thanks.

This is the full list for reference:

mysql> SELECT user, host, plugin from mysql.user WHERE plugin='mysql_native_password';
+---------------+-------------------------------+-----------------------+
| user          | host                          | plugin                |
+---------------+-------------------------------+-----------------------+
| root          | %                             | mysql_native_password |
| rsyncer       | %                             | mysql_native_password |
| nagiosql_user | 10.55.0.116                   | mysql_native_password |
| nagiosql_user | 10.55.0.116';                 | mysql_native_password |
| librenms      | 10.55.0.27                    | mysql_native_password |
| librenms_dvl  | 10.55.0.31                    | mysql_native_password |
| root          | 127.0.0.1                     | mysql_native_password |
| root          | ::1                           | mysql_native_password |
| librenms      | besser.int.unixathome.org     | mysql_native_password |
| fruityext     | fruit-ext.int.unixathome.org  | mysql_native_password |
| fruityint     | fruit-int.int.unixathome.org  | mysql_native_password |
| fruityext     | fruity-ext.int.unixathome.org | mysql_native_password |
| fruityint     | fruity-int.int.unixathome.org | mysql_native_password |
|               | localhost                     | mysql_native_password |
| root          | localhost                     | mysql_native_password |
| rsyncer       | localhost                     | mysql_native_password |
+---------------+-------------------------------+-----------------------+
16 rows in set (0.00 sec)

Let’s just concentrate on 10.55.0.116

mysql> alter user 'nagiosql_user'@'10.55.0.116' IDENTIFIED WITH caching_sha2_password BY 'foo';
Query OK, 0 rows affected (0.02 sec)

Damn, not fixed.

After trying lots of different things, including:

mysql> alter user nagiosql_user@10.55.0.116 IDENTIFIED WITH caching_sha2_password BY 'foo';
Query OK, 0 rows affected (0.02 sec)

mysql> alter user nagiosql_user@10.55.0.116 IDENTIFIED BY 'foo';
Query OK, 0 rows affected (0.02 sec)

I finally ran this same query again:


mysql> SELECT user, host, plugin from mysql.user WHERE plugin='mysql_native_password';
+---------------+-------------------------------+-----------------------+
| user          | host                          | plugin                |
+---------------+-------------------------------+-----------------------+
| rsyncer       | %                             | mysql_native_password |
| nagiosql_user | 10.55.0.116';                 | mysql_native_password |
| librenms      | 10.55.0.27                    | mysql_native_password |
| librenms_dvl  | 10.55.0.31                    | mysql_native_password |
| root          | 127.0.0.1                     | mysql_native_password |
| root          | ::1                           | mysql_native_password |
| librenms      | besser.int.unixathome.org     | mysql_native_password |
| fruityext     | fruit-ext.int.unixathome.org  | mysql_native_password |
| fruityint     | fruit-int.int.unixathome.org  | mysql_native_password |
| fruityext     | fruity-ext.int.unixathome.org | mysql_native_password |
| fruityint     | fruity-int.int.unixathome.org | mysql_native_password |
|               | localhost                     | mysql_native_password |
| rsyncer       | localhost                     | mysql_native_password |
+---------------+-------------------------------+-----------------------+
13 rows in set (0.00 sec)

mysql>

You’ve got to be kidding. Where did my first nagiosql_user go?

Let’s try another.

mysql> alter user 'rsyncer'@'%' IDENTIFIED WITH caching_sha2_password BY 'foo';
Query OK, 0 rows affected (0.02 sec)
mysql> 

mysql> SELECT user, host, plugin from mysql.user WHERE plugin='mysql_native_password';
+---------------+-------------------------------+-----------------------+
| user          | host                          | plugin                |
+---------------+-------------------------------+-----------------------+
| nagiosql_user | 10.55.0.116';                 | mysql_native_password |
| librenms      | 10.55.0.27                    | mysql_native_password |
| librenms_dvl  | 10.55.0.31                    | mysql_native_password |
| root          | 127.0.0.1                     | mysql_native_password |
| root          | ::1                           | mysql_native_password |
| librenms      | besser.int.unixathome.org     | mysql_native_password |
| fruityext     | fruit-ext.int.unixathome.org  | mysql_native_password |
| fruityint     | fruit-int.int.unixathome.org  | mysql_native_password |
| fruityext     | fruity-ext.int.unixathome.org | mysql_native_password |
| fruityint     | fruity-int.int.unixathome.org | mysql_native_password |
|               | localhost                     | mysql_native_password |
| rsyncer       | localhost                     | mysql_native_password |
+---------------+-------------------------------+-----------------------+
12 rows in set (0.00 sec)

mysql>

OK, that worked, despite the claim it affect nothing.

This just adds to my list of reasons I prefer PostgreSQL. Please stop wasting my time.

The tricky ones

I kept plugging away, one by one, and eventually I was left with this:

mysql> SELECT user, host, plugin from mysql.user WHERE plugin='mysql_native_password';
+---------------+---------------+-----------------------+
| user          | host          | plugin                |
+---------------+---------------+-----------------------+
| nagiosql_user | 10.55.0.116'; | mysql_native_password |
|               | localhost     | mysql_native_password |
+---------------+---------------+-----------------------+
2 rows in set (0.00 sec)

mysql>

I stopped the jail, took a snapshot, and started it back up.

Then I started trying the hard stuff.

Before:

mysql> SELECT user, host, plugin from mysql.user order by plugin, user, host;
+------------------+-------------------------------+-----------------------+
| user             | host                          | plugin                |
+------------------+-------------------------------+-----------------------+
| fruityint        | fruity-int.int.unixathome.org | caching_sha2_password |
| librenms         | 10.55.0.27                    | caching_sha2_password |
| librenms         | besser.int.unixathome.org     | caching_sha2_password |
| librenms_dvl     | 10.55.0.31                    | caching_sha2_password |
| mysql.infoschema | localhost                     | caching_sha2_password |
| mysql.session    | localhost                     | caching_sha2_password |
| mysql.sys        | localhost                     | caching_sha2_password |
| nagiosql_user    | 10.55.0.116                   | caching_sha2_password |
| new_user         | your_host                     | caching_sha2_password |
| root             | %                             | caching_sha2_password |
| root             | 127.0.0.1                     | caching_sha2_password |
| root             | ::1                           | caching_sha2_password |
| root             | localhost                     | caching_sha2_password |
| rsyncer          | %                             | caching_sha2_password |
| rsyncer          | localhost                     | caching_sha2_password |
|                  | localhost                     | mysql_native_password |
| nagiosql_user    | 10.55.0.116';                 | mysql_native_password |
+------------------+-------------------------------+-----------------------+
17 rows in set (0.00 sec)

mysql>

Now, for the risky deletes.

mysql> drop user ''@'localhost';
Query OK, 0 rows affected (0.02 sec)
mysql> drop user 'nagiosql_user'@'10.55.0.116\';';
Query OK, 0 rows affected (0.02 sec)

Again, notice how it claims nothing was changed. That’s terrible. Do not lie.

mysql> SELECT user, host, plugin from mysql.user order by plugin, user, host;
+------------------+-------------------------------+-----------------------+
| user             | host                          | plugin                |
+------------------+-------------------------------+-----------------------+
| fruityint        | fruity-int.int.unixathome.org | caching_sha2_password |
| librenms         | 10.55.0.27                    | caching_sha2_password |
| librenms         | besser.int.unixathome.org     | caching_sha2_password |
| librenms_dvl     | 10.55.0.31                    | caching_sha2_password |
| mysql.infoschema | localhost                     | caching_sha2_password |
| mysql.session    | localhost                     | caching_sha2_password |
| mysql.sys        | localhost                     | caching_sha2_password |
| nagiosql_user    | 10.55.0.116                   | caching_sha2_password |
| new_user         | your_host                     | caching_sha2_password |
| root             | %                             | caching_sha2_password |
| root             | 127.0.0.1                     | caching_sha2_password |
| root             | ::1                           | caching_sha2_password |
| root             | localhost                     | caching_sha2_password |
| rsyncer          | %                             | caching_sha2_password |
| rsyncer          | localhost                     | caching_sha2_password |
+------------------+-------------------------------+-----------------------+
15 rows in set (0.00 sec)

mysql>

There, I’m done. I can reverse the my.cnf changes.

Not shown here: removal of mysql_native_password=ON from /usr/local/etc/mysql/my.cnf.

[21:49 mysql02 dvl ~] % sudo service mysql-server restart   
Stopping mysql.
Waiting for PIDS: 2441.
Starting mysql.
[21:50 mysql02 dvl ~] % mysql -u root -p mysql           
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.7 Source distribution

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

There, it works. I’m glad.