How to reset MySQL root password

Question: We have forgotten the MySQL database root password. How can we reset it?

MySQL stores all its username and password data in a special database named mysql. In order to reset a MySQL root password, you need to have the root access to the server.

1. Login to server as root or superuser privilege and stop the MySQL.

# service mysqld stop

2. Start the MySQL and skip the grant table which stores the password.

# mysqld_safe --skip-grant-tables

3. Once the MySQL is up, you can able to login to MySQL without password. Reset the password.

# mysql --user=root mysql
mysql> update user set Password=PASSWORD('new-password') where user='root';
mysql> flush privileges;
mysql> exit

4. Kill all the MySQL processes and restart the MySQL service.

# kill -9 [mysql PID]
# service mysqld start