0

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.

1
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.

2
  • Sorry couldn't get to this earlier. I tried and get this message when issuing the ALTER USER-cmd: ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
    – Ulrich
    May 20 at 18:20
  • Have you tried creating new user in skip-grant-tables mode ? May 21 at 2:02
0

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.

4
  • Sorry, I do not get what you mean. When is sudo unnecessary? In the "sudo mysql" command? How can I issue commands with root privilege when not being authenticated as "root"? And the question her is, how can I authenticate as root.
    – Ulrich
    May 20 at 20:44
  • The OS "root" is distinct from the mysql "root". (OK, 8.0 has a way to tie them together; did you do that?)
    – Rick James
    May 20 at 20:49
  • I'm aware, that both of the roots are different. My OS-root even does not have the name "root". And I occasionally found that the "sudo mysql" worked - for whatever reason I cannot tell - when "mysql -u -root -p" didn't.
    – Ulrich
    May 22 at 9:33
  • Aside from installing, users should not need to know anything about the "user" involved in the installation of "mysql" (mysqld, mysql, mysqldump, etc). And normally all users can execute mysql without anything like "sudo".
    – Rick James
    May 22 at 22:03
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.