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
Note: A user without the MySQL root password, but with the ability to start and stop the service (like root on the operating system, or a user permitted to use sudo) can reset the MySQL root password.