-3

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.

Youshikyou
  • 365
  • 1
  • 8
  • According to the very same docs you linked the `alter user` command is not to be executed in the MySQL shell but within a `mysql-init` file. – derpirscher Apr 30 '23 at 07:54

2 Answers2

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
Majid Hajibaba
  • 3,105
  • 6
  • 23
  • 55
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.

Youshikyou
  • 365
  • 1
  • 8