Updated MySQL 8.0 to 8.4 - can't get root password properly set

[dvl@]

I updated a MySQL 8.0.45 instance to MySQL 8.4.8 - along the way, I encountered the "'mysql_native_password’ is not loaded" situation.

I was able to follow my own blog post to get the non-root passwords updated. However, I still can't get the root password for MySQL to work.

If I remove mysql_native_password=ON from /usr/local/etc/mysql/my.cnf, I am unable to get in as root:

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

That tells me I haven't yet updated the root password (i.e. it's still using the old format).

Adding that native setting back in & restarting MySQL:

[20:15 zuul-mysql dvl ~] % mysql -u root -p mysql
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Confirming, the password is indeed not yet set.

Yet:

[20:16 zuul-mysql dvl ~] % mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.4.8 Source distribution

Copyright (c) 2000, 2026, 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.

root@localhost [(none)]> SELECT user, host, plugin from mysql.user WHERE plugin='mysql_native_password';
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
root@localhost [(none)]>

I also tried https://dev.mysql.com/doc/refman/8.4/en/resetting-permissions.html (B.3.3.2.2 Resetting the Root Password: Unix and Unix-Like Systems)

echo '/usr/local/bin/mysqld_safe --init-file=/usr/home/dvl/new-pass.mysql' | sudo su -fm mysql

where that file contains:

[20:18 zuul-mysql dvl ~] % cat /usr/home/dvl/new-pass.mysql                                                             
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'foo';
ALTER USER 'root'@'::1'       IDENTIFIED WITH caching_sha2_password BY 'foo';
ALTER USER 'root'@'mysql'     IDENTIFIED WITH caching_sha2_password BY 'foo';
ALTER USER ''@'localhost'     IDENTIFIED WITH caching_sha2_password BY 'foo';
ALTER USER ''@'mysql'         IDENTIFIED WITH caching_sha2_password BY 'foo';

I have also tried mysql_args="--skip-grant-tables" within /etc/rc.conf:

That seems to have the most success:

[20:20 zuul-mysql dvl ~] % mysql -u root mysql                 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.4.8 Source distribution

Copyright (c) 2000, 2026, 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.

root@localhost [mysql]> SELECT user, host, plugin from mysql.user WHERE plugin='mysql_native_password';
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
|      | localhost | mysql_native_password |
|      | mysql     | mysql_native_password |
+------+-----------+-----------------------+
2 rows in set (0.01 sec)

root@localhost [mysql]>

However, I have been unable to update those passwords, I think because I don't know the format:

root@localhost [mysql]> alter user ''@'localhost' IDENTIFIED WITH caching_sha2_password BY 'foo';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Or more likely, MySQL won't let me run that command, because ... it's being a prat.

 

[Alain De Vos]

sorry i cannot reply, using always and always mariadb. Even if my make.conf. Give it a try...

 

[Alain De Vos]

Performance: MariaDB generally outperforms MySQL in replication and complex queries due to its more efficient query optimizer and better thread pooling.

 

[VladiBG]

After you start the MySQL without grant tables you need to load it using "FLUSH PRIVILEGES;" and after that you can reset the password.

From 8.0.35 Native password is replaced with caching_sha2_password.

Post in thread 'MySQL 8.0.35 and mysql_native_password'

Dec 9, 2023

You should test it first. Create a user with caching_sha2_password

CREATE USER 'sha2user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

It will be the same as:

CREATE USER 'sha2user'@'localhost'' IDENTIFIED BY 'password';

After your upgrade the MySQL

Then try to auth with it from the app that you are using like PHP

And if you can't log in you can change the password back to mysql_native_password

ALTER USER 'sha2user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

As of MySQL 8.0.34, the...

• VladiBG

•