0

I was getting error 1698 when I was trying to log into root

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

I did this to fix it

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('woofwoof')  WHERE  User='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

but that didn't work and now I've been getting error 1045 when I try to log into root. The top answer here https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost to my same problem says its because of anonymuos users, but I don't have any.

orpheus@Roedelius:~$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

these are all my users, none are anonymous, I think.

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| orpheus          | localhost |
| root             | localhost |
| tom              | localhost |
+------------------+-----------+

so I tried granting all privileges to root and I get error 1698 again

mysql> grant all privileges on *.* to root@localhost identified by 'woofwoof' with grant option;
ERROR 1698 (28000): Access denied for user 'orpheus'@'localhost'

when I try to see grants for root I get this

mysql> show grants for 'root'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host 'localhost'

but for my other user, orpheus, I get this

mysql> show grants for 'orpheus'@'localhost';
+------------------------------------------------------+
| Grants for orpheus@localhost                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'orpheus'@'localhost' |
+------------------------------------------------------+

I don't know why this is happening, but I just want to access root again. I would appreciate any help with this.

  • UPDATE mysql.user SET authentication_string Why you do not use ALTER USER? WHERE User='root' Do you understand you alter ALL records with this name and any host? Rows matched: 1 Changed: 1 Warnings: 1 Why you do NOT view the warning? – Akina Aug 2 '18 at 4:33
0

This solution belongs to the previous version of MySQL. By logging in to MySQL using socket authentication, you can do it.

sudo mysql -u root

Then the following command could be run.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '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.