We’re humans, and we forget stuff. when we do, we tend to go back in time to remember what was forgotten. The same is true with passwords. We do forgot a lot when it comes to passwords. and that’s why there are countless password managers to help us manage our passwords.
Forgetting your desktop password is one thing. however, fogetting the root password to your MySQL database in production is totally another thing and not fun. and if you did, then you’re in the right place. the steps below will show you how to quickly reset the root password to your MySQL database server.
This brief tutorial is going to show students and new users how to quickly regain access to MySQL databases by resetting the root password.
When you’re ready to reset the root password for MysQL, continue with the steps below
Setting MySQL Root Password
To reset MySQL root password, logon to the Ubuntu server and run the commands below to stop MySQL database service
sudo /etc/init.d/mysql stop
Then run the commands below to create a new mysqld directory
sudo mkdir /var/run/mysqld/
and give mysql user access to it.
sudo chown mysql /var/run/mysqld/
After that, run the commands below to start MySQL in safe mode by bypassing the standard authentication process.
sudo mysqld_safe --skip-grant-tables &
You should see something like this. you may have to press the Enter key.
richard@ubuntu1710:~$ 2017-12-25T16:49:30.551520Z mysqld_safe Logging to syslog.
2017-12-25T16:49:30.554646Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-12-25T16:49:30.578079Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-12-25T16:49:32.568746Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[1]+ Done sudo mysqld_safe --skip-grant-tables
Next, run the commands below to logon to the database server with the root account without typing a password. you’re given root access to the database without entering the password.
sudo mysql -u root
Once logged into the database, run the SQL command statement below to use the default mysql database. this database holds the settings for root user and other server settings.
use mysql;
The output should look like the one below:
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Finally, run the SQL statement below to change the root password
update user set authentication_string=PASSWORD("New_Passwore_Here") where User='root';
Save the change by running the commands below
flush privileges; exit;
Finally, stop MySQL safe_mode and start MySQL default service
sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start
If you did everything as described above, you should be able to log back onto MySQL database using the root new password.
sudo mysql -u root -p
Enjoy!
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.20-0ubuntu0.17.10.1 (Ubuntu) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
This is how one resets MySQL root user.
You may also like the post below:
When I run
sudo /etc/init.d/mysql stop
The service never stop
How can I fix this?
sudo systemctl stop mysql.service
wow..great and so helpful.. i struggled a lot to reset my password. This helped me a lot .. thanks 🙂
Everything is working but when I would like start mysql with
sudo /etc/init.d/mysql start
It does not start and run into an failure
I’m having the same trouble
I get the same issue at this stage:
mysql.serviceJob for mysql.service failed because the control process exited with error code.
See “systemctl status mysql.service” and “journalctl -xe” for details.
failed!
But the password change has worked.
thanks that’s work
Its work, thanks so much,
Best regards.
Big thanks!
Thanks,
It worked perfect…
Thank you!
Thank you much! This helped me a lot!
thanks a lot
Perfect…
Thanksss
After doing this I get the following error:
“ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)”
What should I do now? Folder /var/run/mysqld/ is still exist, but the mysqld.sock file is missing
Thanks for this very helpful page.
One minor point – at the end when one logs back in with the new root password, this command is shown:
sudo mysql -u root -p
However the “sudo” is not needed as the computer and mysql accounts are different. (The command works with or without the sudo.)
@harry Schaffer
Although you are correct that the mysql ‘root’ user is not the same as the system ‘root’ user, actually sudo may be required to start the mysql program, depending who you are logged in as and what your Linux privileges are!
Thank you very much, it worked like magic.
Thanks! great tutorial
When I do
sudo mysqld_safe --skip-grant-tables &I get:2019-06-12T19:56:12.883771Z mysqld_safe Logging to syslog.
2019-06-12T19:56:12.887980Z mysqld_safe Logging to ‘/var/log/mysql/error.log’.
2019-06-12T19:56:12.921711Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
It never finishes… Is there anything I can do to remedy this?
mine says this:
[….] Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code.
See “systemctl status mysql.service” and “journalctl -xe” for details.
failed!
Worked. But I had faced cannot open socket issue also after the above steps. So had to correct by PC restart and following one additional step mentioned in https://stackoverflow.com/questions/37879448/mysql-fails-on-mysql-error-1524-hy000-plugin-auth-socket-is-not-loaded
Thank you so much. There are so many solutions but no one talks about creating the sqld directory! I have spent hours at multiple times trying to get this done.
Now I have bookmarked the page and will also try to remember passwords! 😀
Thanks a lot, it worked like charm.
To everyone who is facing problems with the sudo /etc/init.d/mysql start command, use sudo killall -u mysql to kill all the mysql processes.
Thank You very much bro…
Thanks! it worked.
Thanks.
Thank a lots
while entering sudo mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
I am getting an error 2002…how to resolve
rest all steps have been executed correctly….
Worked without any flaw. Thanks for providing simple steps.