MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Reset MySQL root Password

with 8 comments

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.

  1. 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';
  1. 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.

Written by maclochlainn

October 21st, 2010 at 12:09 am

Posted in MySQL,Windows7

Tagged with ,

8 Responses to 'Reset MySQL root Password'

Subscribe to comments with RSS or TrackBack to 'Reset MySQL root Password'.

  1. [...] Rest MySQL Root Password [...]

  2. [...] changing the root password when you don’t have it [...]

  3. [...] you should have your new MySQL root password! [...]

  4. Running mysql 5.5 on Win7. I’ve tried several failed methods to reset PW. I’m glad I found this before giving up. Total success. Thanks.

    lquid

    22 Mar 12 at 9:34 pm

  5. Its a nice quick fix bro.Thanks

    Arvind

    10 Jul 12 at 4:12 pm

  6. I am using windows vista, i have tried every thing in the world and all solution failed, i think there is a bug here.

    yasser

    3 Feb 13 at 12:28 pm

  7. It works for me and many others. Can you walk me through the steps you’re taking?

    maclochlainn

    3 Feb 13 at 12:33 pm

  8. this was the easiest way to fix the root password as well. thank you!

    juan mendez

    12 Mar 13 at 1:57 pm

Leave a Reply