Introduction
We’ve all been there. You’ve forgotten the MySQL root password or it has mysteriously changed đ€. Thankfully there is a way around this using the --skip-grant-tables option.
Before you begin, if you are having problems logging into phpMyAdmin and getting an error Access denied for user ârootâ@âlocalhostâ , but you’re certain your root password is correct, please refer to this article first: Canât log into phpMyAdmin: mysqli_real_connect(): (HY000/1698): Access denied for user ârootâ@âlocalhostâ
1. Confirm MySQL version
Firstly, you must confirm which version of MySQL on Ubuntu you are running as commands will be different.
mysql -VIf on MySQL version 8, you will see something like:
mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))If you are on MySQL version 5, you will see something similar to:
mysql Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using EditLine wrapper2. Restart MySQL with skip-grant-table
In order to skip the grant tables and reset the root password, we must first stop the MySQL service. Enter your Linux password if prompted.
sudo /etc/init.d/mysql stopEnsure the directory /var/run/mysqld exists and correct owner set.
sudo mkdir /var/run/mysqldsudo chown mysql /var/run/mysqldNow start MySQL with the --skip-grant-tables option. The & is required here.
sudo mysqld_safe --skip-grant-tables&You should see something similar:
[1] 1283
[email protected]:~$ 2019-02-12T11:15:59.872516Z mysqld_safe Logging to syslog.
2019-02-12T11:15:59.879527Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2019-02-12T11:15:59.922502Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysqlNow press ENTER to return to the Linux BASH prompt.
3. Change MySQL Root Password
You can now log in to the MySQL root account without a password.
sudo mysql --user=root mysqlOnce logged in, you will see the mysql> prompt.
MySQL 8 – Reset Root Password
For MySQL 8 on Ubuntu, run following commands.
UPDATE mysql.user SET authentication_string=null WHERE User='root';flush privileges;Replace your_password_here with your own. (Generate a strong password here)
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';Flush privileges again.
flush privileges;Exit MySQL.
exitNow skip to Step 4 below.
MySQL 5.7 – Reset Root Password
For MySQL 5.7 on Ubuntu, run this command to change the root password. Replace your_password_here with your own. (Generate a strong password here)
update user set authentication_string=PASSWORD('your_password_here') where user='root';Change the auth plugin to mysql_native_password.
update user set plugin="mysql_native_password" where User='root';Flush privileges.
flush privileges;Exit MySQL.
exitNow skip to Step 4 below.
MySQL 5.6 – Reset Root Password
For MySQL 5.6 on Ubuntu, run this command to change the root password. Replace your_password_here with your own. (Generate a strong password here)
update user set Password=PASSWORD('your_password_here') where user='root';Change the auth plugin to mysql_native_password.
update user set plugin="mysql_native_password" where User='root';Flush privileges.
flush privileges;Exit MySQL.
exitNow skip to Step 4 below.
4. Test New Root Password
Make sure all MySQL processes are stopped before starting the service again.
sudo killall -u mysqlIf you see a message similar to below, press ENTER to continue.
2020-05-30T07:23:38.547616Z mysqld_safe mysqld from pid file /var/lib/mysql/ubuntu.pid endedStart MySQL again.
sudo /etc/init.d/mysql startLog in to MySQL again and you should now be prompted for a password.
sudo mysql -p -u rootEnter your MySQL root password. If correct, you should see something like:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2020, 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.
mysql>You’re all done!
4.92 (66 votes)Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or đ buy me a smoothie.
p.s. I increased my AdSense revenue by 68% using AI đ€. Read my Ezoic review to find out how.
64 replies
Aweosome! You’re the big guy.
This is the only dowcument that explains the process correctly. Thanks
You saved my day!
You won’t believe it but I spent 3 hours on Google to find your solution! Thank you so much for allowing me to finally go to sleep now!
I spent have the day on this and yours was the only post I came across that actually worked. Thank you.
Thank you very much.
Thank you for this article. It was the only article I found that really worked, and allowed me to set a root password for mysql, after I lost the original password.
Sweet!!!
Excellent!!! Thanks!!!!
Thanks, it’s working.
perfect!!!!!
Nicely explained. Save me trouble..
Worked for me too! bundles of thanks
Worked like a charm. Tried several other sites but came up short. Thanks for this!
yo thanks, I couldn’t find mysql 8 commands
many thanks – the documentation does not cover V8 – spent a few days struggling with this – appreciate the clear and correct instructions – Cheers.
Thank you. Great.
great article
Finally after following the steps from 3 other sites, yours finally worked! Thank you!
Thanks a lot Really Helpful post
you are awesome , thaaaaaaaaanks
Thank you so much.
This article really helpful,thanks!
Thanks! help a lot #2020/11/05
Thank you!!
Thanks a lot!!! Perfectly work for me!
Thanks this did the trick!
Great! worked perfectly
thank you so much
This worked flawlessly, thank you, very helpful and easy to follow!
Thanks for very useful informations. It works.
Work for me
me ha servido muy bien !
THANKYOU VERY MUCH
I’m a newbie to all this and can’t count the # of hours and things i’ve tried to get my root password working (because it was never set at installation) with no success until now. I CAN’T THANK YOU ENOUGH FOR YOUR TIME & CONTRIBUTION AND HOPE YOU REALIZE HOW MUCH IT MEANS TO SOME OF US NEWBIES TO GET CORRECT AND WORKING INFO LIKE THIS.
Best explanation ever!
Thank you very much!
You just saved my life!
What about MySQL 8? It seems PASSWORD() is no longer supported.
mysql> update user set authentication_string=PASSWORD('my_password') where user='root';throws me an syntax error near (‘my_password’) …Thank you for letting me know.
This article has now been updated with instructions on resetting the root password for MySQL 8.
Awesome.
Spot on instructions….
Many Thanks!
thanks u thanks u oh my god
Thanks very much was block since 8hours
u saved my life thanks , u truly know what ur doing thanks , much appreciated
Thanks!
Thanks a lot!
mysql> update user set plugin=”mysql_native_password” where User=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[email protected]:/home/webserver# killall -u mysql
[email protected]:/home/webserver# 2020-03-16T08:43:49.778593Z mysqld_safe mysqld from pid file /var/lib/mysql/webserver.pid ended
/etc/init.d/mysql start
[ ok ] Starting mysql (via systemctl): mysql.service.
[1]+ Done mysqld_safe –skip-grant-tables
[email protected]:/home/webserver# sudo mysql -p -u root
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[email protected]:/home/webserver# mysql -p -u root
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
[email protected]:/home/webserver#
Thank you it was really helpful
Thanks a ton, Buddy.
In step 2 i get
2019-12-29T22:46:28.239042Z mysqld_safe Logging to syslog.2019-12-29T22:46:28.242008Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2019-12-29T22:46:28.258774Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2019-12-29T22:46:32.136840Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Then on running
sudo mysql --user=root mysqlERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)