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, Mar 25 '17 at 07: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, Mar 25 '17 at 07: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, Mar 25 '17 at 07: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, Mar 25 '17 at 07:43
Can you check whether there really is a directory called `C:\Program Files\MySQL\MySQL Server 5.7\data`? — Manngo, Mar 25 '17 at 07:48
Please don't re-add the sql-server tag: your question is not about Microsoft SQL Server. Same goes for the sql tag: your question is not about the SQL query language, but purely about administering MySQL. — Mark Rotteveel, Mar 25 '17 at 08:02
Possible duplicate of [Resetting ROOT password in MySQL 5.6](http://stackoverflow.com/questions/21651898/resetting-root-password-in-mysql-5-6) — Douglas Hosea, Mar 25 '17 at 08:12
Hey Mark, Thanks man for clarifying.. My understanding is a good SQLdeveloper or a SQL techie might have all the knowledge on SQL server/SQL database and on MySQL as well.My sincere request would be, If this question is bothering you please ignore it. — little_code, Mar 25 '17 at 08:31

score 7 · Answer 1 · edited Jul 17 '22 at 15:48

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

score 0 · Answer 2 · edited Aug 11 '18 at 21:40

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

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. — little_code, Mar 25 '17 at 08:16
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. — Douglas Hosea, Mar 25 '17 at 08:41
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. — little_code, Mar 30 '17 at 07:43
In MySQL 5.7 there is no Password field, instead it's called authentication_string — Ynhockey, Feb 27 '20 at 15:14

How to change 'root' password in MySQL5.7

2 Answers2