Reset MySQL root Password
Sometimes the MySQL installation goes great but students forget their root
password. It’s almost like the DBA who has the only copy of the root
user’s password getting hit by a bus. How do you recover it? It’s not terribly difficult when deployed on the Windows OS (you’ll find a nice article on Linux here). This page takes you to standard documentation for resetting permissions.
There are two ways to do it. The first is quick and easy but risks letting others into the database through the network. The second requires a bit more work but ensures that network is shut while you disable security to reset the root
password.
- The quick and easy way to disable security and reset the
root
password.
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'; |
- The secure way to disable security and reset the
root
password.
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'; |
After you’ve updated the password, remove the previous statement lines from the my.ini
file. Then, reboot the server.
Hope this helps a few people.