locked from root and can't change privileges on another user

Question

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.

Answer 1

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';