MacLochlainns Weblog

You add the following parameter to the my.ini configuration file in the [mysqld] block. While you’re editing the configuration file, you should also enter the other two. You’ll uncomment them in subsequent steps because they’re necessary to connect via a localhost OS pipe when you suppress the listener.

[mysqld]
 
# These let you safely reset the lost root password.
skip-grant-tables
#enable-named-pipe
#skip-networking

After you’ve saved these changes in the my.ini file, you should stop and restart the mysql51 service. If you named the Microsoft service something else, you should substitute it for mysql51 in the sample statements. The command-line steps are:

To stop the service:

net stop mysql51

To start the service:

net start mysql51

Now you can sign on as the root (superuser) without a password and change the password. However, you can’t do it through the normal command:

SET PASSWORD FOR 'student'@'%' = password('cangetin');

If you attempt that normal syntax, MySQL raises the following exception:

ERROR 1290 (HY000): The MySQL server IS running WITH the --skip-grant-tables option so it cannot execute this statement

You need to first connect to the mysql database, which holds the data dictionary or catalog. Then, you use a simple UPDATE statement to reset the root password.

-- Connect to the data dictionary.
USE mysql
 
-- Manually update the data dictionary entry.
UPDATE USER
SET    password = password('cangetin')
WHERE  USER = 'root'
AND    host = 'localhost';

Remove the comment marks before the enable-named-pipe and skip-networking, if you added all three parameters while testing the easy way. Otherwise you should add the following three parameters to the my.ini configuration file in the [mysqld] block. The enable-named-pipe opens an OS pipe through which you can connect to the database. The skip-networking instructs the database not to start the MySQL listener.

[mysqld]
 
# These let you safely reset the lost root password.
skip-grant-tables
enable-named-pipe
skip-networking

After you’ve saved these changes in the my.ini file, you should stop and restart the mysql51 service. The command-line steps are:

To stop the service:

net stop mysql51

To start the service:

net start mysql51

You still can’t reset a password with the SET PASSWORD FOR 'user'@'host' syntax when you’ve disabled reading the database instance’s metadata. The syntax to connect to the database through the OS pipe as the unauthenticated root user is:

mysql -h . -uroot

Unfortunately, once you’ve connected, you can’t reset the password through the normal command because that’s disabled by the skip-grant-tables parameter. Check the example in the quick and easy way above.

With the data dictionary validation disabled, you need to first connect to the mysql database to make this change. The mysql database holds the data dictionary or catalog. You use a simple UPDATE statement to reset the root password once connected to the mysql database.

-- Connect to the data dictionary.
USE mysql
 
-- Manually update the data dictionary entry.
UPDATE USER
SET    password = password('cangetin')
WHERE  USER = 'root'
AND    host = 'localhost';