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 <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 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 '<span class="red">your_password_here</span>';

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('<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.

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('<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.

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

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

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

  15. You’re the best thank you 🙂

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

    Reply

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

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