Access denied for user 'root'@'localhost' (using password: NO) - what's wrong?

Question

(Sorry for my English)

This is a log of my console:

anton@anton-X451CA:~$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
anton@anton-X451CA:~$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

What should I do? I understand that it appears when I try entering without a password, but I am dont confirmed password, and 'root'/empty string doesn't help.

Solution 1

When you installed this fresh, you usually execute

mysql_secure_installation

afterwards. There you also set the initial password for your root account (note, that this is not the root account you have in Linux).

When you installed version 5.7. or higher the initial password is not blank anymore. You can find it in the error log. Find this log by looking in your my.cnf file. Default directory is /var/log/mysql, I think.

Solution 2

I solved the problem, when:

Change the file my.cnf (in my Ubuntu-system he is placed at /etc/mysql/my.cnf). In the end i added that code:

[mysqld]
skip-grant-tables

That's helps enter in condole as anyone user without pass. Restart the server: sudo service mysql restart

Enter in the mysql-console:

mysql -u root

Change the password for user root:

UPDATE mysql.user SET authentication_string=PASSWORD('root'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';FLUSH PRIVILEGES;

exit the console:

\q

Remove (or use commentary) for that in my.cnf (we dont need that settings now):

#[mysqld]
#skip-grant-tables

restart mysql-server:

sudo service mysql restart

That's all. :)

Comments

[fancyPants]

You should specify the correct password :p Is this a fresh installation?

[Arun Vitto]

try the password as root

[Антон Игин]

@ArunVitto Already tried: anton@anton-X451CA:~$ mysql -h localhost -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[Антон Игин]

@fancyPants Yes, i am reinstalled client and server with that commands: sudo apt-get purge mysql-server mysql-client sudo apt-get install mysql-server mysql-client

[Антон Игин]

Ok, i am entered mysql_secure_installation and there is result: Securing the MySQL server deployment. Enter password for user root: Error: Access denied for user 'root'@'localhost' (using password: YES) I tried 'root' and empty string in field for password - the result is same.

[fancyPants]

Have you read the rest of my answer? The password can be found in the error log.

[Антон Игин]

i found my.cnf file in /etc/mysql/my.cnf, there is file's content: pastebin.com/CieqCgDW I dont see anything what can give me a hint.

[Антон Игин]

About error log (/var/log/mysql/error.log, as your say). I saw only this notes: pastebin.com/KcFbmq4V

[Антон Игин]

I am fixed that thing! :)... I am add [mysqld] skip-grant-tables in my.cnf, that's helps enter in console without password (mysql -u root). After that i change the password with command (UPDATE mysql.user SET authentication_string=PASSWORD('root'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';FLUSH PRIVILEGES;). That's all. Thank you for helping. :)

[mLstudent33]

UPDATE did not work but this did: alter user 'root'@'localhost' identified by 'PASSWORD'