1. Home
  2. Databases
  3. How to reset MySQL root password ?

How to reset MySQL root password ?

What happens if you forget your MySQL root password? No worries, the below steps will help you to reset mysql root password using options ‘–init-file’ and ‘–skip-grant-tables’.

Option 1: Reset MySQL root password using –init-file option

Step 1: Kill mysqld process

#killall mysqld

Step 2: Create a new mysql-init  (I’ll call it as pass-reset.txt) file.

# vim /var/lib/mysql/pass-reset.txt

Include the below line: (Remember to change the default password – your_new_pass)

Reset mysql password

If you are using MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_pass';

If you are using MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_pass');

Save the file and quit.

Step 3: Start mysqld_safe process with the file created in Step 2 as an input.

# mysqld_safe --init-file=/var/lib/mysql/pass-reset.txt
 Starting mysqld daemon with databases from /var/lib/mysql
Note: If the above command fails with the below error message:
# Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
160524 16:42:16 mysqld ended
[1]+ Done mysqld_safe --init-file=pass-reset.txt
Sol : Look out for the detailed error message in /var/log/mysqld:
[ERROR] /usr/libexec/mysqld: File '/root/pass-reset.txt' not found (Errcode: 13)
Lookout for the file permission, as MySQL should have enough privileges to read the file.

Couldn’t find mysqld_safe executable?

Well, you do the same with mysqld executable as shown below:

# mysqld --init-file=/var/lib/mysql/pass-reset.txt

Step 4: Jump to a different terminal and try accessing MySQL root user with the new password

# mysql -u root -p

Step 5: If you are able to login successfully with the new password, then you can kill the mysqld_safe process started in step 3.

# killall mysqld

Step 6: Start mysqld normally

# /etc/init.d/mysql start

Step 7: Login to MySQL root account and check if everything works as expected.

Step 8: This step is very important. Remember to remove the password file created in step 2:

# rm /var/lib/mysql/pass-reset.txt

Option 2: Recover/Reset MySQL root password using –skip-grant-tables option

Step 1: Stop all mysql process

$killall mysqld

Step 2: Start mysqld_safe daemon with –skip-grant-tables option. Doing so will not prompt for password.

$mysqld_safe --skip-grant-tables

Step 3: Connect to MySQL as root user.

$mysql -u root

Step 4: Reset the root account password.

mysql> use mysql
mysql> update user set password=PASSWORD("new_password_here") where User='root';

Step 5: Quit mysql and connect again using the new password.

$mysql -u root -p

That’s it!

Updated on April 3, 2018

Was this article helpful?

Related Articles

Leave a Comment