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.94 (84 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 200% using AI 🤖. Read my Ezoic review to find out how.
84 replies
THANKS!!
This line
sudo mkdir /var/run/mysqldsaved me hours. Beautiful article.Thank you!!
Very good tutorial, straight forward and worked the first time. The problem is that with each version of mysql they change the rules and none of the old rules work . It can be very frustrating. Even the set of steps listed on the MySQL site itself don’t work. How ridiculous.
This is the best ever tutorial for setting root password for mysql. However i had issues that needed me to reintsall mysql and i used this tutorial https://askubuntu.com/questions/1068270/apparmor-parser-error-for-mysqld
thank you very much! it works
great blog
great!
Great article !! Thank you.
I had a problem to restart Mysql with –skip-grant-tables. I got this error message:
> Can’t connect to local MySQL server through socket ‘/var/mysql/mysql.sock’ (38)
Checking the error log I found Mysql could not be started because unknown variable ‘log-syslog=1’:
> [ERROR] [MY-000067] [Server] unknown variable ‘log-syslog=1’.
To solve this edit the file /etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf and comment the second line:
Thank you so much. I wasted hours reading answers but finally your blog helped me.
You have saved my degree
Thank you!!!!!!
thank you very much!!!!
Thanks, it worked!!
Trually effective doc.
Thank you.
The Best answer i ever got for “How to reset MySQL password”.
Thank You very much!!!
[email protected]:/var/run/mysql$ sudo mysql –user=root mysql
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
To solve this edit the file /etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf and comment the second line:
Thank you very much!!!
Thanks it solved my problem in less than 5 minutes
Finally it is done.
I have been trying it for hour from other websites but no progress but this page solved my problem.
as of January 29, 2021 with version
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!