39

I have followed this tutorial to install MySQL but after successfully installed MySQL, I could not access database.

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Improve this question
1
  • 1
    I faced the same issue, after some digging, got it that u have to set root password your self following instructions at the StackOverFlow.
    – Mohammad.H Fathi
    May 7, 2018 at 10:51

2 Answers 2

Reset to default
81

I found a solution at here link, by following this solution I have solved my problem.

Short steps are:

sudo mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

Note: here test is a new password for the root user. Also, remember to run the command sudo service mysql restart after altering the user.

Improve this answer
6
  • 2
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'b230mehonot'' at line 1
    – alhelal
    Jun 5, 2018 at 3:40
  • same error here... :(
    – user1111929
    Jun 8, 2018 at 20:57
  • @alhelal, The solution is for MySQL not for MariaDB. So you can try another solution.
    – Ashrafuzzaman Sujan
    Jun 21, 2018 at 1:43
  • 2
    Do not write mysql -u root, Just write "sudo mysql", In Ubuntu systems running MySQL 5.7 (and later), the root user is authenticated by the auth_socket plugin by default.
    – Ravi Soni
    Jan 12, 2019 at 11:07
  • 1
    @ravisoni - THANK YOU. I was going nuts. And: What a reasonable default. I really should have thought to try that...
    – T.J. Crowder
    Jun 17, 2020 at 14:49
26

I found another way that is much better as we need not to give any password for local system.
It is as followed.

Open terminal and type 

sudo mysql -u root -p

It will prompt you in mysql, here you can fire any mysql commands.

Use mysql table for change table type, so we can use empty password. Bellow is command for it

USE mysql;

Now we change type of table by following command

UPDATE user SET plugin='mysql_native_password' WHERE User='root';

now we have to flush the privileges, because we have used UPDATE. If you use INSERT, UPDATE or DELETE on grant tables directly you need use FLUSH PRIVILEGES in order to reload the grant tables. 

FLUSH PRIVILEGES;

now exit from mysql by following command

exit;

now restart mysql server by following command

service mysql restart

Hope this may help

Thank you.

Improve this answer
3
  • 3
    This is the right answer
    – Ningappa
    Dec 15, 2018 at 10:01
  • How "It will prompt you in mysql", if the error is that it's not logging in at all?!!
    – Rodrigo
    Jul 21, 2021 at 15:58
  • in my case it always prompt in terminal you can also try
    – Krunal Pathak
    Feb 4, 2022 at 2:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .