What to do when #1698 - access denied for user 'root'@'localhost'?
I discovered that my phpmyadmin cannot login with my root password. So I updated the password as follow:
# mysql
mysql> UPDATE user set authentication_string=password('secret') 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> exit
Bye
After doing this I tried to connect with my new root password:
# sudo -u www-data mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
I don't understand why I am still denied.
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select USER(),CURRENT_USER();
+----------------+----------------+
| USER() | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)
I tried with:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
and it worked, but I have absolutely no idea of why any other methods do not work.
What is bizarre is that before I could connect in root with # mysql. Now I have to do # mysql -uroot -p. What did I change?
In case of #1698 - access denied for user 'root'@'localhost' -
0. login to MySQL
$ sudo mysql -u root
use mysql;
1. Change Password
UPDATE user SET authentication_string=PASSWORD('yourpass') WHERE User='root';
2.Change Plugin
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User='root';
3. Finish It
flush privileges;
exit
Extra info
If you get errors like: "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements" you can view the password rules with:
SHOW VARIABLES LIKE 'validate_password%';