MySQL error: #1045 - Access denied for user 'root'@'localhost' (using password: YES)

Question

I have tried every fix that I have found, and I cannot change my root MySQL password. At the shell, if I type in mysql -u root, I get

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

When I go to PHP MyAdmin, I get this:

#1045 - Access denied for user 'root'@'localhost' (using password: YES)

This all started when I was forced to enter a new password for MySQL. I have NO idea how to fix this and I am desperate.

Thank you.

Answer 1

I had this problem on a Windows computer a while ago I did manage to fix it by...

1. First open the MYSQL installer
2. Next to "MySQL Server" press reconfigure
3. Goto authentication method
4. Then change to legacy auth
5. And then press finish

Answer 2

The following worked for me: I'm running a Springboot application, I could create the table by command line mysql -u root -p, but once I ran the application, the table was not created (I didn't set a password to the database) so in the application.properties of the project you have to make sure that the line doesn't exist, or in case you want to use password that will match with the following line spring.datasource.password=pssw

Answer 3

I was facing the same problem #1045 Error even I logged in as root from my shell. Then I followed the below procedure and it worked for me-

Please note this method is only for Linux 20.04 users

Step 1: Type in your command shell sudo nano /etc/mysql/debian.cnf --->This will open mysql database config file And only see the file. No changes required. Locate these lines in the file as below

[client] 
host     = localhost 
user     = debian-sys-maint  --->Copy this user name
password = QiExXKUgZHc4Mnui  --->Copy this password 
socket   = /var/run/mysqld/mysqld.sock 
[mysql_upgrade] 
host     = localhost 
user     = debian-sys-maint 
password = QiExXKUgZHc4Mnui 
socket   = /var/run/mysqld/mysqld.sock

Step 2: Go back to your user prompt and type as follows-

`sudo -i` ----->This will change to user to root
`mysql -u debian-sys-maint -p` ----This will prompt for MySql editor

Paste the password as earlier you have copied then type

`FLUSH PRIVILEGES;` ----->This will grant all privileges to debian-sys-maint

Then change the 'root' user password by typing the following in mysql editor

`ALTER USER 'root'@'localhost' IDENTIFIED BY 'yournewpassword';`  --->this will change your password

Thats's it. Now your 'root' user and password is ready for login into the phpmyadmin.

Answer 4

I had this same problem recently. The newest version of PHPmyadmin does not allow you to login as root. I had additional problems because the username and password defined during setup on Ubuntu 18 by the setup script was not granted any privileges. I solved this by logging into mysql as root on the terminal. Then I added a new user from the command line. At that point I could log in as the new user in PHP my admin and the new user had the privileges I granted.

Answer 5

my problem was solved in below way.

When I was entered below command then I will get an error. sudo mysql -u root -p

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

To resolve this follow below steps. Enter command,

sudo mysqld_safe --skip-grant-tables &

Then the below error occurred.

2018-12-17T06:43:33.106111Z mysqld_safe Logging to syslog.
2018-12-17T06:43:33.108971Z mysqld_safe Logging to '/var/log/mysql/error.log'.
/usr/bin/mysqld_safe: 152: /usr/bin/mysqld_safe: cannot create /var/log/mysql/error.log: Permission denied
2018-12-17T06:43:33.111388Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
/usr/bin/mysqld_safe: 152: /usr/bin/mysqld_safe: cannot create /var/log/mysql/error.log: Permission denied

To resolve this error, then follow below lines.

1. systemctl stop mysql

2018-12-17T06:48:42.971692Z mysqld_safe Logging to syslog.
2018-12-17T06:48:42.974451Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2018-12-17T06:48:42.976653Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

2. sudo mkdir -p /var/run/mysqld

3. sudo chown mysql:mysql /var/run/mysqld

4. sudo mysqld_safe --skip-grant-tables &

2018-12-17T06:50:39.135292Z mysqld_safe Logging to syslog.
2018-12-17T06:50:39.137938Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2018-12-17T06:50:39.152966Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

5. Then connect mysql -u root. then MySQL prompt will appear. then after set your password.

6. FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

7. systemctl stop mysql

8. After that loing in below way. mysql -u root -p

Answer 6

For Windows users: Before starting to recover your password, try in command line:

mysql -uroot -pyourpassword 

as is, without space like shown below:

Print of my MySQL login

I have no time to explain why, but if you want to know more, please check how the 'MySQL X.X command line client' shortcut in your computer works.

In time, make sure that the mysql paths are in the PATH variable.

My computer is using these paths shown below. Your computer might be using other paths. C:\ProgramData\MySQL\MySQL Server 8.0 C:\Program Files\MySQL\MySQL Server 8.0\bin

Answer 7

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 shutdown 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 host name part, modify the instructions to use that host name.

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.

Answer 8

First Run CMD mode 
E:\xampp\mysql\bin>mysql.exe -u root -p

Screenshot: