While installing MySQL, it asks to set a root password. If you have not done so, you can connect to MySQL just by running the command:
mysql
However, this is not a good security practice as anyone can access the database directly using this command.
If you have set a root password for your database but lost track of it, read the following steps to understand how to reset MySQL root password.
Please note that you must run the commands as the root user. So either log in directly as the root user or use the su or sudo commands to run the commands as the root user.
1) Login to your MySQL server.
2) Stop the MySQL server using the appropriate command for your Linux distribution:
- For CentOS and Fedora, type:
service mysqld stop
- For Debian and Ubuntu, type:
service mysql stop
3) Restart the MySQL server with the –skip-grant-tables option. To do this, type the following command:
mysqld_safe --skip-grant-tables &
Note the & symbol at the end of the above command. It’s to run the command in the background.
Remember, running MySQL with the –skip-grant-tables option is not to be performed unless needed as it’s highly insecure. Here we run the command for a short period of time while we reset the password.
4) Now login to MySQL by using the mysql command without any options
mysql
5) Reset the root password by executing the following queries at the mysql prompt. Just replace “password” with your preferred root password.
UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
exit;
6) Stop the MySQL server using the following command. You will need to enter the new root password to stop the MySQL server.
mysqladmin -u root -p shutdown
7) Now start the MySQL server normally.
- For CentOS and Fedora, type:
service mysqld start
- For Debian and Ubuntu, type:
service mysql start
You should be able to login to MySQL by using the new root password.
