You need to have command line access to use the below-mentioned steps to reset the passwordfor MySql root user. Usuallymysql daemon will be running in the background, but for resetting the password we need to kill the current instance of it, and restart it in safe mode.

pankaj@pankaj:-$ service mysql stop 
OR 
pankaj@pankaj:-$ /etc/init.d/mysql stop 
## start the mysql server in safe mode 
pankaj@pankaj:-$ mysqld_safe --skip-grant-tables &

“skip-grant-tables” option lets anyone start the server without using the privilege system at all. This gives unrestricted access to all databases, which otherwise is not possible if you don’t have the password. Once, you are successfully there, you can now run mysql and reset the new password in ‘user‘ table under ‘mysql‘ database for any particular user.

pankaj@pankaj:-$ mysql 
mysql> use mysql; 
mysql> update user set password=PASSWORD("new-password-here") where User='root'; 
mysql> flush privileges; 
mysql> quit

flush privileges updates the privileges from the grant tables, otherwise the older information from the in-memory cache will be used. Using this command, the cache is cleared and all privileges are reloaded. Once the password is updated, quit from the mysql command prompt, restart the process and try to login with the new password.

pankaj@pankaj:-$ service mysql stop 
pankaj@pankaj:-$ service mysql start

OR

pankaj@pankaj:-$ /etc/init.d/mysql stop 
pankaj@pankaj:-$ /etc/init.d/mysql start 
pankaj@pankaj:-$ mysql -u root -p Enter Password here

Resetting a deleted MySql root user:

What if your root user itself is deleted ? You need to create a new user and then grant all the required privileges to this users such as – ‘select_priv‘, ‘Update_priv‘ etc. using GRANT command. But, when you run mysql under safe mode with ‘–skip-grant-tables‘ option, you will be able to create a new user, but you wont be able to provide any privileges using GRANT command because of this very option supplied at command line.

However, there is another easy way to do this, simply provide all the required privileges at the time of creation of the MySql root user itself:

pankaj@pankaj:-$ service mysql stop 
pankaj@pankaj:-$ mysqld_safe --skip-grant-tables & 
pankaj@pankaj:-$ mysql

mysql> use mysql;

mysql> INSERT INTO `user`(`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) VALUES ('%', 'root', PASSWORD('new-password-here'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0');

Use the following command to verify the data for root user:

mysql> select * from user where User='root'\G

Host: localhost User: root Password: d795c890861edem65 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: mysql> quit pankaj@pankaj:-$ service mysql stop pankaj@pankaj:-$ service mysql start

That’s all you need to do if you get lost while trying to recover your MySql root user login or password.

PS: Sometimes, the ‘mysqld_safe’ process doesn’t stop by using ‘service’ command as specified above, in that case it is advisable to use ‘mysqladmin shutdown’ to stop mysqld_safe and then use ‘service mysql start’ to start the mysql process.