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.
What’s next?
I’ll be redoing my snapshot / clone for the mysql02 database. That approach will be:
- sudo service jail stop mysql02
- sudo zfs rename data02/jails/mysql02 data02/jails/mysql02.old
- zfs snapshot data02/jails/mysql01@mysql80-part2
- zfs clone data02/jails/mysql01@mysql80 -part2 data02/jails/mysql02
- sudo service jail start mysql02
- repeat the stuff in this post
Thank you for coming to my TED talk.
