Resetting the root password for MySQL:
If you are using a separate server and in the event of forgotten the password of MySQL, you need to reset the password. This tutorial describes resetting the root password of MySQL in your server. To reset the password, you should have root access to the server.
Follow the below steps to reset the password:
Step 1: Log in to the server using SSH access. You must log in as the root user to run the commands. You should either log in directly as the root user or use the sudo commands to run the following commands as the root user.
Step 2: Stop the MySQL server with the use of following commands for the Linux distribution.
If you are using CentOS and Fedora, type the following command as root user.
service mysqld stop
If you are using Debian and Ubuntu, use the below command to stop the MySQL server.
service mysql stop
Step 3: After completing the above step, restart your MySQL server with the “–skip-grant-tables” option. To do that enter the following command given below.
mysqld_safe –skip-grant-tables &
Note: Ensure that you have entered & at the end of the command and it will run the command in the background. Running the skip-grant-tables option in MySQL is highly insecure and must be done in the case of resetting the password only. Follow the below steps to start the MySQL server securely.
Step 4: Use the following command to log in to the MySQL.
mysql
Step 5: Reset the password at the mysql> prompt. To reset the password type the following command.
UPDATE mysql.user SET Password=PASSWORD(‘New-password’) WHERE User=’root’ ;
Step 6: After that, enter the below command in mysql prompt.
FLUSH PRIVILEGES;
exit;
Step 7: Now you need to shut down the MySQL server with the following command. Also, you will be prompted to enter the new password before the MySQL server shuts down.
mysqladmin -u root -p shutdown
Step 8: Now start the MySQL server as usually with the following command.
If you are using CentOS and Fedora, enter the below command.
service mysqld start
If you are using Debian and Ubuntu, enter the below command.
service mysql start
Now the MySQL password is updated successfully. We hope that this article will help you to reset the MySQL server password using the commands. For more information, like and follow us on Facebook and Twitter. For video tutorials, subscribe to our YouTube channel “ServerCake India“.