Question 108368 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql 8.0.24 root password reset not working

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.

    ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

FLUSH PRIVILEGES;

    Then as the other threads, I can log in with my new password as root user. Hopes this help you.


  2. 0

    Change the root user’s authentication method to one that uses a password

    sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
exit

    and then try to login by password.

    You may change the root user’s authentication method back to the default

    mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
exit
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search