Introduction
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 <span class="red">8.0.20</span>-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 <span class="red">5.7.36</span>, 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
user@server:~$ 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 '<span class="red">your_password_here</span>';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('<span class="red">your_password_here</span>') 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('<span class="red">your_password_here</span>') 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!
Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.
Worked like charm. Thank you.
Thanks, very useful guide, very help me
Thanks bro, you save my time
thanks
oHH MAN YOU SAVED MY FRIDAY! THANK YOU VERY MUCH
really worked… !!! Thank you so much
Doesn’t work : I have Ubuntu 20.04 + mysql 8.0.28
SO after executing mysqld_safe –skip-grant-tables&
I get after 2-3 seconds
(…)
2022-05-09T17:02:38.878818Z mysqld_safe mysqld from pid file xxx.pid ended
Impossible to log and change password
PS: xxx is a path + hostname to the pid file
Thanks a lot. This guide helped me a lot, while other guides did not!
Hello, thanks for your help. I ran the command explain in pragraph for MySQL 8 on my version which is MySQL 5.7 and I successfully rest the root password.
you save me
After two days of debugging, browsing…, searching and trying everything I can… this is actually what helped me!
Awesome
After lots and lots of searching… Thank you, thank you!
Just commenting to push this post to the top because it was the only tutorial that finally worked.
Legend, many other tutorials just don’t work. Thank you
Awesome straightforward tutorial. Saved me a bunch of time! Thanks!
Thank you.
This manual helped a lot
Awesome tutorial!
Success !!! Excellent …!!!!
*the only tutorial that actually helped 🙂
THANKS!
This tutorial work for me.
Thanks in advance
Thanks a lot 😀
save me from another fresh install
Thanks a lot. You have provided all procedures that how we can change mysql password step by step.
yes, thanks. Just what I needed. Trying to troubleshoot problems and the cowboy who set it up retired without documenting anything.
ohhh the joy of a treasure hunt ಥ_ಥ
Yeah man all of this process worked thanks a lot, perfect article