CODEWITHSUNDEEP
mysql
Alan P.
Alan P.

Reputation: 3123

MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I've tried multiple solutions from StackOverflow but haven't had any success. I'm on Mac OSX (Sierra 10.12.3) trying to create a new database and user. From terminal I enter:

mysql -u root

which outputs this error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

To try and resolve it I stopped mysql from 'System Preferences', then from terminal typed:

sudo mysqld_safe —skip-grant-tables

I opened a second tab and entered:

mysql -u root

Then from in mysql:

update mysql.user set password_expired = 'N', authentication_string=PASSWORD('newpassword') where user = 'root';

flush privileges;

I then restart the computer (killing the process with CMD + C doesn't work). After restarting, trying mysql -u root still produces the same error.

I am able to access mysql via a MySQL client and a non-root user.

Any help is appreciated.

Upvotes: 43

Views: 235834

Answers (12)

Developer Vicky
Developer Vicky

Reputation: 1163

If you are using Mariadb+Ubuntu here is the solution:

you need to Open two terminals one by one:

  1. Open the first terminal and type

    sudo systemctl stop mariadb

    sudo mysqld_safe --skip-grant-tables &

  2. NOW YOU need to open a Duplicate Session in another terminal, and type:

    mysql

  3. Now type:

    FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY ''; exit;

  4. Now type in existing terminal:

    sudo mysqladmin shutdown && sudo systemctl start mariadb && exit

  5. In first Terminal Press (Ctrl+c) to stop.

  6. Now type mysql you get access.

Upvotes: 0

Vikas Reddy
Vikas Reddy

Reputation: 151

  1. update your password in "initialize database".

  2. System Preferences -> Click on MySql(Bottom Left or search) -> Click on Stop MySql Server -> Click on Initalize database and update the password.

  3. Once the password is updated click on "Start MySql Server"

  4. Open the terminal and run "mysql -u root -p" and provide the updated password.

Upvotes: 0

Pigpocket
Pigpocket

Reputation: 458

In my case, I needed to Edit Inbound Rules on my AWS RDS instance to accept All Traffic. The default TCP/IP constraint prevented me from creating a database from my local machine otherwise.

Upvotes: 0

mohammed_ayaz
mohammed_ayaz

Reputation: 679

Comment by @Niagaradad helped me. I was entering the wrong password the whole time.

Notice the error message

ERROR 1045 (28000): Access denied for user 'ayaz'@'localhost' (using password: YES)

It says, Password: Yes. That means I am sending the password to SQL and that is wrong.

Usually root account doesn't have password if you haven't set one. If you have installed mysql via homebrew then root account won't have a password.

Here is the comment.

so, not exactly the same then. NO means you are not sending a password to MySQL. YES means you are sending a password to MySQL but the incorrect one. Did you specifically set a password for the mysql root user when you installed MySQL? By default there is no password so you can use mysql -u root -p and hit enter.

Upvotes: 0

daewoo
daewoo

Reputation: 49

Try this (on Windows, i don't know how in others), if you have changed password a now don't work.

1) kill mysql 2) back up /mysql/data folder 3) go to folder /mysql/backup 4) copy files from /mysql/backup/mysql folder to /mysql/data/mysql (rewrite) 5) run mysql

In my XAMPP on Win7 it works.

Upvotes: 0

Ansel Faillace
Ansel Faillace

Reputation: 419

Just to confirm: You are sure you are running MySQL 5.7, and not MySQL 5.6 or earlier version. And the plugin column contains "mysql_native_password". (Before MySQL 5.7, the password hash was stored in a column named password. Starting in MySQL 5.7, the password column is removed, and the password has is stored in the authentication_string column.) And you've also verified the contents of authentication string matches the return from PASSWORD('mysecret'). Also, is there a reason we are using DML against the mysql.user table instead of using the SET PASSWORD FOR syntax? – spencer7593

So Basically Just make sure that the Plugin Column contains "mysql_native_password".

Not my work but I read comments and noticed that this was stated as the answer but was not posted as a possible answer yet.

Upvotes: 12

lofihelsinki
lofihelsinki

Reputation: 2571

If you need to skip the password prompt for some reason, you can input the password in the command (Dangerous)

mysql -u root --password=secret

Upvotes: 4

invalid entry
invalid entry

Reputation: 127

Is it possible the root password is not what you think it is? Have you checked the file /root/.mysql_secret for the password? That is the default location for the automated root password that is generated from starting from version 5.7. 

cat /root/.mysql_secret

Upvotes: 1

Pramod Patil
Pramod Patil

Reputation: 2763

For security reason mysql -u root wont work untill you pass -p in command so try with below way

 mysql -u root -p[Enter]
 //enter your localhost password

Upvotes: 49

gud3
gud3

Reputation: 565

mysql -u root -p;

And mysql will ask for the password

Upvotes: 18

b2ok
b2ok

Reputation: 536

You must run your mysql by xampp-controle.exe in folder XAMPP. After that login:

mysql -u root

Upvotes: 1

Alex Angelico
Alex Angelico

Reputation: 4065

just use:

$ sudo mysql

without the "-u root" parameter.

Upvotes: 6

Related Questions