How to change 'root' password in MySQL5.7

Question

I have forgotten my "Root" password of "MySQL" on my Windows machine. I tried using this link, But getting the error message(Pic attached). Can anyone help me out from this?

Steps Followed:-

1:- Stopped "MySQL process" in my computer service section.

2:- Created a .txt file having data ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

3:- Renamed the file to mysql-init.txt & saved into C drive.

4:- Opened command prompt & used the command C:> cd C:\Program Files\MySQL\MySQL Server 5.7\bin

5:- Then used C:> mysqld --init-file=C:\mysql-init.txt

After then I am getting this error message in my console.

I see that you’re using v 5.7. There appears to be a difference in procedure between 5.7.5 and 5.7.6. Could you outline the steps (edit your question) that you followed? — Manngo
– Manngo, Commented Mar 25, 2017 at 7:33
Hi Mango, Thanks for quick revert. I don't want to keep any data that I had created before. Simply when I am trying to create a new database I am facing this issue. — little_code
– little_code, Commented Mar 25, 2017 at 7:34
Without looking at your system, I can’t say what’s actually gone wrong, but the first error message suggests that something is missing. If worse comes to worse, you can uninstall and start again. However, I strongly recommend that you also install MySQL WorkBench, which is a free GUI from Oracle. — Manngo
– Manngo, Commented Mar 25, 2017 at 7:37
Hey Manngo, Now the followed steps are edited in my question section. Already I have tried with the Uninstall-Install process. But seems it is not working out. — little_code
– little_code, Commented Mar 25, 2017 at 7:43

Mark Rotteveel · Accepted Answer · 2022-07-17 15:48:56Z

8

Here a little bit twist with MySQL Community Server 5.7 I share some steps, how to reset MySQL 5.7 root password or set password. It will work CentOS 7 and RHEL7 as well.

Stop your databases

service mysqld stop

Modify /etc/my.cnf file add skip-grant-tables

vi /etc/my.cnf
[mysqld] 
skip-grant-tables

Start MySQL

service mysqld start

Select MySQL default database

mysql -u root
mysql> use mysql;

Set a new password

mysql> update user set authentication_string=PASSWORD("yourpassword") where User='root';

Restart MySQL database

service mysqld restart
mysql -u root -p

edited Jul 17, 2022 at 15:48

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

answered Oct 5, 2017 at 15:07

Narendra Kumar

3293 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

halfer · Accepted Answer · 2018-08-11 21:40:10Z

1

For Linux This process is if you have root access on your server

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

mysqld_safe --skip-grant-tables

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

mysql --user=root mysql

update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;

Now kill your running mysqld, then restart it normally. Let me know if you have a problem

For windows

Stop your MySQL server completely. This can be done from Wamp(if you use it), or start “services.msc” using Run window, and stop the service there.
Open your MS-DOS command prompt using “cmd” inside the Run window. Then go to your MySQL bin folder, such as C:\MySQL\bin. Path is different if you use Wamp.
Execute the following command in the command prompt:
```
mysqld.exe -u root --skip-grant-tables
```
Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.
Go to your MySQL bin folder again.
Enter “mysql” and press enter.
You should now have the MySQL command prompt working. Type “use mysql;” so that we switch to the “mysql” database.
Execute the following command to update the password:
```
UPDATE user SET Password = PASSWORD('your_new_passowrd') WHERE User ='root';
```
However, you can now run almost any SQL command that you wish.

After you are finished close the first command prompt, and type “exit;” in the second command prompt.

You can now start the MySQL service. That’s it.

Take a look at this answer for more explanation from the discussions

edited Aug 11, 2018 at 21:40

halfer

20.2k20 gold badges111 silver badges208 bronze badges

answered Mar 25, 2017 at 7:32

Douglas Hosea

1,02214 silver badges30 bronze badges

6 Comments

Douglas Hosea Over a year ago

How is it not working out, have you looked at my edit

little_code Over a year ago

hey, I have not seen your edits.Apologize for this.After step-2 when saving the configuration file ** my.ini** it is showing 1:- my-ini already exists. Do you want to replace it. When I am clicking on Yes. The dialogue-box is showing access denied.

Douglas Hosea Over a year ago

I have updated my answer so that you do not have to deal with files but only with command prompt. Hope i have helped you out.

little_code Over a year ago

Hey Dougulas, Thanks for your great effort for helping.Unfortunately, my system was having only ready only access to change the root password. So I followed the following steps. Uninstalled the complete MySQL package. Then manually deleted Program files--> MySQL + Programme data__> MySQL (+), C:\Users\User\AppData\Roaming\MySQl. Then Installed again. Now it is completly running fine. Thanks for your help again.

Douglas Hosea Over a year ago

Thats great @Jyotiranjan, glad it worked out for you.

|

Collectives™ on Stack Overflow

How to change 'root' password in MySQL5.7

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related