The root user in MySQL server does not have an authentication string. This is preventing me to login as root.
See link:
https://gyazo.com/fef2073150114ae487f6a0b3b6cf3f96
What I have tried:
I have spent a lot of time trying to figure this out and have finally reached quite close to the solution as pointed out in this article:
https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/
Especially this line:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
But, when I run this on my command line I get the following:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test'; ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
Further down below in this article there is a comment by someone:
If the commande “ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘test’;” doesn’t work you can try :
UPDATE mysql.user SET authentication_string = PASSWORD(‘test’), plugin = ‘mysql_native_password’ WHERE User = ‘root’ AND Host = ‘localhost’; FLUSH PRIVILEGES;
When I try this I get the following:
mysql> UPDATE mysql.user SET authentication_string = PASSWORD(‘test’), plugin = ‘mysql_native_password’ WHERE User = ‘root’ AND Host = ‘localhost’; ERROR 1054 (42S22): Unknown column '‘root’' in 'where clause'
I can see the problem that my root user has no password set. That is why I cannot login as root and I have combed through all the threads here discussing that without any solution.
Here are some warnings that were helpful to me to find the above resources.
Root user has no authentication_string set
https://gyazo.com/fef2073150114ae487f6a0b3b6cf3f96
Any help is much appreciated.
Thanks.