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

Keep note of your “Distrib”. In the above example, we are on MySQL 5.7. Keep note of this for later.

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

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.81 (21 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.

25 replies

avatar
  Subscribe  
newest oldest
Notify of
Camo
Guest
Camo
Camo
10 days ago

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’) …

Stonewallz
Guest
Stonewallz
Stonewallz
19 days ago

Awesome.
Spot on instructions….
Many Thanks!

omer
Guest
omer
omer
1 month ago

thanks u thanks u oh my god

DJATIO Stéphane
Guest
DJATIO Stéphane
DJATIO Stéphane
1 month ago

Thanks very much was block since 8hours

Mostefa Mourad
Guest
Mostefa Mourad
Mostefa Mourad
2 months ago

u saved my life thanks , u truly know what ur doing thanks , much appreciated

vidomarkas.com
Guest
vidomarkas.com
vidomarkas.com
2 months ago

Thanks!

Tom Van Dyck
Guest
Tom Van Dyck
Tom Van Dyck
2 months ago

Thanks a lot!

hay
Guest
hay
hay
2 months ago

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#

Phinah
Guest
Phinah
Phinah
2 months ago

Thank you it was really helpful

Max Solace
Guest
Max Solace
Max Solace
3 months ago

Thanks a ton, Buddy.

karan
Guest
karan
karan
5 months ago

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 mysql
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

Crypto MadMax
Guest
Crypto MadMax
Crypto MadMax
5 months ago

thank you this helped me, mysql 5.7.27-0ubuntu.18.04.1 on ubuntu

Tijol
Guest
Tijol
Tijol
6 months ago

You’re the best thank you 🙂

Found you through Google with “less than a month” filter, else there is some much crap lol

kraken
Guest
kraken
kraken
7 months ago

A very big thank you – after looking at several solutions, this is the most succinct and helpful answer; it solved all the ensuing issues after I forgot the root mysql pass.

hossein
Guest
hossein
hossein
7 months ago

i surf all web pages but none of them could help me but your pages helpme a lot …….
thanks !!!!!!

crew22
Guest
crew22
crew22
7 months ago

mysql> update user set plugin="mysql_native_password" where User='root';

[email protected]:~# sudo mysql -p -u root
Enter password:

ERROR 1524 (HY000): Plugin ‘mysql_native_password’ is not loaded

The error is because mysql dont recongnize pluggin with ‘my passowrd name’?!

Fábio CS
Guest
Fábio CS
Fábio CS
8 months ago

Muito obrigado.

asd
Guest
asd
asd
8 months ago

Work like charm

Gabriel
Guest
Gabriel
Gabriel
9 months ago

Thank you! Very useful!

faisalqayyom
Guest
faisalqayyom
faisalqayyom
11 months ago

Thanks alot you are a saviour…

Víc
Guest
Víc
Víc
1 year ago

I was fighting a lot with this and you helped me solve it to the first!.
I Love You : )

Nina
Guest
Nina
Nina
1 year ago

Just perfect, thanks a lot!

Shahin
Guest
Shahin
Shahin
1 year ago

Its works fine for me.