Mysql – Supplying root password to MySQL V8 sort of fails

alter-tableMySQLpasswordroot

On Ubuntu 20.04 I have installed MySQL V8.0.25. But I kind of fail to apply a (valid) root password. The apt-get process for installing mysql-server did not ask for a root password.
And I cannot enter mysql with the "mysql -u roor -p" command, I always do with "sudo mysql".

So i first tried with the process described by this MySQL page. But this did not work at all, because V8 seems not to support the PASSWORD-function.
So instead of running

UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

I used this:

UPDATE mysql.user 
   SET authentication_string = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('MyNewPass'))))), password_expired = 'N' 
   WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

which I found described here.

The query
select host, user, authentication_string, password_expired from mysql.user;
shows a nice table:

   +-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | joomla           | $A$005$MF`6ea"OfH5v1kTuRW0zJS5MKk82btugdAz62uWe6QkxnrXtTLtx5M. | caching_sha2_password |
| localhost | debian-sys-maint | $A$005$l%.r}2CBQT:+DV)a9S/UJUDJoFA8PhnCIE.E3zDFbBeUZ5vTrNSZpZDDv05 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
s%nn69n9�NkFf7xoPdW/CCD/NjvLhTKXtx8gQmTX.RpIbOcHWsA. | caching_sha2_password |
| localhost | root             | mypass                                                                 | auth_stock            |  
+-----------+------------------+------------------------------------------------------------------------+-----------------------+

but still "mysql -u root -p" does not work with the suppiel password.
I still get message:

$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I also tried this – without any success:

UPDATE mysql.user 
   SET authentication_string = 'mypass', password_expired = 'N', plugin = '' 
   WHERE User = 'root' AND Host = 'localhost';

The ALTER cmd did not work

neither

ALTER USER 'root'@'localhost' IDENTIFIED BY 'oldpass';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

nor

ALTER USER 'root'@'localhost' IDENTIFIED BY 'oldpass' REPLACE 'mypass';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

I spent now many hours in this and have no clue how to preceed. Any help is appreciated.

Best Answer

Step 1 : Add below parameter in my.cnf file under [mysqld] section.

skip-grant-tables

Step 2 : Restart MySQL instance.

Step 3 : Login MySQL instance. (This will not ask you password)

mysql -uroot -p

Step 4 : Reset root password.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewRoot@123Pass';

Step 5 : Remove skip-grant-tables parameter from my.cnf file & restart MySQL instance.

Step 6 : Now Login MySQL instance using root user & new password.

Related Question