Reset MySQL root password

Preface

In some circumstances you need to reset MySQL root without knowing current password of it. For example you forget the password. You can change the password of the root user easily. The following guide is tested on Ubuntu 12.04 based on MySQL 5.5.

Reset root password (without knowing the current password)

To do so, please follow the instructions below:

Step 1: Stop the MySQL Service

At first, you need to stop the MySQL service:

$ sudo /etc/init.d/mysql stop

Step 2: Start the MySQL daemon in safe mode with skipping grants

Please run the MySQL in safe mode with skipping grants parameter:

$ sudo mysqld_safe --skip-grant-tables &

Step 3: run the MySQL prompt as root user without password

Now, you can enter the MySQL command line as root user without password:

$ mysql -u root

Step 4: Update the password of root user

Run the following commands to update root user password. Please replace “NEW-ROOT-PASSWORD” with the desired password:

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step 5: Restart the MySQL service

You should restart the MySQL service to check the new password:

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

Step 6: Login with the new password

Now, login to the MySQL prompt with the new password:

$ mysql -u root -p