Reset the Root Password on MySQL
August 11th, 2008 | Development
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.
- Stop the MySQL process:
sudo /etc/init.d/mysql stop - Start the MySQL process in safe mode:
sudo mysqld_safe --skip-grant-tables & - Connect to the MySQL server (no password required):
mysql -u root - Tell MySQL to use the mysql database:
USE mysql; - Run this UPDATE query:
UPDATE user SET password = PASSWORD("new_root_password") WHERE user='root'; - Flush the database Privileges:
flush privileges; - Exit MySQL:
quit - Stop the MySQL Server from running in safe mode:
sudo /etc/init.d/mysql stop - Restart MySQL in normal mode:
sudo /etc/init.d/mysql start - 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.
