This post was originally written September 10, 2016 during a stressful time in my life - I couldn't access MySQL root. I'm migrating all my persona...
For further actions, you may consider blocking this person and/or reporting abuse
After upgrading MySQL from the previous version to 5.7, when I had tried to log in to the database as a non-sudo user it shows the following error. But I was enabled to login into the system as sudo user without any issues.
ERROR 1698 (28000): Access denied for user ‘root’@’localhost’.
You can use the following steps to beat MySQL Error 1698 (28000): Access denied for user ‘root’ @ ‘localhost’
In Ubuntu systems running MySQL 5.7 (and later), the root user is authenticated by the auth_socket plugin by default.
$ sudo mysql #No Username to be the provide
mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;
+——————+———————–+
| User | plugin |
+——————+———————–+
| root | auth_socket |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+——————+———————–+
mysql> UPDATE user SET plugin=’mysql_native_password’ WHERE User=’root’;
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ service mysql restart
Right Answer!!
If you want to login to your MySQL server as root from an external program such as phpMyAdmin you have two options.
The first one is to change the authentication method from auth_socket to mysql_native_password. You can do that by running the following command: ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘very_strong_password’;FLUSH PRIVILEGES;
Conclusion: Now you have learned to install troubleshoot the Access Denied error in MySql 5.7 and also you can check detailed solution from expresstechsoftwares.com/mysql-acc...
Your suggestion worked like a charm. Thanks for the help
Wow, this was a lifesaver! Thanks!
Thank you! thank you so much!
If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.
Short way to solve
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';
Then it disallows connecting via GUI tools as the MySQL Workbench with error:
You must reset your password using ALTER USER statement before executing this statement.To overcome this run
In the mysql shell.
Thank you for taking the time to write this, Erika. For me, knowing how to run MySQL in safe mode was the trick, after which it was trivial to reset the password for my
rootuser. Thanks for sharing!One must go into the
mysqldatabase before applying commands in Step 3I owe you a beer if I ever meet you! I have spent 5+ hours on this issue and your answer was the only one that worked!
Bless you ! May the rain of good luck keep showering upon you. Phewwwww