Supplying root password to MySQL V8 sort of fails

Question

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.

Answer 1

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.

Answer 2

sudo is unnecessary. (If it is necessary, something else is going wrong.)

Do not use that UPDATE with 8.0, only use GRANT and associated commands.

Note that using "passwords" out of fashion in 8.0 unless you specify that the user is using native_passwords.

Answer 3

Gave up about this issue. Reinstalled the whole system and used MariaDB instead of MySQL. For this installation all worked fine and the root-user has got now a password.