I have been following these instructions for resetting root password for local installation of MySQL 5.6
on Windows 7 laptop.
I stopped the service, created init-file, and ran the following command (as Administrator):
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --init-file=C:\\MySQL-misc\\mysql-init.txt
I got the following warning:
2014-02-08 15:44:10 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
Since it's a warning I'm not sure whether I need to fix anything and then redo the process again.
Currently the command window is still on and does not accept any input. Should I force-close it or is there anything I can do to complete the process gracefully?
UPDATE
I killed the Command window and tried to restart the service. Got an error.
Restarted Windows and the service automatically started. The new root password
seems to work. I was successfully able to use various functions of Workbench that require the password.
So, the warning was indeed just a warning.
Start the server manually using this line:
In new cmd (Run as administrator) execute :
Execute the following query in mysql client:
That's it!!
Updating this answer regarding to changes at MySQL 5.7:
0) shut down service
mysql57
1) go to
C:\ProgramData\MySQL\MySQL Server 5.7
, note thatProgramData
is a hidden folder2) looking for file
my.ini
, open it and add one lineskip-grant-tables
below[mysqld]
,save3) start service
mysql57
4) by right, you can access the database, run mysql
5) and use the query below to update the password
update mysql.user set authentication_string=password('NEW_PASSWORD') where user='root';
6) shut down the service again, remove the line
skip-grant-tables
save it, and start the service again. try to use the password you set to login.In case if you have Xampp installed.
skip-grant-tables
under[mysqld]
C:\xampp\mysql\bin\mysql
update mysql.user set password=PASSWORD('root') where user='root';
On Windows:
0) shut down service
mysql56
1) go to
C:\ProgramData\MySQL\MySQL Server 5.6
, note thatProgramData
is a hidden folder2) looking for file
my.ini
, open it and add one lineskip-grant-tables
below[mysqld]
,save3) start service
mysql56
4) by right, you can access the database, run
mysql
5) and use the query below to update the password
note: for newer version, use
authentication_string
instead ofpassword
6) shut down the service again, remove the line
skip-grant-tables
save it, and start the service again. try to use the password you set to login.On Mac:
0) stop the service
1) skip grant table
once it's running, don't close it, and open a new terminal window
2) go into mysql terminal
3) update the password
for newer version like 5.7, use
4) run
FLUSH PRIVILEGES;
5) run
\q
to quit6) start the mysql server
The issue has been resolved.
As stated in my question I followed instructions from MySQL manual.
The process did not go exactly as described (and this was the reason for my original post) but it worked nevertheless (see UPDATE section in my post).
First stop mysql server and follow below steps:
Go to mysql bin directory on cmd i,e. cd C:\ProgramData\MySQL\MySQL Server 5.6\bin
skip grant tables will allow you enter into mysql
Open new command prompt or on same command prompt
run below query to change mysql root password
Thats it, Restart mysql and good to go with new password..!!