Forgetting MySQL root user password is easy. Really it is! When you setup an application like website etc., you put it into configuration and everything is good. After some months when you want to troubleshoot MySQL database, there you go. Blank! At first place, you should not use root MySQL root user for any application and site database.
So, let’s see how you can reset a forgotten, lost, unknown SQL root password using Secure Shell (SSH) access. You can use same method for local computer.
Connect to your server using PuTTY or any other SSH client. Make sure you are log in as server root or admin user.
For local Linux or Mac machine, you can connect as root with command,
ssh root@//IPADDRESS-OR-HOSTNAME
You may need to accept the RSA fingerprint as Yes. Then the first thing is to stop the running MySQL server,
service mysqld stop
Now, start the MySQL daemon in background by skipping the regular authentication,
/usr/bin/mysqld_safe --skip-grant-tables &
Start the MySQL daemon and get yourself to sql prompt,
mysql
You will notice that command prompt is changed and now you can pass the SQL commands. So, go ahead and initiate user rights and permissions database,
use mysql;
Remember that you need to put ‘;’ at every command or prompt will return an error. Copy-paste below command to change the root user password,
update user set password=password('NEWPASS') where user='root' and host='Localhost';
Change the NEWPASS with your new password. Usually, localhost works fine but you may need to change localhost with your server IP or hostname. It should be noted here that you can change any user password like this.
Now, flush the already loaded privileges from the system by,
flush privileges;
Then quit,
quit
We should stop this permission disabled daemon of MySQL,
service mysqld stop
And start it in regular mode with,
service mysqld start
You may leave the shell now and carry on your work with newly changed password.
Caution: Remember that as you have changed password for MySQL database user the applications, sites and frameworks using this user will break instantly. You will also need to change the configuration files for website (e.g. WordPress etc.)