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)

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

If you are unable to start mysqld as root user and get the below error, then here’s the fix.

# mysqld --init-file=/var/lib/mysql/pass-reset.txt
[Server] /usr/sbin/mysqld starting as process 7430
[Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
[Server] Aborting
[Server] /usr/sbin/mysqld: Shutdown complete MySQL Community Server - GPL.

To fix the above error, you need to specify the user under which mysqld should be started.

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

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!