How to Reset MySQL Root Password in Ubuntu

How to Reset the MySQL Root Password on Ubuntu

Last updated on

In this article we will reset the MySQL root password in Ubuntu by starting MySQL with the --skip-grant-tables option.

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 -V

If 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 wrapper

2. 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 stop

Ensure the directory /var/run/mysqld exists and correct owner set.

sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld

Now 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/mysql

Now 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 mysql

Once 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.

exit

Now 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.

exit

Now 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.

exit

Now 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 mysql

If 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 ended

Start MySQL again.

sudo /etc/init.d/mysql start

Log in to MySQL again and you should now be prompted for a password.

sudo mysql -p -u root

Enter 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!

1 Star2 Stars3 Stars4 Stars5 Stars 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

avatar
  Subscribe  
newest oldest
Notify of
african is back again
Guest
african is back again
african is back again
3 days ago

THANKS!!

wiredmartian
Guest
wiredmartian
wiredmartian
15 days ago

This line sudo mkdir /var/run/mysqld saved me hours. Beautiful article.

Thank you!!

African-GOD
Guest
African-GOD
African-GOD
22 days ago

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.

skhura
Guest
skhura
skhura
24 days ago

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

Adolf
Guest
Adolf
Adolf
1 month ago

thank you very much! it works

Kashan ghori
Guest
Kashan ghori
Kashan ghori
1 month ago

great blog

ali
Guest
ali
ali
1 month ago

great!

Luis Rodríguez
Guest
Luis Rodríguez
Luis Rodríguez
1 month ago

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:

[mysqld_safe]
#syslog
Nitin
Guest
Nitin
Nitin
1 month ago

Thank you so much. I wasted hours reading answers but finally your blog helped me.

Daniel
Guest
Daniel
Daniel
1 month ago

You have saved my degree

Rachel
Guest
Rachel
Rachel
1 month ago

Thank you!!!!!!

Martin
Guest
Martin
Martin
1 month ago

thank you very much!!!!

Haris Noori
Guest
Haris Noori
Haris Noori
1 month ago

Thanks, it worked!!

Adib Akhtab
Guest
Adib Akhtab
Adib Akhtab
2 months ago

Trually effective doc.

Thank you.

packer
Guest
packer
packer
2 months ago

The Best answer i ever got for “How to reset MySQL password”.
Thank You very much!!!

geary
Guest
geary
geary
2 months ago

[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)

Luis Rodríguez
Guest
Luis Rodríguez
Luis Rodríguez
1 month ago

To solve this edit the file /etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf and comment the second line:

[mysqld_safe]
#syslog
Roberto Dimas
Guest
Roberto Dimas
Roberto Dimas
2 months ago

Thank you very much!!!

fawzia Mohammed
Guest
fawzia Mohammed
fawzia Mohammed
2 months ago

Thanks it solved my problem in less than 5 minutes

ahwar
Guest
ahwar
ahwar
2 months ago

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


mysql  Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using  EditLine wrapper
``` It is working 
Thanks A lot
Felipe
Guest
Felipe
Felipe
2 months ago

Aweosome! You’re the big guy.

Drtamtam
Guest
Drtamtam
Drtamtam
2 months ago

This is the only dowcument that explains the process correctly. Thanks

washington
Guest
washington
washington
3 months ago

You saved my day!

Jack
Guest
Jack
Jack
3 months ago

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!

Mr Tizzle
Guest
Mr Tizzle
Mr Tizzle
3 months ago

I spent have the day on this and yours was the only post I came across that actually worked. Thank you.

long
Guest
long
long
3 months ago

Thank you very much.

Kurt Krueckeberg
Guest
Kurt Krueckeberg
Kurt Krueckeberg
3 months ago

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.

misterUyi
Guest
misterUyi
misterUyi
3 months ago

Sweet!!!

Selín
Guest
Selín
Selín
3 months ago

Excellent!!! Thanks!!!!

ly
Guest
ly
ly
3 months ago

Thanks, it’s working.

masita
Guest
masita
masita
4 months ago

perfect!!!!!

aravind
Guest
aravind
aravind
4 months ago

Nicely explained. Save me trouble..

Imran Ali
Guest
Imran Ali
Imran Ali
4 months ago

Worked for me too! bundles of thanks

Ray
Guest
Ray
Ray
4 months ago

Worked like a charm. Tried several other sites but came up short. Thanks for this!

assdz
Guest
assdz
assdz
4 months ago

yo thanks, I couldn’t find mysql 8 commands

Woniok
Guest
Woniok
Woniok
4 months ago
You made my life happier with this article. Thanks.
Tony
Guest
Tony
Tony
4 months ago

many thanks – the documentation does not cover V8 – spent a few days struggling with this – appreciate the clear and correct instructions – Cheers.

Steven Moo
Guest
Steven Moo
Steven Moo
4 months ago

Thank you. Great.

benmazouz
Guest
benmazouz
benmazouz
4 months ago

great article

Hugo
Guest
Hugo
Hugo
4 months ago

Finally after following the steps from 3 other sites, yours finally worked! Thank you!

Saravana Kumar
Guest
Saravana Kumar
Saravana Kumar
4 months ago

Thanks a lot Really Helpful post

marjan sadat mousavi
Guest
marjan sadat mousavi
marjan sadat mousavi
5 months ago

you are awesome , thaaaaaaaaanks

Lakshan Madusanka
Guest
Lakshan Madusanka
Lakshan Madusanka
5 months ago

Thank you so much.

mysql rookie
Guest
mysql rookie
mysql rookie
5 months ago

This article really helpful,thanks!

udara jayawardena
Guest
udara jayawardena
udara jayawardena
5 months ago

Thanks! help a lot #2020/11/05

Agvil
Guest
Agvil
Agvil
5 months ago

Thank you!!

Steve
Guest
Steve
Steve
5 months ago

Thanks a lot!!! Perfectly work for me!

Todd
Guest
Todd
Todd
5 months ago

Thanks this did the trick!

Adeel
Guest
Adeel
Adeel
5 months ago

Great! worked perfectly

RyanC
Guest
RyanC
RyanC
5 months ago

thank you so much

Dino Matijašević
Guest
Dino Matijašević
Dino Matijašević
6 months ago

This worked flawlessly, thank you, very helpful and easy to follow!