Resetting ROOT password in MySQL 5.6

Question

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.

Answer 1

On Windows:

0) shut down service mysql56

1) go to C:\ProgramData\MySQL\MySQL Server 5.6, note that ProgramData is a hidden folder

2) looking for file my.ini, open it and add one line skip-grant-tables below [mysqld],save

[mysqld]

skip-grant-tables

3) start service mysql56

4) by right, you can access the database, run mysql

5) and use the query below to update the password

update mysql.user set password=PASSWORD('NEW PASSWORD') where user='root';

note: for newer version, use authentication_string instead of password

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

sudo /usr/local/mysql/support-files/mysql.server stop

1) skip grant table

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

once it's running, don't close it, and open a new terminal window

2) go into mysql terminal

/usr/local/mysql/bin/mysql -u root

3) update the password

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

for newer version like 5.7, use

UPDATE mysql.user SET authentication_string=PASSWORD('password') WHERE User='root';

4) run FLUSH PRIVILEGES;

5) run \q to quit

6) start the mysql server

sudo /usr/local/mysql/support-files/mysql.server start