There are times when you get stuck with a lost password of MySQL database. And yes it becomes difficult to recall one with the computerized brains of ours 🙂
In this post, I am going to show you how you can rest a lost MySQL password. Using this procedure you can reset the lost password of root or any other MySQL user. The best part is, it only resets the password, keeping your tables and data inside them quite safe.
Theoretically, we are going to login into MySQL by skipping the login prompt and then resetting the password simply by updating one of its internal table. After all, this where MySQL stores its part of storage
This trick would generally work for all type of systems. However, some syntax may vary, like starting and stopping MySQL daemons, the steps remain same.
Steps to reset MySQL Password for root user
- Login into your system, make sure you are logged into the system with the same user who has access to start and stop the MySQL server. If you have a user name dbuser who starts/stops MySQL daemon, then you should login with the same user. Since logging into the system as root and following the steps below may create files with root access level, which will be inaccessible to the actual dbuser
- Stop the MySQL process. You can search for the mysqld process and end it using the kill command in Unix system and taskkill in windows system. The path of this process depends upon the underlying OS, it could be any of the following – /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/ . However, you can also end the process with the following command as well –
sudo /etc/init.d/mysql stop
- Next, we will start the mysql daemon, but with an option called –skip-grant-tables , this lets you login into MySQL without having to ask for permissions. Since it allows anyone to login into MySQL without permission, so let’s disable remote login for it using –skip-networking . So use the following command to start MySQL daemon without network support and without having to ask for login.
sudo /usr/sbin/mysqld –skip-grant-tables –skip-networking &
- Now lets login using the following command – Yes it wont ask for the password 🙂
mysql -u root
- Run the update query to set the new password for your root user –
UPDATE user SET Password=PASSWORD(‘newrootpassword’) WHERE User=’root’;
- Now use the flush privileges command to,reload the grant tables into the memory and propagates the new change.
FLUSH PRIVILEGES;
It should reset your root password with the one you specified in step 5 and you should be able to login as root. But, please be sure to restart your server without –skip-grant-tables –skip-networking , otherwise users won’t be able to connect the MySQL server locally and anyone from the local machine would be able to login into MySQL.
So that should be all to reset your password.
Stay Digified !!
Sachin Khosla
