Contents

How to Reset MySQL Root Password

11 Aug 2018 1 46  0
How to reset a MySQL root password
How to reset a MySQL root password

In this tutorial, we will teach you how to reset MySQL root password in case you have forgotten it. The following steps should work with any modern Linux distribution.

I have talked about the MySQL and Password reseting  earlier, the details of which you can read in these articles:

  1. How to Install MySQL 8.0 in Ubuntu 18.04
  2. Configure remote and secure connections for MySQL on Ubuntu 16.04

 

First stop the MySQL or the MariaDB service:

sudo systemctl stop mysql

Start the MySQL server without a password:

mysqld_safe --skip-grant-tables &

Log in to the MySQL shell:

mysql -u root

Set a new MySQL root password:

Depending on the MySQL or MariaDB server version you are running on your system, you will need to use different commands to recover the root password.

You can find your server version by issuing the following command:

mysql --version

As you can see from the output above, I am running MySQL version 5.7.22.

Run the following commands if you have MySQL 5.7.6 or later or MariaDB 10.1.20 or later:

mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

 

Run the following commands if you have MySQL 5.7.5 or earlier or MariaDB 10.1.20 or earlier:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;

 

If the ALTER USER statement doesn’t work for you, try to modify the user table directly:

UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

 

In both cases, if everything went well, you should see the following output:

Query OK, 0 rows affected (0.00 sec)

Stop the server with the command below.

sudo mysqladmin shutdown

Finally, start the MySQL or the MariaDB service:

sudo systemctl start mysql

Final Thoughts

You have learned how to reset your MySQL or MariaDB root password. You can now check these instructions and learn how to manage your MySQL user accounts and databases.

That’s all! If you have any question or feedback, feel free to leave a comment.

 

0 Comments
Add a comment

Leave a Reply