How to Reset the root Password of MySQL | Reset MySQL Password if you have lost the root Password


Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. Follow the steps to recover the password





Step 1:  Stop MySQL instance.

[root@serv01~]# service mysqld stop
Stopping MySQL:  [  OK  ]

Step 2:  Start MySQL in Safe mode with the mysqld_safe command and mention not to read the grant tables with all the MySQL database passwords.

[root@serv01~]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 6116
[root@serv01~]# Starting mysqld daemon with databases from /var/lib/mysql

Step 3:  Now MySQL is now running without password protection. You now have to use the  mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.

[root@serv01~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


Step 4:  You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to 123456.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET Password=PASSWORD("123456") WHERE User="root";
Query OK, 1 row affected (0.01 sec)
Rows matched: 2  Changed: 1  Warnings: 0


Step 5: Exit MySQL and restart the mysqld server daemon.

mysql> exit Bye
[root@serv01~]# service mysqld restart
STOPPING server from pid file /var/run/mysqld/
051224 17:24:56  mysqld ended

Stopping MySQL:  [  OK  ]
Starting MySQL:  [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables --skip-networking

The MySQL root user will now be able to manage MySQL using this new password.

Step 6: Use the following command to login to MySQL. The following method of login is not recommended, it just for example.

[root@serv01~]# mysql -u root -p123456 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

That’s all!

You might also like