Reset your MySQL root password if you ever forget it by following the instructions provided here. Earlier I wrote an article to reset the MySQL root password in windows, this article does the same thing in Linux resetting the MySQL root user’s password if you forget it. The instructions outlined here are based on the Linux command-line. The procedure is same for both Windows and Linux, stopping the MySQL service, creating an file with the SQL query to reset the Linux password and executing it through the init-file option.
First create a file with a .sql extension inside the /tmp folder and type the following content
UPDATE mysql.user SET password=PASSWORD('new_password') WHERE user='root';
Replace new_password with your desired password. The file should be saved in /tmp because other directories may not have proper permissions and the file may not execute properly. But be warned keeping the file inside the /tmp folder makes it readable by all so delete it and change your root password to something else after the whole process is over.
Now stop the MySQL service, execute the command
service mysqld stop
If you get a message stating “unrecognized service mysqld” then most probably MySQL is not running as a service so use the kill command to terminate the process.
kill `cat /var/run/mysqld/mysqld.pid`
we’ll have to start the MySQL daemon in safe mode with the init-file (initialization file) option pointing to the SQL file.
mysqld_safe --init-file=/tmp/file.sql &
after you see the message starting MySQL daemon ……. press [enter] to return to the command line. Try connecting to the MySQL server with the new password
mysqld -u root -p
Enter the new password, if it says access denied its because the SQL init file couldn’t be read so the query to reset the password wasn’t executed. Look into the MySQL log file located in /var/log/mysqld.log and find any errors that may have occurred.
On the other hand if you can login with the new password change it immediately by executing the following queries
UPDATE mysql.user SET password=PASSWORD('secure_password') WHERE user='root';
Replace secure_password with a very strong password. Finally remove the SQL file
and restart MySQL service
service mysqld restart
Note: remove the SQL file you created to reset the MySQL root user’s password immediately after the password is reset.
Leave a Reply