Updating MySQL root password in Windows

Recently on one of my personal desktop running Windows Vista OS I installed latest MySQL (64 bit, 5.1.41-Community version). Installation was straight forward and every thing went well including default configuration for DSS (Decision Support System). While all was well, I did not revisit the system for few days and forgot the password. Here are the steps I took to fix with the new password.

NOTE: Perform the following as a system an "Administrator".

1. Stop the service ("MySQL") from the task manager.

2. Using cygwin window, get to "/cygdrive/c/Windows/system32" directory and run

/cygdrive/c/Windows/system32> sc queryex MySQL
#-- If it is running you will see something like this:
# SERVICE_NAME: MySQL
#        TYPE               : 10  WIN32_OWN_PROCESS
#        STATE              : 4  RUNNING
#                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
#        WIN32_EXIT_CODE    : 0  (0x0)
#        SERVICE_EXIT_CODE  : 0  (0x0)
#        CHECKPOINT         : 0x0
#        WAIT_HINT          : 0x0
#        PID                : 92940
#        FLAGS              :
#
#-- If mysql was stopped, then you will see
# SERVICE_NAME: MySQL
#        TYPE               : 10  WIN32_OWN_PROCESS
#        STATE              : 1  STOPPED
#        WIN32_EXIT_CODE    : 0  (0x0)
#        SERVICE_EXIT_CODE  : 0  (0x0)
#        CHECKPOINT         : 0x0
#        WAIT_HINT          : 0x0
#        PID                : 0

#-- Note: sc.exe: This tool provides a way to communicate with Service Controller
#-- (Services.exe) from the command prompt, to retrieve information about services. 

3. Run

/cygdrive/c/Program Files/MySQL/MySQL Server 5.1/bin> ./mysqld.exe --skip-grant-tables &
#-- Output will be something like:  [1] 5678 

/cygdrive/c/Program Files/MySQL/MySQL Server 5.1/bin> ./mysql --user=root mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>      #-- Now you are connected to mysql server with access control disabled.
mysql> update user set Password=PASSWORD('YOUR-NEW_PASSWORD-HERE') WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;   #-- This reloads the privileges from the grant tables in the mysql database.
Query OK, 0 rows affected (0.01 sec)

mysql> select version();   #-- A quick test...
mysql> exit;               #-- Exit from mysql

/cygdrive/c/Program Files/MySQL/MySQL Server 5.1/bin> fg    #-- Now back at the cygwin prompt bring the background process and kill it. 
#-- Or kill it (mysqld) from task manager under "Processes tab".

Note: The msyql.com documentation (http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-windows) has similar technique, it seem to be not updated with the latest. For example, there is no "mysqld-nt" executable under bin directory! You don't need to create a text file as well to do the steps shown here.

4. Now start the MySQL service from task manager "Services tab".

5. Login to MySQL using the new password from CLI or Toad or any client you want to connect to database.

Other commands that might be of helpful are:

/cygdrive/c/Program Files/MySQL/MySQL Server 5.1/bin> ./mysql_upgrade.exe -u root -p
Enter password: ********
#- It checks all tables in all databases for incompatibilities with the current version of MySQL Server after any upgrades done to MySQL.

/cygdrive/c/Program Files/MySQL/MySQL Server 5.1/bin> ./mysql_client_test.exe --help

Cheers,
Shiva