How to Reset 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:
- How to Install MySQL 8.0 in Ubuntu 18.04
- 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:
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
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.