Recover MySQL root password in five simple steps

Posted on 14th January 2015

The root user account in MySQL is a super user account that has all administrative and database privileges. It is created at the time of installation and is permitted to make connections from the localhost. The root user account is initially created with an empty password so you can connect to the server without entering a password. However this is insecure and you should assign a password to this account.

If you have set the password and then forgotten it at some point you can reset it easily by following the five simple steps below.

1. Stop MySQL server

On Windows

  • Login to your system as Administrator
  • Open Control Panel → Administrative Tools → Services
  • Select MySQL service and stop it.

On RHEL based Linux, run the command

sudo service mysqld stop

On Ubuntu and Debian based Linux, run the command

sudo /etc/init.d/mysql stop

2. Start MySQL server but skip the user privileges table

On Windows

mysqld.exe --skip-grant-tables

On Linux

sudo mysqld_safe --skip-grant-tables

3. Login to MySQL server

mysql -u root

You don't need a password here as we skipped the user privileges table while starting MySQL.

4. Reset the password

Change to mysql database

use mysql;

Set new password

update user set password=PASSWORD("mynewpassword") where user='root';

Flush privileges and quit

flush priveleges;

5. Restart MySQL server

On Windows

Stop mysql from command line

mysqladmin.exe shutdown

Then start the server from Control Panel → Administrative Tools → Services
Select MySQL service and click Start.

On RHEL based

sudo /etc/init.d/mysqld stop
sudo /etc/init.d/mysqld start

On Ubuntu and Debian based Linux

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

After the restart login using the new password to test.

mysql -u root -p

Post a comment


Nothing the first to share wisdom.