How to Reset MySQL Root Password in Ubuntu

How to Reset the MySQL Root Password on Ubuntu

Last updated on | 178 replies

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

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 -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.36, 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
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/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!

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.

178 replies

Leave a reply

Your email address will not be published. Required fields are marked *

  3. Thank you very much. I’ve been around the world twice trying to figure this out. Thank you!!!!!!!!!!!!!

    Reply

  5. A new installation on Kubuntu 23.04 and I was expecting the usual interminable struggle but your guidance for mysql 8.0.33 wass PERFECT!
    Many many thanks.

    Reply

  7. Thanks a million times. I have been struggling to reset the password, which magically disappeared, for a while. Lots of “solutions” on the web have been unsuccessful until I discovered this one. Worked perfectly the first time. Kudos.

    Reply

  10. This is an excellent help. Very much appreciated. However I still can’t connect my mysql workbench to MySQL database. It will be nice if you can bel on this,
    Thanks very much

    Reply

  11. thank you so much mister.. but i have problem this
    The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. Find out why.
    Or alternatively go to ‘Operations’ tab of any database to set it up there.
    Open new phpMyAdmin window
    mysqli::real_connect(): (HY000/1045): Access denied for user ‘phpmyadmin’@’localhost’ (using password: YES)
    Connection for controluser as defined in your configuration failed.

    Reply

  24. really…. excellent website.. after 100 trails from other sources, the solution of this website only could solve my problem… thank you soo much

    Reply

  33. thank you very much this worked very smoothly and really this is great help :))) Really you saved me from headache .
    Thanks thanks alot

    Reply

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

    Reply

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

    Reply

  57. After two days of debugging, browsing…, searching and trying everything I can… this is actually what helped me!

    Reply

  70. 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 ಥ_ಥ

    Reply

  77. 100% OK, gracias a este POST, solucione un gran dolor de cabeza..! instale mi servidor linux y no podia conectarme a la base de datos.!.

    Reply

  79. Thank you so much bro i did just spent my 3 days time in this but not found correct answer thank you LOVE FROM INDIA

    Reply

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

    Reply

  98. 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
    Reply

  106. geary@NUC:/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)

    Reply

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

      [mysqld_safe]
#syslog
      Reply

  109. 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
    Reply

  113. 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!

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

    1. Thank you for letting me know.

      This article has now been updated with instructions on resetting the root password for MySQL 8.

      Reply

  156. 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
    root@webserver:/home/webserver# killall -u mysql
    root@webserver:/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
    root@webserver:/home/webserver# sudo mysql -p -u root
    Enter password:
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
    root@webserver:/home/webserver# mysql -p -u root
    Enter password:
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
    root@webserver:/home/webserver#

    Reply

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

    Reply

  161. You’re the best thank you 🙂

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

    Reply

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

    Reply

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

    root@localhost:~# 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’?!

    Reply