11

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.

Pawel Veselov
  • 3,996
  • 7
  • 44
  • 62
SandyS
  • 117
  • 1
  • 1
  • 6

8 Answers8

11

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.

Steve Johnson
  • 182
  • 6
  • I tried to change the root mysql password like you suggested but do not work. When I run the command to change the password, I got this error: `ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement` – Diego Somar May 22 '17 at 11:09
  • If you are getting error 1290, it is usually because you didn't do the FLUSH PRIVILEGES command. This reactivates account management. If you have problems with non-standard versions of MySQL (like MariaDB giving Error 1131), you can update the user table directly instead of using the account management statements: `UPDATE mysql.user set password=PASSWORD('mynewpassword') WHERE user='root';` – Steve Johnson May 23 '17 at 12:56
  • I'm using 'generic' MySQL 5.7. This did not work, but the solution looks promising. I will pursue this further in a new post. – Eric Hepperle - CodeSlayer2010 May 25 '17 at 14:03
10

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.

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

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

  3. 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

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

  2. FLUSH PRIVILEGES;

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

  1. systemctl stop mysql

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

Lamanus
  • 12,898
  • 4
  • 21
  • 47
netajik
  • 196
  • 1
  • 4
  • 15
4

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.

BLOGBIR
  • 67
  • 9
2
First Run CMD mode 
E:\xampp\mysql\bin>mysql.exe -u root -p

Screenshot:

enter image description here

Ram Pukar
  • 1,583
  • 15
  • 17
  • Tried this, too. I ended up installing a new version of XAMPP and that did it. I lost my databases, but that did it. – SandyS Jan 25 '17 at 15:38
  • Since there are so many flavors and versions of MySQL, it is helpful when asking for help if you identify the flavor and version you are working with. If it doesn't work, an error message is helpful for getting additional help. I've added an answer specific to XAMPP. – Steve Johnson May 23 '17 at 13:05
1

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.

tshirtdr1
  • 69
  • 1
  • 3
0

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

Alex Myers
  • 6,196
  • 7
  • 23
  • 39
Renato Pierri
  • 1
  • 1
0

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

Karoli
  • 11
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 19 '22 at 08:07
0

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
Angus
  • 70
  • 6