Reset the Root Password on MySQL

From time to time, you may run across a MySQL database where you don’t have the root password, but you require it to perform some task or another. In my case, I inherited a MySQL installation but no root password was available. The root password in the records did not work. Here’s a quick lesson in resetting the root password.

NOTE: This only works of you have command line access to your MySQL installation. This is a multi-step process. All steps must be completed to properly re-secure your database.

  1. Stop the MySQL process: sudo /etc/init.d/mysql stop
  2. Start the MySQL process in safe mode: sudo mysqld_safe --skip-grant-tables &
  3. Connect to the MySQL server (no password required): mysql -u root
  4. Tell MySQL to use the mysql database: USE mysql;
  5. Run this UPDATE query: UPDATE user SET password = PASSWORD("new_root_password") WHERE user='root';
  6. Flush the database Privileges: flush privileges;
  7. Exit MySQL: quit
  8. Stop the MySQL Server from running in safe mode: sudo /etc/init.d/mysql stop
  9. Restart MySQL in normal mode: sudo /etc/init.d/mysql start
  10. Try to log in with the new root password: mysql -u root -p

If the goal of the exercise is simply to dump the data, you can stop after completing step 3 and run your favorite mysqldump command. Then you can import it into a new MySQL instance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s