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

Question

After installing MySQL for the first time, the command line client is not opening so i searched for solutions.

They said i must go to the bin directory and run it manually. and after I run below command and enter password.

mysql -u root -p

But it gives this error:

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

I tried some solution on stack overflow including disabling permissions, running manually which i mentioned above, starting the service from service.msc, running it with password and without it just doesn't want to work.

appreciate any help in advance.

Answer 1

For me changing a user password with below command cause this error.

ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
EXIT;

After run below command and only select flush privilege problem is solved.

sudo mariadb-secure-installation

Here is version info

$ mariadb -V
mariadb  Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

$ lsb_release -a 
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 12 (bookworm)
Release:    12
Codename:   bookworm

---

Thanks luuk for below comment and As stated in This answer there is no need to FLUSH PRIVILEGES; for ALTER statements.

I don't now this and blindly follow some random tutorial on Internet.

Answer 2

I just had the same problem and I fixed it by running 'mysql -u root -p -f'

It then prompted me for a password. Not sure if that is force or not, but it worked for me.

Answer 3

I got the answer myself. Seemingly, if you get this error, it means that you need to reset your password. You can learn how to do that in MySQL from this link.

And don't forget to change the 5.7 version with your currently installed version in using commands (mine was 8.0). After that, everything was working fine for me.

Answer 4

GENERIC MYSQL INFO

To start with, read the mysql manual: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

The steps will show you how to shut down the service and start it with an overriding command that doesn't require passwords, then you reset the password. From the manual:

Stop the MySQL server, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

Connect to the MySQL server using the mysql client; no password is necessary because the server was started with --skip-grant-tables:

shell> mysql

In the mysql client, tell the server to reload the grant tables so that account-management statements work:

mysql> FLUSH PRIVILEGES;

Then change the 'root'@'localhost' account password. Replace the password with the password that you want to use. To change the password for a root account with a different hostname part, modify the instructions to use that hostname.

MySQL 5.7.6 and later:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Or directly on the user table:

UPDATE mysql.user SET password=PASSWORD('mynewpassword') WHERE user='root';

XAMPP SPECIFIC

Stop the MySQL service. Open a command window. Change to the XAMPP MySQL directory:

> cd \xampp\mysql\bin\

Run the service without security (note you are running mysqld, not mysql):

> mysqld.exe --skip-grant-tables

The MySQL service will be running in this window, so open another command window and switch to the XAMPP MySQL directory:

> cd \xampp\mysql\bin\

Run the MySQL client:

> mysql

Update the password:

mysql> UPDATE mysql.user SET password=PASSWORD('mynewpassword') WHERE user='root';

Exit MySQL:

mysql> \q

Use task manager to cancel the mysqld.exe that is still running. Restart the mysql service.