Mysql – locked from root and can’t change privileges on another user

MySQL

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.

Best Answer

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