I know this topic is discussed in many threads but I still cannot find the solution for my case.
I am using mysql 8.0.24 in centOS 8.0 and trying to reset my root password. Here is my user table.
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
I first started with mysql docs with below command.
`ALTER USER 'root'@'localhost' IDENTIFIED BY '111';`
But I got error, ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
And I tried with
update mysql.user set authentication_string='111' where user='root';
I got Query OK, 1 row affected (0.00 sec)
.
Then I did
FLUSH PRIVILEGES;
I checked the table with
mysql> SELECT user,authentication_string FROM mysql.user;
+------------------+------------------------------------------------------------------------+
| user | authentication_string |
+------------------+------------------------------------------------------------------------+
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | 111 |
Then I kill the safemode and start in normal model with
systemctl start mysqld
mysql -u root -h localhost -p
When I type the password 111, it says
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
I really don’t know why this happened. First of all, the official docs syntax doesn’t work for my version. I used the update command. Nothing works.
2
Answers
I just solved by using another method. Thank the people for answering my question. In case someone tracks to here. I would like to provide them with some ideas. What I did is I drop the root user in the safe mode according to this thread's the highest score answer cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04. Below command works for me. Someone might not know what is Identified with caching_sha2_password. You can select 'plug in' the user table. It tells you.
Then as the other threads, I can log in with my new password as root user. Hopes this help you.
Change the root user’s authentication method to one that uses a password
and then try to login by password.
You may change the root user’s authentication method back to the default