Supplying root password to MySQL V8 sort of fails

0 votes
3 answers
1228 views
                          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. 





                        
Asked by Ulrich (101 rep)
May 20, 2021, 07:28 AM
Last activity: May 22, 2021, 07:37 PM
Supplying root password to MySQL V8 sort of fails

Related Questions
